missing numbers in list (1 Viewer)

basilyos

Registered User.
Local time
Today, 09:52
Joined
Jan 13, 2014
Messages
252
hello guys,


i have two tables
table1 = customers info
table2 = custom invoices table


each customer will have invoices starting number 1


customer1 invoice 1 - 2 - 3 - 4
customer2 invoice 1 - 2 - 3 - 4


so now i have too much invoices i want to add to the system



when i enter all the data i want to check any customer what isthe missing invoice if existed


for example i entered the customer1 old invoices
from 1 to 100 then i forget to enter 101 and i continue from 102



so i want to check all the missing invoices between the first and last number


thanks in advance
 

Ranman256

Well-known member
Local time
Today, 12:52
Joined
Apr 9, 2015
Messages
4,339
make a table with ALL invoice#s.
in a query bring in the All invoice table and your data table,
join on invoice#
create an OUTER Join...
dbl-click the join line
set to : Show ALL records in tAllInvoices, SOME in tData
bring down the Inv# field from both tables

run query, there will be a null in tData.Inv# where its missing,
set the query criteria under it to NULL and ONLY see what is missing.
 

basilyos

Registered User.
Local time
Today, 09:52
Joined
Jan 13, 2014
Messages
252
thank you sir
i will try it and get back with the result
 

basilyos

Registered User.
Local time
Today, 09:52
Joined
Jan 13, 2014
Messages
252
cant get a result
do you have a sample for this query


thank you
 

CJ_London

Super Moderator
Staff member
Local time
Today, 16:52
Joined
Feb 19, 2013
Messages
16,553
there is another way

Code:
SELECT T1.*
FROM tblInvoices T1 LEFT JOIN tblInvoices T2 ON T1.CustID=T2.CustID [COLOR=red]AND T1.InvNum-1=T2.InvNum[/COLOR]
WHERE T2.Invnum is null and T1.invnum<>1

note the special join in red - write as an ordinary join in the query builder, then go to sql view to add the -1
 

plog

Banishment Pending
Local time
Today, 11:52
Joined
May 11, 2011
Messages
11,611
I think that will fail for consecutive omissions:

custid, invnum
1, 3
1, 8
1, 9

You can't generate records that don't exist. To find out specific omissions you need a data source to bump against. You can however know how many are missing in total without knowing specifically knowing the ones--aggregate query where you subtract the COUNT from the MAX.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 16:52
Joined
Feb 19, 2013
Messages
16,553
ah yes, good point. Also realised after posting that it returns the following number rather than the missing number - should have been

SELECT T1.CustID, T1.Invnum-1

but as you say, it will only report the last missing record if there are more than one consecutively.

The problem with the 'list' is if one customer has 3 invoices and another has 3000 - becomes a long list.

an alternative would be to use a group by query to return a range

Code:
SELECT T1.CustID, Max(nz([T2].[Invnum],0)+1) AS MissStart, [T1].[Invnum]-1 AS missEnd
FROM tblInvoices AS T1 LEFT JOIN tblInvoices AS T2 ON T1.CustID=T2.CustID [COLOR=red]AND T1.InvNum>T2.InvNum[/COLOR]
WHERE T1.invnum<>1
GROUP BY T1.CustID, [T1].[Invnum]-1
HAVING Max(nz([T2].[Invnum],0)+1)<=[T1].[Invnum]-1
 

basilyos

Registered User.
Local time
Today, 09:52
Joined
Jan 13, 2014
Messages
252
could i do this with two tables
table1 for custID and invoice id
table2 list of numbers between 1 and 500 so if table1 contains 200 invoice it will compare between the numbers from 1 to 200

or in another way i dont want to enter the customer ID in another table
i want to make it work with entering the invoices in two tables


please check my attachment the result should be this numbers (7-8-11-12) for Customer Number1
The Customer ID is named PID
 

Attachments

  • Data.accdb
    520 KB · Views: 36
Last edited:

plog

Banishment Pending
Local time
Today, 11:52
Joined
May 11, 2011
Messages
11,611
Using the database you provided this query will return the customer and missing invoices:

Code:
SELECT tbl_Personal_Sanctions_02.PID, tbl_Convert_Numbers.txt_Numbers
FROM tbl_Personal_Sanctions_02, tbl_Convert_Numbers
WHERE (((IIf(DMax("[Record_Number]","tbl_Personal_Sanctions_02","[PID]=" & [PID])<[txt_Numbers],1,0))=0))
GROUP BY tbl_Personal_Sanctions_02.PID, tbl_Convert_Numbers.txt_Numbers
HAVING (((Max(IIf([Record_Number]=[txt_Numbers],1,0)))=0));

And it will take forever to run on any non trivial amount of data.
 

Users who are viewing this thread

Top Bottom