Sequence missing to be tracked (1 Viewer)

Shiv

Registered User.
Local time
Today, 13:59
Joined
Dec 23, 2018
Messages
16
Greetings to everyone !

Column A containing "sequence no" and column B containing User detail so i would like to know that which are the nos are missing of each user.

Required result is provided in the column C of attached table.

Thanks in advance for the cooperation received.
 

Attachments

  • sequence missing.accdb
    640 KB · Views: 56

June7

AWF VIP
Local time
Today, 00:29
Joined
Mar 9, 2014
Messages
5,425
Options:

1. Need a dataset of all possible pairs of No and User. Create a table with a record for each number that is required. For the example data, that would be a table with 12 records - call it Numbers. Then queries:

Query1:
SELECT DISTINCT Table1.User, Numbers.[No]
FROM Numbers, Table1
ORDER BY Table1.User, Numbers.[No];

Query2:
SELECT Query1.User, Query1.[No], Table1.[No], Table1.User
FROM Table1 RIGHT JOIN Query1 ON (Table1.User = Query1.User) AND (Table1.[No] = Query1.[No]);

This won't provide the Result column but you will see gaps in the data.

2. VBA code
 

isladogs

MVP / VIP
Local time
Today, 08:29
Joined
Jan 14, 2017
Messages
18,186
Here's a variation on the previous answer
I'm assuming this is only a subset of all your data as you already have the answers for this dataset!

NOTE: 'No' is a reserved word in Access - UserNo would be better

1. First make a table tblAll with all possible combinations of User & No fields - there are 24 possible values here.
If your original data includes all values of each field in one or more records use a make table query qryMakeAll with 2 copies of Table1 & a Cartesian join
Code:
SELECT DISTINCT Table1.User, Table1_1.[No] INTO tblALL FROM Table1, Table1 AS Table1_1;

However with your example data, that isn't possible as you have missing 'No' values. You could add the missing values manually or use VBA similar to this to create the table

Code:
Sub MakeTableAll()

Dim I As Integer, J As Integer
Dim MinNo As Integer, MaxNo As Integer
Dim MinUser As Integer, MaxUser As Integer
Dim rst As DAO.Recordset

On Error GoTo Err_Handler

'enclose [No] in [] as its a reserved word
MinNo = DMin("[No]", "Table1", "[No]>0")
MaxNo = DMax("[No]", "Table1", "[No]>0")

MinUser = DMin("User", "Table1", "User>0")
MaxUser = DMax("User", "Table1", "User>0")

'Debug.Print MinNo, MaxNo, MinUser, MaxUser

CurrentDb.Execute "CREATE TABLE tblAll(User INT, No INT)"

Set rst = CurrentDb.OpenRecordset("tblAll", dbOpenDynaset)
'DoCmd.OpenRecordset "tblAll"

With rst
    .MoveFirst
    For I = MinUser To MaxUser
        For J = MinNo To MaxNo
            .AddNew
            !User = I
            !No = J
            .Update
        Next J
    Next I
    
End With

Set rst = Nothing

Exit_Handler:
    Exit Sub
    
Err_Handler:
    'err=3010 - table already exists
    'err=3021 = table is empty
    If Err = 3010 Or Err = 3021 Then Resume Next
    
    MsgBox "Error " & Err.Number & " " & Err.Description
    Resume Exit_Handler
End Sub

NOTE: The error handling isn't perfect but it should be enough for your purposes

Now use an unmatched query tblMisdsingUserNo on tblAll & Table1
SELECT tblAll.User, tblAll.[No]
FROM tblAll LEFT JOIN Table1 ON (tblAll.[No] = Table1.[No]) AND (tblAll.User = Table1.User)
WHERE (((Table1.[No]) Is Null) AND ((Table1.User) Is Null));

See attached
 

Attachments

  • sequence missing_v2_CR.zip
    24.6 KB · Views: 55

Shiv

Registered User.
Local time
Today, 13:59
Joined
Dec 23, 2018
Messages
16
Options:

1. Need a dataset of all possible pairs of No and User. Create a table with a record for each number that is required. For the example data, that would be a table with 12 records - call it Numbers. Then queries:

Query1:
SELECT DISTINCT Table1.User, Numbers.[No]
FROM Numbers, Table1
ORDER BY Table1.User, Numbers.[No];

Query2:
SELECT Query1.User, Query1.[No], Table1.[No], Table1.User
FROM Table1 RIGHT JOIN Query1 ON (Table1.User = Query1.User) AND (Table1.[No] = Query1.[No]);

This won't provide the Result column but you will see gaps in the data.

2. VBA code


I hereby attaching the data to get the missing numbers . i have deleted the '1492337' of lane 3 from the dB so it shall be missing no sure .
 

isladogs

MVP / VIP
Local time
Today, 08:29
Joined
Jan 14, 2017
Messages
18,186
@Shiv
Nothing attached
Did you look at my solution in post #3? You should be able to use that 'as is'
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 03:29
Joined
Feb 28, 2001
Messages
27,001
The last time I had this problem it was for a check register. I tried a few queries but the most reliable thing was some VBA that just read the number on the check and compared to a variable holding the expected number. If there was a gap, I could find it pretty quick because that IS a single-table, simple-sort type of problem. If I remember this detail correctly, I was using an old Intel 80486 box at the time on Windows 3.1 and Access 2.0, so they weren't blindingly fast. But it still took only a few seconds for a 3000-record scan in VBA.
 

deletedT

Guest
Local time
Today, 08:29
Joined
Feb 2, 2019
Messages
1,218
Now use an unmatched query tblMisdsingUserNo on tblAll & Table1
SELECT tblAll.User, tblAll.[No]
FROM tblAll LEFT JOIN Table1 ON (tblAll.[No] = Table1.[No]) AND (tblAll.User = Table1.User)
WHERE (((Table1.[No]) Is Null) AND ((Table1.User) Is Null));

See attached

Very clever way of using a query like this.
 

Shiv

Registered User.
Local time
Today, 13:59
Joined
Dec 23, 2018
Messages
16
@Shiv
Nothing attached
Did you look at my solution in post #3? You should be able to use that 'as is'

Dear Friend

missing no to be tracked in the attached database therefore please your support is needed on the same.
 

Attachments

  • New Microsoft Access Database.accdb
    520 KB · Views: 62

isladogs

MVP / VIP
Local time
Today, 08:29
Joined
Jan 14, 2017
Messages
18,186
you've supplied another set of numbers but the idea is still the same.
I provided a complete working solution a week ago in post #3. Have you tried it? If not, do so.
If you did try it, what issues did you have and what did you do to try and solve those issues?
 

Shiv

Registered User.
Local time
Today, 13:59
Joined
Dec 23, 2018
Messages
16
you've supplied another set of numbers but the idea is still the same.
I provided a complete working solution a week ago in post #3. Have you tried it? If not, do so.
If you did try it, what issues did you have and what did you do to try and solve those issues?

Dear Friend,
As suggested, i had tried post no 3 but i am not understanding how it is to be executing therefore i need your favour once again.

Query to be designed on the DB which is posted by me in post #8.

Thanks in advance for the nice support.
 

isladogs

MVP / VIP
Local time
Today, 08:29
Joined
Jan 14, 2017
Messages
18,186
As already stated I provided a complete solution to your original example 3 weeks ago which you have ignored until now

I have looked at your new database which is slightly different to the original example.
Field tno is unique - it could be a primary key there are 400 records
Field lane has 8 values 1-6 ,66, 67 - each used with multiple tno.

I have no idea what you mean by missing numbers in this context.
Every record in field tno has 7 missing values - all except the actual value.

As before you could use a cartesian join to create a tblALL with 400*8=3200 records
Then run an unmatched query on that and the original table - there will be 2800 unmatched records.
If that's what you mean, you should be able to adapt my code.

If not, then as I don't understand what you are asking, I cannot assist any further.
As you do (hopefully) understand your own project, try adapting my code accordingly if it seems applicable
Good luck with your project
 

Users who are viewing this thread

Top Bottom