A03 Report How to Pull Missing Data

[JK]

I can't figure out the logic behind what I'm trying to do.

I have a db that I use to log our Salesmen's Activity Summary Reports (a
summary of what they did the past week.) The data is logged in the db based
on category totals (e.g. total mileage, total new accouts, etc.) This
information in entered by salesman and by week end date (always a saturday.)

I need a report that tells me if and when a salesmen forgot or simply did
not submit their report. For example: salesman x did not submit a report last
week (week end 5-19-07).

The database only stores information that I enter. If salesman x didn't
submit a report last week, he simply would not have anything in for the 19th
(nothing about the 19th would exist in the db so I can't pull based on a null
value.)

I know this is tough to do without seeing the db, but if you have any ideas
- you'd help me out significantly.

Thx in advance,
Jason

 

[Klatuu]

Actually, pulling the data based on the null value is exactly what you want.
Create a query for the report's record source filtered on the date field
being Null:

SELECT * FROM tblSalesSummary WHERE tblSalesSummary.TheDate IS NULL;

 

[JK]

Unless I'm missing something - this won't work...

Let's take the last entry I entered into the database. Week End 5-19-07.
Lets assume Salesman X didn't submit a report last week, there is nothing in
the db for Salesman X. There is no null record to search for.

The closest I could get, is if I start entering zero values. If Salesman X
doesn't submit a report next week, I'll have to make an entry with all zero
values so that I can search for it later. Then I could sum all of the
categories and if they equal zero then the Salesman didn't submit a report.

Again, unless I'm missing something (or if there is a better way to do
this,) then please let me know. Thanks for your help.

 

[Klatuu]

Yea, I see where I wasn't thinking.
Hopefully, you have a table that lists all the sales people that is
different from the table you are working with. That being the case, Join the
tables on the sales person's ID and look for a Null in the sales person's ID
in the activity table. The query would also need to be filtered on the date.