Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 201
  • Last Modified:

Query to return missing values

I am creating a database to track verifications on welds. The user is free to enter welds in whatever order they would like as welds are not always verified in order. I would like a query that would show all welds less than the greatest weld # that do not have a corresponding record in the database so that they can be listed for the user when they close the database to remind them that they still need to enter them. So far I have the attached code below however if I have records 1,2,3,9, and 10 it will only tell me that I am missing 4 and 11 instead of 4,5,6,7,and 8.
SELECT [Weld #]+1
FROM tblWeldLog AS T1
WHERE (((Exists (select * from tblWeldLog T2 where T2.[Weld #] = T1.[Weld #] + 1))=False));

Open in new window

0
FINGERHUT
Asked:
FINGERHUT
  • 2
1 Solution
 
jfmadorCommented:
There is no way to do that using a single table in a single query, because your database don't know which weld need to be done, also it cannot create rows that doesn't exists in your database. For example you have the tblWeldLog with two distinct entries 1 and 100, you want to get a dataset of 98rows.

To do it you need to have a table listing the welds to do like a Job table with column JobID, WeldID
where you can find your 10 rows representing the weld to do (from 1 to 10)

Then you will be able to do a query on your two table

SELECT * FROM tblJob j LEFT JOIN tblWeldLog w on j.WeldID = w.WeldID where w.WeldID is null

it will return you the job that don't have any log.

0
 
FINGERHUTAuthor Commented:
Could I perhaps create a table that just holds the values 1 - 9999 and create a query that finds all records that do not exist in the tblWeldLog but exist in NewTable and is not greater than the MAX value in tblWeldLog? If that could work could you help me write that query?

Thanks,

- FINGERHUT
0
 
FINGERHUTAuthor Commented:
Sorry, I was confused when I originally read your response but in the end it was exactly what I needed. Thanks.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now