January 28th, 2003, 03:49 PM
Identify missing data
I have a Microsoft Access 97 table with two fields, Date and Site. Data is reported to me on a daily basis from each of five sites. I would like to create a query which identifies which sites did not report data on any given day. For example:
... and so on
I would like the query to report that on 1-26-2003, site C did not report data to me.
Is there some simple way to do this...? Thanks in advance for your help.
January 28th, 2003, 04:20 PM
A combination of SQL/english:
You would need a list of dates that you left joined with the other table using Date where Site is null.
So there's no easy way unless using some calendar stuff in Access...
UN*X is sexy!
who | grep -i blonde | date; cd ~; unzip; touch; strip; finger; mount; gasp; yes; uptime; umount; sleep
January 29th, 2003, 09:29 AM
If you are asking what I think you are then here is one way you can approach this:
Create a table that holds the site locations
In conjunction with the existing table and the fact that you can perform subqueries in Access the following SQL will get you the sites that did not report for a given day:
FROM table1 t1
LEFT OUTER JOIN table2 t2
ON t1.field1 = t2.field2
WHERE t1.field1 NOT IN (SELECT field2 FROM table2 where field1 = #2003-01-03#)
Let me know if you need more help or if this was not what you were looking for.
January 29th, 2003, 02:25 PM
The table is normalized. The ‘Date’ column can be given a primary key constraint, since each date attribute is unique. The non-key column ‘Site’ is dependent on the ‘Date’ column. Therefore the original table should suffice.
Next step is to answer the question posed in a proposition form, something like:
A REPORT (the entity name possibly) was generated on ‘Date’ (date_value) by ‘Site’ (site_value), where Date and Site are column names (predicates of the proposition to be exact).
So in pseudo-SQL, the query would appear as:
Where Date = (date_value)
-- to return all Sites for that particular date
Where Date = (date_value)
Site = (site_value)
-- to return a particular site value for a particular day. If no rows returned then there is no report from the site for the day
Please let me know if I am missing anything.
January 29th, 2003, 05:30 PM
Identify missing data: Clarification
Thank you all for your suggestions. I'm a beginner with Access and SQL, so I need a little extra help interpreting your solutions. I think I need to clarify: The dates in the existing table (with fields <date> and <site>) go back to 1999. Each of the 12 sites is supposed to report data on a daily basis. I would like to generate a report which lists all the dates on which particular sites did not report data -- if I had a limited number of dates, I could run a query separately for each date or site, but in this case I have several years' worth of data so that approach doesn't seem practical. Thanks again for your help.
January 31st, 2003, 01:48 AM
Why not run the report based on sites since it seems from your test data that the number of sites is fairly small. So instead of date being your search criteria change that to siteID.
January 31st, 2003, 12:48 PM
Thanks again for your responses. I ended up doing something similar to what NoXcuz suggested, separately for each site.