Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Find missing report using a query

Status
Not open for further replies.

0243906

Technical User
Jul 29, 2005
18
0
0
IE
I have a large table of reports all with their own individual report number which i set as the primary key, but some of the reports are missing. The reports numbers range from 1 to just above 4000 and about 500 are missing.My question is, is there any method of making a query that can give me a list of all the missing report numbers.
 
For anyone who is interested i found a solution to my problem. I created an excel table with the numbers 1 to 4000 in a column and then imported this spreadsheet into a table in access using 'Report Number' as the column heading (which is the same name given to the column contain the report numbers in my database. I then created a Query using this new table and the existing table in question. i joined the two 'report numbers' and set the join properties so that it would display all of new table and the report numbers section of the table in question. The repots that were missing were now blank. I then exported this file to excel and deleted all of the rows that had a match and was finally left with the list of missing reports.
A much easier way of doing this (I think?) would have been to use a unmatched query but this was not installed on my computer and the IT department in my company are very picky about changing anything on the network, but i have set up a new thread looking for the SQL for an unmatched query.
If anyone has any questions about my method just reply to this thread and ill try answer them.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top