INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Show all records from Query A even if no matching records in Query B

Show all records from Query A even if no matching records in Query B

(OP)
Hi

I need to show calculated fees for all 52 weeks of the year, if there are no fees for a certain week, i need to show a zero for that week.

my first query lists the weeks, the SQL is:

CODE

SELECT tblDates.FinYrGrouping, tblDates.strWklyStudentCostsWkNo, tblDates.strWklyStudentCosts
FROM tblDates
GROUP BY tblDates.FinYrGrouping, tblDates.strWklyStudentCostsWkNo, tblDates.strWklyStudentCosts
HAVING (((tblDates.FinYrGrouping)="2016")); 

and the joined query has

CODE

SELECT qryBooking3Calculations.lngChildID, qryBooking3Calculations.FinYrGrouping, qryBooking8StudentWeeklyCostsWkNumbers.strWklyStudentCostsWkNo, qryBooking3Calculations.strWklyStudentCostsWkNo, qryBooking3Calculations.strWklyStudentCosts, Sum(qryBooking3Calculations.ChargeableSessions) AS SumOfChargeableSessions, qryBooking3Calculations.ActRate, Sum([ChargeableSessions]*[ActRate]) AS weeklycost
FROM qryBooking8StudentWeeklyCostsWkNumbers LEFT JOIN qryBooking3Calculations ON qryBooking8StudentWeeklyCostsWkNumbers.strWklyStudentCostsWkNo = qryBooking3Calculations.strWklyStudentCostsWkNo
GROUP BY qryBooking3Calculations.lngChildID, qryBooking3Calculations.FinYrGrouping, qryBooking8StudentWeeklyCostsWkNumbers.strWklyStudentCostsWkNo, qryBooking3Calculations.strWklyStudentCostsWkNo, qryBooking3Calculations.strWklyStudentCosts, qryBooking3Calculations.ActRate
HAVING (((qryBooking3Calculations.lngChildID)=2180) AND ((qryBooking3Calculations.FinYrGrouping)="2016") AND ((qryBooking3Calculations.strWklyStudentCostsWkNo)>="01" And (qryBooking3Calculations.strWklyStudentCostsWkNo)<="53")); 

i'm not sure if i've joined the 2 queries together properly or if i have to use a null as a criteria on one of the fields.

Mikie

RE: Show all records from Query A even if no matching records in Query B

Here is your final query in a more readable version:

CODE --> SQL

SELECT C.lngChildID, C.FinYrGrouping, S.strWklyStudentCostsWkNo, 
  C.strWklyStudentCostsWkNo, C.strWklyStudentCosts, 
  Sum(C.ChargeableSessions) AS SumOfChargeableSessions, 
  C.ActRate, Sum([ChargeableSessions]*[ActRate]) AS weeklycost
FROM qryBooking8StudentWeeklyCostsWkNumbers S 
  LEFT JOIN qryBooking3Calculations C ON S.strWklyStudentCostsWkNo = C.strWklyStudentCostsWkNo
WHERE C.lngChildID=2180 AND C.FinYrGrouping="2016" AND C.strWklyStudentCostsWkNo>="01"
  And C.strWklyStudentCostsWkNo<="53"
GROUP BY C.lngChildID, C.FinYrGrouping, S.strWklyStudentCostsWkNo, 
  C.strWklyStudentCostsWkNo, C.strWklyStudentCosts, C.ActRate; 
Any time you apply a filter against the right side of a left join, it negates the "all records". You will need to apply the filtering in the qryBooking3Calculations query.

Duane
Hook'D on Access
MS Access MVP

RE: Show all records from Query A even if no matching records in Query B

(OP)
Hi

So i copied and pasted the new sql query, the results are below. It still doesn't list all 52 weeks, i even tried changing the query slightly but
nothing seemed to work

CODE --> datasheet

