#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2000
    Posts
    17
    Rep Power
    0

    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:

    Date Site
    1-28-2003 A
    1-28-2003 B
    1-28-2003 C
    1-28-2003 D
    1-28-2003 E
    1-27-2003 A
    1-27-2003 B
    1-27-2003 C
    1-27-2003 D
    1-27-2003 E
    1-26-2003 A
    1-26-2003 B
    1-26-2003 D
    1-26-2003 E
    1-25-2003 A
    1-25-2003 B
    1-25-2003 C
    1-25-2003 D
    1-25-2003 E

    ... 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.
  2. #2
  3. Wiking
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Sep 2000
    Location
    Sweden
    Posts
    3,608
    Rep Power
    26
    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...

    //NoXcuz
    UN*X is sexy!
    who | grep -i blonde | date; cd ~; unzip; touch; strip; finger; mount; gasp; yes; uptime; umount; sleep
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    No es importante
    Posts
    2,065
    Rep Power
    14
    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
    sitelocations table
    -----------------------
    A
    B
    C
    D
    E
    .....

    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:

    SELECT t1.field1
    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.
    El éxito consiste en una serie de pequeñas victorias día a día

    MySQL, MS SQL, MS ACCESS, Oracle Database Manager - http://victorpendleton.net/products/psdviewer.html
  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2002
    Posts
    5
    Rep Power
    0
    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:

    Select Site
    From REPORT
    Where Date = (date_value)

    -- to return all Sites for that particular date

    or

    Select Site
    From REPORT
    Where Date = (date_value)
    And
    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.

    Regards,
    Sean
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2000
    Posts
    17
    Rep Power
    0

    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.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    No es importante
    Posts
    2,065
    Rep Power
    14
    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.
    El éxito consiste en una serie de pequeñas victorias día a día

    MySQL, MS SQL, MS ACCESS, Oracle Database Manager - http://victorpendleton.net/products/psdviewer.html
  12. #7
  13. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2000
    Posts
    17
    Rep Power
    0
    Thanks again for your responses. I ended up doing something similar to what NoXcuz suggested, separately for each site.

IMN logo majestic logo threadwatch logo seochat tools logo