Solved How to sequence duplicate values in a table?

[euzica2]

Hi everyone!

I was hoping you could lend some insight on how to go about the problem I'm having below:

Departure Line value
1245 1
1245 2
1235 1
1235 2
1235 3
5233 1

I would like to create a sequencing line value column for duplicate departure values. I attempted to use the Count() function but unfortunately, this is the output I have:

Departure Line value
1245 2
1245 2
1235 3
1235 3
1235 3
5233 1

Any insight would be sincerely appreciated!
-Eldar Uzicanin

 

[theDBguy]

Hi. You'll have to add a criteria to your Count() column. Look up subqueries.
Sent from phone...

 

[June7]

Need a unique identifier field. Autonumber should serve. https://www.access-programmers.co.uk/forums/threads/sequential-numbers-in-a-query.312520/

Review similar threads list at bottom.

 

[arnelgp]

add Autonumber (call it ID) field to your Table.
always, i recommend it (as well as others i supposed).
it always come handy.

you create a query (with autonumber) like this:

select Departure, DCount("1", "yourTable", "Departure='" & [Departure] & "' And [ID] <= " & [ID]) As [Line] from yourTable

 

[The_Doc_Man]

If you have no other way of separating one record from another, make one. I'm adding my voice to those who suggest "Autonumber." The thing about it is that it doesn't matter if an occasional autonumber gets dropped because of failing to save an entry correctly. As long as you use it ONLY for sorting internally, that doesn't technically violate the purpose of an autonumber. However, if there is ANY other field in that list that could be used to establish an order, you would want to use it to establish record order as a secondary sort.

The problem you have with what you showed us is that there is no apparent reason to CARE whether one appears before the other. I'm saying, more precisely, that within the data as shown, there is no obvious reason to care. For example, if you had a date/time field or some other type of sequencing field then it would make a difference. But as shown, you have no secondary key to delineate the difference between two records. You are asking about synthesizing a "running" count field but have no particular basis on which to provide the ordering necessary for a sub-query that COULD impose those numbers.

 

[euzica2]

I sincerely appreciate the prompt responses everyone!

The unique identifier is the combination of the Shipment Reference Number and HTS Code (Which is unique to the same Shipment Reference Number but shared with other Shipment Reference Numbers). Here is what I found work for me:

SELECT OrderID, [Shipment Reference Number], (SELECT COUNT(*)+1 FROM [Copy Of Final_Table] WHERE T1.[Shipment Reference Number] = [Shipment Reference Number] AND t1.[OrderID]<[OrderID]) AS [Commodity Line Number]
FROM [Copy Of Final_Table] AS T1;

This gave me the sequencing above that I was looking for!

 

[theDBguy]

I sincerely appreciate the prompt responses everyone!

View attachment 84799

The unique identifier is the combination of the Shipment Reference Number and HTS Code (Which is unique to the same Shipment Reference Number but shared with other Shipment Reference Numbers). Here is what I found work for me:

SELECT OrderID, [Shipment Reference Number], (SELECT COUNT(*)+1 FROM [Copy Of Final_Table] WHERE T1.[Shipment Reference Number] = [Shipment Reference Number] AND t1.[OrderID]<[OrderID]) AS [Commodity Line Number]
FROM [Copy Of Final_Table] AS T1;

This gave me the sequencing above that I was looking for!

Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.