Return values for every day of date range even if records don't exist 2

[Lhuffst]

I have a report that has 29 items that we do counts on. I have to report on the counts per person per timeframe. If a person does not have a record for a given month then I still need to print their name and a count of 0 for the 29 items. The report is broken down by:
GH1 Date (year)
GH2 Date (Month)
GH3 People and contains the counts for each item.


item1 item2 item3
People1 0 12 12
People2 0 0 0

Currently what happens is the people who have stats will print but those who don't have anything are omitted. I am not sure how to include them and print 0's. thanks for any and all help. lhuffst

 

[NickieP]

You need to create a table to link your people table to that has all dates.
Create a one off in Excel(or Access).
Excel:
Type "DATE" in A1
Type the earliest date you need in A2
TypeA2+1 in row 3 and copy down whole column to span all dates you might need.
I call mine "ALLDATES"
Then link from "ALLDATES" to your table with the people and their dates. Link FROM "ALLDATES" to people.
Remember, any parameters, such as 'in last 30 days', 'last full month' and any grouping on dates must use the "ALLDATES" field not the date field in your other table.
You will then get all dates returned, some with no records from the table with people in, which is what you wanted.
Even if the people data is in an ODBC , you can still link to an Excel table.

 

[lbass]

Also make sure you are using a left join FROM Alldates TO the other table.

-LB

 

[Lhuffst]

Tried all suggestions and still had issues with empty records. I was able to get the names as long as there was a date but if they hadn't entered something for a specific date, the names didn't appear. This works as long as someone enters something for the time frame but discovered that one or two counts were dropping. The database guy here suggested I redo my query and instead of linking the tables as I tried, he wrote several subselects to produce a line item for each day. That seems to have done the trick. It's a very long query since I am basically making a mock cross tab so if anyone wants to see it, just let me know and I'll post it. Thanks again for all the help. lhuffst

 

[lbass]

Yes, if you added any selection criteria based on the "other" table, you would lose dates, as it effective 'undoes' the left outer join.

-LB