Query not returing all records (1 Viewer)

[bbulla]

Hi,

I have a table that has a record each time a road gets patrolled. I have another table, that has all of the roads. I also have a query that tells me how many times a road was patrolled in a week.

What I want to do is get a list of all road patrols for the current week for all roads, but I also want the roads with no (ie. 0) patrols to show up also. If there is 1 or more, they show up, but how do I get the roads that have 0 patrols to show up.

Here is my SQL from the designer in MS Access:

SELECT tblREGIONAL_ROADS.REG_RD, tblREGIONAL_ROADS.REG_RD_NUM, tblREGIONAL_ROADS.Depot, qryRoadPatrolsByWeek.Expr1 AS [Current Week], qryRoadPatrolsByWeek.[Count of Road Patrols this Week]
FROM tblREGIONAL_ROADS LEFT JOIN qryRoadPatrolsByWeek ON tblREGIONAL_ROADS.REG_RD_NUM = qryRoadPatrolsByWeek.ROADNUM
WHERE (((tblREGIONAL_ROADS.Depot)="Ajax/Pickering") AND ((qryRoadPatrolsByWeek.Expr1)=DatePart("ww",Now())));

My qryRoadPatrolsByWeek basically just summarizes the # of patrols for each week....it groups and then does a count to get the [Count of Road Patrols this Week].

Obvioulsy the roads with a count of 0 aren't going to show up in the qryRoadPatrolsByWeek, since they won't be in the table. But ALL of the roads are in the tblREGIONAL_ROADS, so how do I get a record to show up in the final query that shows a count of 0.

Thanks,

 

[Galaxiom]

Add this to the Where clause

OR qryRoadPatrolsByWeek.ROADNUM Is Null

Use the Nz function to get the Null records to show zero

 

[bbulla]

Thanks for the advice. I still can't get it to work. I'm not totally sure where the Nz function is supposed to go, after trying it on various fields.

I've attached the DB with some sample data in it. Could you have a look at it and show me where I need to alter my queries??

There are 13 roads in total. After running the qryLessThan3RoadPatrolsThisWeek_DAY query, there should be 11 roads with 1 patrol, 1 road with 2 patrols, and 1 road with no (ie. 0) patrols.

I am only getting the roads with patrols reported.

 

[jdraw]

I did the following based on your tables and request.

I created a query qj1
SELECT tblREGIONAL_ROADS.REG_RD
, tblREGIONAL_ROADS.REG_RD_NUM
, tblREGIONAL_ROADS.Depot
, Count(tblPATROL.PATROLDATE) AS CountOfPATROLDATE
, DatePart("ww",[Patroldate]) AS Expr1
FROM tblREGIONAL_ROADS LEFT JOIN tblPATROL
ON tblREGIONAL_ROADS.REG_RD_NUM=tblPATROL.ROADNUM
GROUP BY tblREGIONAL_ROADS.REG_RD, tblREGIONAL_ROADS.REG_RD_NUM, tblREGIONAL_ROADS.Depot, DatePart("ww",[Patroldate])
HAVING (((tblREGIONAL_ROADS.Depot)="Ajax/Pickering"));


The used it in a second query called qj2

SELECT qJ1.REG_RD_NUM
, qJ1.REG_RD
, qJ1.Depot
, qJ1.Expr1
, qJ1.CountOfPATROLDATE
FROM qJ1
GROUP BY qJ1.REG_RD_NUM
, qJ1.REG_RD
, qJ1.Depot
, qJ1.Expr1
, qJ1.CountOfPATROLDATE
HAVING (((qJ1.CountOfPATROLDATE)<3))
ORDER BY qJ1.CountOfPATROLDATE DESC;

I hope this is helpful.

 

[bbulla]

Hi. Thanks for the help, but that isn't quite what I am looking for. It gives me the grand total, but I only need the total for a given week....not all weeks.

I figured out how to do it using three queries:

1. Query all the Road Patrols by week (qryAllRoadPatrolsByWeek)

SELECT tblPATROL.ROADNUM, DatePart("ww",CDate([patroldate])) AS [Week Number], Count(tblPATROL.ROADNUM) AS [Count of Road Patrols this Week], tblPATROL.DEPOTAREA
FROM tblPATROL
GROUP BY tblPATROL.ROADNUM, DatePart("ww",CDate([patroldate])), tblPATROL.DEPOTAREA;

2. Query all the Road Patrols for the current week (qryRoadPatrolsThisWeek)

SELECT qryAllRoadPatrolsByWeek.ROADNUM, qryAllRoadPatrolsByWeek.[Week Number], qryAllRoadPatrolsByWeek.[Count of Road Patrols this Week], qryAllRoadPatrolsByWeek.DEPOTAREA
FROM qryAllRoadPatrolsByWeek
WHERE (((qryAllRoadPatrolsByWeek.[Week Number])=DatePart("ww",Now())));

3. Finally, do a Left Join to the Roads Table to get any roads with no completed patrols (qrySummaryRoadPatrolsThisWeek)

SELECT tblREGIONAL_ROADS.REG_RD_NUM, tblREGIONAL_ROADS.Depot, nz([Count of Road Patrols this Week],"None") AS Expr1
FROM tblREGIONAL_ROADS LEFT JOIN qryRoadPatrolsThisWeek ON tblREGIONAL_ROADS.REG_RD_NUM = qryRoadPatrolsThisWeek.ROADNUM;


Perhaps a bit more complex than I need it to be, but it works. I can also filter by the Depot Area in this last query, and not mess with any of my other queries.

Thanks for your help.

 

[jdraw]

Glad you got it working.
My queries were primarily meant to show how to get the RoadRecords that was not patrolled that week. PatrolCount 0 so to speak.