query to find missing numbers

[LD]

hey,

i have a query that retrieves 'flag numbers' for uncompleted orders that are yet to be delivered so we can put numbered pins into a map so that we can see the status of what is to be delivered and where. is there a way that i can run a query on my existing query to find which numbers from 1-50 dont appear so i can take out the completed flags?
i would run it off the completed yes/no box, but the existing query already uses the no to determines uncompleted orders and yes would bring up all the orders that had ever had the flag numbers, so it would show that they all needed to be pulled out!

thanks guys, hope you can help!

 

[meloncolly]

we would really need to know which fields with which criteria will highlight the records you want.

Can you supply that detail then supply the SQL you have that's close to what you want?

 

[LD]

Heres what i have at the moment

SELECT salesorder.[Flag number], salesorder.salesrec, salesorder.cusordernumber, Contacts1.CompanyName, salesorder.completed, salesorder.[Customer/school detail], salesorder.[Second address], Contacts1.Address, Contacts1.City
FROM Contacts1 INNER JOIN salesorder ON Contacts1.ContactID = salesorder.ContactID
WHERE (((salesorder.[Flag number])>0) AND ((salesorder.completed)=No))
ORDER BY salesorder.[Flag number];

In the sales order there is a box to type in a flag number and thats what the query finds. it then only shows the ones that are uncompleted so that the current location of the flag is displayed. I really cant get my head round how to show which flags aren't already being used to display something without showing up all the orders theyve ever shown, if you get me! hope this is enough!

 

[pritcham]

Hi

In your initial post you asked whether you could exclude flags 1-50 - could you not use a "NOT BETWEEN 1 AND 50" as the selection criteria against the flag field or am I missing something?

Martin

 

[LD]

hey,
i dont want to exclude numbers between 1 and 50 altogether, just find the numbers between 1 and 50 that aren't there, if you get what i mean?!

 

[pritcham]

I think I know what you mean (but may be way off - if so, nudge me back in the right direction)

What you're saying is that you want a list of the flag numbers that DO NOT appear in the results of your existing query:

(SELECT salesorder.[Flag number], salesorder.salesrec, salesorder.cusordernumber, Contacts1.CompanyName, salesorder.completed, salesorder.[Customer/school detail], salesorder.[Second address], Contacts1.Address, Contacts1.City 
FROM Contacts1 INNER JOIN salesorder ON Contacts1.ContactID = salesorder.ContactID 
WHERE (((salesorder.[Flag number])>0) AND ((salesorder.completed)=No)) 
ORDER BY salesorder.[Flag number];)

If that's the case then something like the following should do the trick:

Select salesorder.[Flag number] from salesorder where [Flag number] NOT IN (SELECT salesorder.[Flag number], salesorder.salesrec, salesorder.cusordernumber, Contacts1.CompanyName, salesorder.completed, salesorder.[Customer/school detail], salesorder.[Second address], Contacts1.Address, Contacts1.City 
FROM Contacts1 INNER JOIN salesorder ON Contacts1.ContactID = salesorder.ContactID 
WHERE (((salesorder.[Flag number])>0) AND ((salesorder.completed)=No)) 
ORDER BY salesorder.[Flag number])

Is that what you're trying to do?

Martin