Help with showing 0 records in querie (1 Viewer)

[Vergy39]

I have asked for help in the NullToZero function before, but for some reason I cannot apply what I have learned to a new situation. I need to create a report that will show records for weekending date even if there were no records recorded for a week or 2. I have attached a copy of the sample database. Use query _QryDaysByWeekending as an example and use 10/31/2010 as a start date and 12/25/2010 as a end date. Use Associate FFFFFF. You will see that some of the weekending dates are missing due to no records were found. I need to roll this data up into a report by weekending date and must include all 4 or 5 weeks in the fiscal month. Oour work weeks are Sunday to Saturday. So weekending dates will always be a saturday. Any assistance is greatly appreciateed.

 

[boblarson]

You're missing the form you reference in the query. It isn't in the database sample.

 

[boblarson]

I took out the form references for the dates - you'll want to put them back in I'm sure. But take a look at your revised query (I also had to create a second query based on that first one because the first needed the begin date and end date fields which then caused it to not sum properly for the total day count. So look at the additional query I did which should be easy to find as I did NOT preface it with an underscore.

 

[Vergy39]

Thanks Bob for the quick reply. I studied your query and still could not apply it to how I need it. I thought to give you a better sample. I have attached the database that includes the form "datepicker". In still using the date range of 10/31/2010 to 12/25/2010 and associate JJJJJ on the query "_QryTATByWeekending", you will see that it returns 6 rows. It is missing weekending 11/6 and weekending 12/4. This associate was off those weeks, so she would not have any records, but I still need to show this on the report that this query will rollup to. You have been a great help Bob, and I certainly appreciate you assistance.

Thanks
David V

 

[Vergy39]

Still pulling my hair out over this. Please, if anyone can assist. I have seen some threads that are real close to what I am looking for, but I am unable to apply the formula to my queires. I have #ofIssues: Count(*) that I think I need to apply the NZ function to. Bob Larsen had this in a thread dated 1/11/11: IIf(Nz([Allocated], 0) + Nz([Carryover], 0) = 0 OR Nz([Approved], 0) = 0, 0, Nz([Allocated],0) + Nz([Carryover],0)). Not sure how to combine this with the Count function. Any assistance is greatly appreciated.

Thanks
David V.

 

[gemma-the-husky]

if you have a totals query, you will generate totals for the weeks that you DO have data

so to get zeroes for the weeks you dont have data, you need a sort of MASTER query of all the week numbers

then left join the totals query, to get a "full" query, including the nulls