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

[spartansFC]

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:

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

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

 

[dhookom]

Here is your final query in a more readable version:

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 [highlight #FCE94F]C.lngChildID=2180 AND C.FinYrGrouping="2016" AND C.strWklyStudentCostsWkNo>="01"
  And C.strWklyStudentCostsWkNo<="53"[/highlight]
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

 

[spartansFC]

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

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

 

[dhookom]

Share your SQL view.

Duane
Hook'D on Access
MS Access MVP

 

[spartansFC]

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:

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

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