Re: Missing Numbers

From: AHopper (anonymous_at_discussions.microsoft.com)
Date: 03/31/04 

Date: Wed, 31 Mar 2004 10:51:07 -0800

Tom,
It is very important that no numbers be missing, so I
would like to include the 468808 if possible. How can I
know that 468808 is also missing?
If I have the user choose the start and end of the series
on a form how would I incorporate that? Would this allow
468808 to be included?
Form "ItemNumberInquiry"
Combo boxes on form "StartOfSeries" and "EndOfSeries".

I haven't used Queries other than those created by Access
very much so I am having some difficulty understanding how
the query works.
Are Products T1 and FROM Products) T different Tables?
Are they variables that I need to declare?

What is the best way or a way to show the end user the
results (report, on screen)? Can I print the results?

Thank you very much for your response and help.

Allan

>-----Original Message-----
>Dear Allan:
>
>SELECT ItemNumber + 1 AS BeginSeries,
> (SELECT MIN(ItemNumber) - 1 FROM Products T1
> WHERE T1.ItemNumber > T.ItemNumber) AS EndSeries
> FROM (SELECT DISTINCT ItemNumber FROM Products) T
> WHERE NOT EXISTS (SELECT * FROM Products T1
> WHERE T1.ItemNumber = T.ItemNumber + 1)
> AND ItemNumber < (SELECT MAX(ItemNumber) FROM
Products)
>
>This will give you one row for your sample data, showing
BeginSeries =
>468802 and EndSeries = 468804, meaning numbers 468802
through 468804
>are missing. Close enough?
>
>Tom Ellison
>Microsoft Access MVP
>Ellison Enterprises - Your One Stop IT Experts
>
>
>On Wed, 31 Mar 2004 09:32:23 -0800, "AHopper"
><anonymous@discussions.microsoft.com> wrote:
>
>>I would like to find missing numbers and then print or
>>display the results. It is alright for numbers to be
>>entered more than once but numbers should not be missing.
>>
>>Table: Products
>>ItemNumbers
>>468800
>>468801
>>468801
>>468805
>>468806
>>468807
>>468807
>>468809
>>468810
>>
>>Missing Numbers 468802, 468803, 468804, and 468808
>>
>>Thank you in advance for your help.
>>
>>Allan
>>
>
>.
>

Relevant Pages

  • Re: How to set default date format in Access 07
    ... If the code compiles and the missing ref has been removed then no it should ... "Nih" wrote: ... Doug Steele, Microsoft Access MVP ... Do I put the =Dateas the default value in the control on the ...
    (microsoft.public.access.gettingstarted)
  • Re: Null Values in Aggregate Calculations
    ... >Also in the 2 querries that I use in the third querry use search criteria, ... If it were missing (possibly a missing ... >> Tom Ellison ... >> Microsoft Access MVP ...
    (microsoft.public.access.queries)
  • Re: Update query : Access 97
    ... Either you are missing something, ... Steve Schapel, Microsoft Access MVP ... Tom Ellison wrote: ... When there are two digits left of the ...
    (microsoft.public.access.queries)
  • Re: How to set default date format in Access 07
    ... unchecking/claering the missing reference. ... "Nih" wrote: ... Doug Steele, Microsoft Access MVP ... Do I put the =Dateas the default value in the control on the ...
    (microsoft.public.access.gettingstarted)
  • Re: Track_No in sub form
    ... Evi's response would be correct. ... Reference, so Access doesn't know what an NZ function is. ... instructuions from Evi or Doug to locate the missing reference. ... Microsoft Access MVP ...
    (microsoft.public.access.reports)