Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2006
    Posts
    110

    Question Unanswered: Creating Query to find Missing Records

    Hi all, I am trying to create a query to find records that have not been received. I have a county table that I am matching it against, and a report field. For example, if Dade county hasnt reported for the month of April I want that to be returned in my query. Any ideas on how to do this? I am guessing I would need to do it in SQL but not sure how. Thanks guys..

    Chris

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    That's a little unclear - Could you clarify and perhaps tell us a little more about the structure of your tables?
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    select countyname from counties
    where not exists
    ( select 937 from countydata where countyid = counties.id and daMonth = 4 )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Oct 2006
    Posts
    110
    I'll try, I have a main table that lists all my fields I use on my form, including the county listing from the county table, and a report month from the report month table. The county and report month are seperate tables.

    What I am trying to find are counties that have not reported based on the county table and report month. Is it possible to find the county that has not been entered for a report month of april? Meaning that X county is not there for April.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the solution i gave you was correct already

    i invented as much information about the tables and columns as you omitted from your original question

    study my solution and apply it to your situation
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Oct 2006
    Posts
    110

    Smile

    This is what I was looking for. Thanks for your help. This way I cant just enter the report month, event, and event year, and it will show me what county does not meet the criteria ive entered.

    SELECT COUNTY.COUNTY_NAME
    FROM COUNTY
    WHERE (((COUNTY.COUNTY_NAME) Not In (SELECT COUNTY FROM FORMS WHERE REPORT_MONTH = [ENTER REPORT MONTH] AND EVENT_TYPE = [ENTER EVENT TYPE] AND EVENT_YEAR = [ENTER EVENT YEAR])));

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •