Query: Creating a Unique ID (1 Viewer)

Hello and thank you in advance!

I apologize in advance for my lack of experience in how to create properly aligned examples below.

I have a month's worth of shipment data in a table. The first field in this table is "refnum" which is the unique identifier of this shipment. "refnum" is duplicated since the shipment is reporting vertically, not horizontally.

For instance...

refnum lane stop sequence Lane ID
12345 Saint Louis 1
12345 Chicago 2
12345 Memphis 3
98765 Reno 1
98765 Chino 2

So while the "refnum" is in there 3 times, it is only one shipment.

What I'd like Access to do is create a "Lane ID" that returns the same number by "refnum". So, in Excel, I would write a formula in cell D2 that states =IF(A1="refnum",1,IF(A2=A1,D1,D1+1)).

This formula would produce the below:

refnum lane stop sequence Lane ID
12345 Saint Louis 1 1
12345 Chicago 2 1
12345 Memphis 3 1
98765 Reno 1 2
98765 Chino 2 2

I'm familiar with IIF statements, I'm just not sure how to reference the actual table field.

I think your issue is your familiarity with Excel and lack of same with database/Access.

Looking at the results produced in your sample
since your new uniqueID is just another way to relate to refnum, so what eactly is the need for uniqueID.

Since we don't know the details of your application, my guess is an issue with data structure. Perhaps you could describe what shipment and related data mean to you and your business.

Whereas Excel sheets tend to be wide and short, database tables tend to be narrow and long. And while spreadsheets can contain info on multiple subjects, database tables are base on one concept/subject and multiple tables are related. Database techniques such as queries bring the data from different tables together for review/manipulation.

It sounds like you have a Shipment(s) that includes Items to be "dropped" at different Location(s). This would/could require different tables.

Good luck with your project.

@jdraw: Thank you for your response. I completely agree with your thinking and would have responded the same way. If the "refnum" is unique, what's the point of creating another unique ID.

My issue is that I can't control the way I get data at my company. I have to go thru a macro-enabled VBA sheet that spits out the data in a pre-formatted way that is 50% useless to me. I created the Access DB with a linked table to that Excel workbook and convert it to a usable format where I run it thru a batch process to compare costs. It's a report I then send to a customer showing, "You used Carrier A on this shipment, but Carrier B's cost was less." The batch process, however, takes the vertical lines and rolls them up into one horizontal row in Excel. That's another thing I can't control.

The idea behind the Unique ID was because a lot of times I can't share the "refnum" data with the customer so I have to remove it, but wanted to have something to reference back to the original data pull.

You could simply create a table

No do an import query of distinct refnums every month. Then use this table in all your queries.

My issue is that I can't control the way I get data at my company. I have to go thru a macro-enabled VBA sheet that spits out the data in a pre-formatted way that is 50% useless to me

Click to expand...

What if someone said to you--"I never get bananas from the store--the peels are so chewy and taste horrible. And don't even get me started on what the stickers do to my insides."

Just because things come the way they do, doesn't mean you are forever trapped by that. You need to set up your tables properly. Don't even consider how your data gets delivered to you, set up your tables properly. Most likely this will require a new table to truly hold unique shipments, then another table to hold all the sequences of it and possibly other tables to support your proper structure.

Then, you build an import process to take the raw, unpealed data and move it into your proper structure. This also solves the problem of not sharing refnums externally. You create autonumber primary keys and give those out which allow the recipent to identify a refnum, but not know its actual value.