lngChildID	FinYrGrouping	S.strWklyStudentCostsWkNo	C.strWklyStudentCostsWkNo	strWklyStudentCosts	SumOfChargeableSessions	ActRate	weeklycost
2180	2016	03	03	11.04.16 to 15.04.16	2	£23.62	47.24
2180	2016	04	04	18.04.16 to 24.04.16	2	£23.62	47.24
2180	2016	05	05	25.04.16 to 01.05.16	2	£23.62	47.24
2180	2016	06	06	02.05.16 to 08.05.16	1	£23.62	23.62
2180	2016	07	07	09.05.16 to 15.05.16	2	£23.62	47.24
2180	2016	08	08	16.05.16 to 22.05.16	2	£23.62	47.24
2180	2016	09	09	23.05.16 to 29.05.16	2	£23.62	47.24
2180	2016	11	11	06.06.16 to 12.06.16	2	£23.62	47.24
2180	2016	12	12	13.06.16 to 19.06.16	2	£23.62	47.24
2180	2016	13	13	20.06.16 to 26.06.16	2	£23.62	47.24
2180	2016	14	14	27.06.16 to 03.07.06	2	£23.62	47.24
2180	2016	15	15	04.07.16 to 10.07.16	0	£23.62	0
2180	2016	16	16	11.07.16 to 17.07.16	0	£23.62	0
2180	2016	17	17	18.07.16 to 24.07.16	0	£23.62	0
2180	2016	19	19	01.08.16 to 07.08.16	0	£23.62	0
2180	2016	24	24	05.09.16 to 11.09.16	5	£23.62	118.1 

Mikie

RE: Show all records from Query A even if no matching records in Query B

(OP)
So somehow i managed to get it to work, there's always a workaround i find in access, anyways my solution was

in query: qryBooking8StudentWeeklyCosts, the SQL looks like:

CODE

SELECT qryBooking3Calculations.lngChildID, qryBooking3Calculations.FinYrGrouping, 
tblPeople.strForename, tblPeople.strSurname, qryBooking3Calculations.strWklyStudentCostsWkNo, 
qryBooking3Calculations.strWklyStudentCosts, 
Sum(qryBooking3Calculations.ChargeableSessions) AS SumOfChargeableSessions, qryBooking3Calculations.ActRate
FROM qryBooking3Calculations INNER JOIN tblPeople ON qryBooking3Calculations.lngChildID = tblPeople.lngPeopleID
GROUP BY qryBooking3Calculations.lngChildID, qryBooking3Calculations.FinYrGrouping, 
tblPeople.strForename, tblPeople.strSurname, qryBooking3Calculations.strWklyStudentCostsWkNo, 
qryBooking3Calculations.strWklyStudentCosts, qryBooking3Calculations.ActRate
HAVING (((qryBooking3Calculations.lngChildID)=[Forms]![frmChildren]![lngPeopleID]) 
AND ((qryBooking3Calculations.FinYrGrouping)="2016")); 

and this query is then added to

CODE

SELECT qryBooking8WkNumbers.FinYrGrouping, qryBooking8WkNumbers.strWklyStudentCostsWkNo, 
qryBooking8WkNumbers.strWklyStudentCosts, qryBooking8WeeklyCosts.lngChildID, 
qryBooking8WeeklyCosts.ActRate, qryBooking8WeeklyCosts.SumOfChargeableSessions, 
IIf([ActRate] Is Null,0,[ActRate]) AS Actrate2, 
IIf([SumofChargeableSessions] Is Null,0,[SumSessions]) AS Sessions, [ActRate2]*[Sessions] AS Costs
FROM qryBooking8StudentWeeklyCostsWkNumbers 
LEFT JOIN qryBooking8StudentWeeklyCosts ON (qryBooking8StudentWeeklyCostsWkNumbers.strWklyStudentCostsWkNo = qryBooking8WeeklyCosts.strWklyStudentCostsWkNo) 
AND (qryBooking8WkNumbers.FinYrGrouping = qryBooking8WeeklyCosts.FinYrGrouping)
WHERE (((qryBookingWkNumbers.FinYrGrouping)="2016")); 

I'm sure someone could design it better but it works and it's pretty quick too.

Thanks again for all the help

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close