Create a Number for Unique Values (1 Viewer)

[t_bard]

Hello,

I need some help writing a query.

I have a table with street sign information. It has 10 fields. I would like to create a new field (an 11th) and have a new ID number for each unique entry in one of the 10 fields field.

In other words, I want to create a new field called UNIQUE IDs. In the next field (MAIN STREET) there might several mentions of a Street Name (like LEROY STREET) (because there are several entries (multiple traffic signs) for each block). I would like a Query that would give a Unique ID number (like X-1, X-2, etc.) every time the value (Text) in the MAIN STREET field changes. So, if the ID for LEROY STREET is X-1, when the MAIN STREET field changes to CANAL STREET (or even LEROY AVENUE) the next consecutive ID is calculated (X-2)

And for you super initiated SQL writers. I would eventually like to create a Unique ID when any one value in as many a 4 fields changes a unique ID# is created and added to a new field, but that may be too much to dream for.

Any help would be greatly appreciated.

Thanks for looking

 

[TexanInParis]

It's possible to do if there's a Primary Key defined on the table.


John Viescas, author
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

 

[t_bard]

Thanks for responding.

Does it matter which field I make the primary key? Once I do that, what next?

 

[MSAccessRookie]

It would help to see the structure of the table. That way we all have the same frame of reference for discussion

 

[TexanInParis]

A Primary Key, by definition, has a unique value in each row. If you choose a field that does not have unique values, you will get an error when you try to save the table definition. If you have more than one field that has unique values, then no, I suppose it doesn't matter.

John Viescas, author
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

 

[t_bard]

1. I did create an automatic numbering field which, as I understand you, I could make into the primary key, But please take a look at a sample of the Tables I am using (uploaded). I would eventually like to create a Unique ID when any one group of values (4) in the MAIN, FROM, TO and SOS fields change..
2. I look at is as: it's as if you were driving down a street. The MAIN street is the one you are driving on. The FROM is the one you just passed. The TO is the one coming up. And the SOS is the left or right (or N,S,E,W) that you are looking at for signs. When any one of those 4 values change, I would like to create a unique ID#. Like a unique ID # for each block.

 

[Dhamdard]

Would this help? Build a select query on the mentioned table. Drop down all fields. In coloumn 11th, type this:

UniqueKey: [field1]&"-"&[field2]&"-X"&Dcount("("*")","Table Name Here")+1

If value for field 1 is A, field 2 is B and the total number of entries in the table is 5, the above expression will give you this result: A-B-X-5

This is just a hint. Play around it and make it best suitable to your need.

Regards,
Dad

 

[TexanInParis]

To get unique rows, you can do this:

1) Make a copy of your table without the ID field - copy to the clipboard, paste structure only, and then delete the ID field. (See table TestReverse in the attached sample.)

2) In the new table, make the combination of the fields 1_SGNH_Hra_Main_Street_X, 1_SGNH_Hra_From_Street, 1_SGNH_Hra_To_Street, and 1_SGNH_Hrap_Sos the Primary Key.

3) Build an Append query on your original data and choose the new table as the target. (See query qAppUniqueStreets in the attached sample.)

4) Run the query and ignore the error message.

This resulted in 71 unique rows.

John Viescas, author
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

 

[t_bard]

OK, first I want to thank both you guys for looking at my problem. Dad, I was unable to get your query to work but that probably has more to do with my inexperience than anything else. Texan in Paris, you are right on the path. That Table of 71 unique names (across the 4 fields) looked perfect. And forging through the error message was definitely cool. Not sure yet how I would get Access to give a same unique number to all the rows with matching 4 fields, but maybe you have a suggestion for that as well.
I hope.

 

[TexanInParis]

Ah, then that begs the question: Why do you want to keep the duplicate rows?

 

[t_bard]

Because my ulterior motive is, I ultimately want to compare all the Sign Descriptions (hence signs) from an OLD Table to a NEW one. There are many Signs (and Sign Descriptions) for each block (with the same MAIN, FROM, TO, & SOS fields), so those 4 fields repeat anywhere from 2 to 30 times (a row for each sign) for any given block. I figure with a unique ID for each block I can link the IDs from the OLD and the NEW tables and view the changes on the types of signs side by side. This is important b/c the data for a whole block is completely updated when just 1 sign changes. If I can see them side-by-side I will be able to see this much quicker. We are talking about 120,000 signs.
I did hire someone to do this for me, but he took several weeks and never even got close to what you did on the first try. I thought it was simpler than it obviously is. So did he, I guess.
What do you think?

 

[TexanInParis]

OK, here's what I did:

1) I performed the steps 1-4 listed before. (I named the table TestReverseUnique this time.)

2) I copied TestReverseUnique to the clipboard and pasted it Structure Only as TestReverseUniqueSeq.

3) I opened TestReverseUniqueSeq in Design view, removed the Primary Key (but not the fields), added a new field called Seq and made it an autonumber.

4) I made the new Seq field the Primary Key. I defined a Unique index on the four fields that were previously the Primary Key.

5) I ran a query (qAppUniqueStreets) to copy the rows from TextReverseUnique to TestReverseUniqueSeq. The AutoNumber Seq field now has unique ascending numbers in it.

6) I added a Seq field to the original table (Copy Of TEST_REVERSE_1-11_6-13_w/out_M_1) and defined it as a Long Integer.

7) I created an update query (qUpdSeqNos) that joined TestReverseUniqueSeq with the original table on the four "direction" fields. Because I defined a unique index on the four fields in TestReverseUniqueSeq, this query is updatable. I told the query to copy the Seq field from TestReverseUniqueSeq.

The original table now has all the data with a new Seq field that is unique for all rows that match on the four direction fields.

 

[t_bard]

Damn, that is so beautiful to behold! I believe you have saved me many, many hours. And you explain it so patiently! Thank you!!!
May I ask one more favor? Because you have designed this technique, I have to ask, when I compare the OLD Table to the NEW, there will be some UNIQUE IDs that have more rows on the OLD Table than the NEW and some that have have more on the NEW Table than the OLD. I believe this is a case for a "Double Outer Join" something I have failed at previously. Do you have a best strategy for that?

 

[TexanInParis]

A FULL OUTER JOIN is a bit tough to do in Access. You have to UNION a LEFT join with a RIGHT join. Can you explain in more detail what it is you're trying to achieve?

 

[t_bard]

[FONT=&quot]Hi again, Texan
To answer your question, I would like to be able to "link" the Unique IDs (or blocks) of an OLD Table and a NEW Table so that I can see the Sign Description (or Signs) fields next to each other for easy comparison.
Very often the changes to the signs on a block are insubstantial. For example an extra sign with the same regulations. But all the rows for that Unique ID (that you created) are updated whether one sign changes or all of them change. This means a lot of unnecessary reading and comparison.
Sometimes the list of Signs will be longer on the NEW side, sometimes on the OLD. The Sign field would be empty when the differences are uneven. The extras on one side would be opposite empty or blank fields on the other. These would need to stay with their Unique IDs (blocks) so that they can be compared at a glance.
Even though there would still be a lot of reading, seeing OLD and NEW sets of Sign descriptions next to each other would make it much faster.
Unfortunately, there is no geocoding of this data and, apparently no other way to read it.
What do you think?
[/FONT][FONT=&quot][/FONT]

 

[TexanInParis]

The way I set up a method to pull out the unique groups, assign a number, then put that number back in the original table will not guarantee that the numbers will match what you have in any "old" table. I don't see how assigning the numbers will help you "match" to an old table unless you take the unique list and use it to generate the numbers in both.

 

[t_bard]

Texan,

The MAIN STREET, FROM STREET, TO STREET, SIDE OF STREET, SIGN DESCRIPTIONS are almost completely uniform. They are so uniform, they are archaic sometimes (not reflecting a street name change). The Unique IDs you created will be the same, especially because I am comparing the Sign updates for 6 - 12 month periods. Ideally, I could give a unique ID to every block and they will be pretty constant for many years, but I am only trying to do this for the updated records for now, so they will be close to 100% accurate for the periods I am working with. The way the Sign is described is also the same (almost always, of course) and can be viewed at a glance. They are based on codes that unfortunately, I do not have access to (Freedom of Information Requests, etc.)
I believe the Unique IDs will match very well and seeing the old and new butted up against each other will save enormous amounts of time and eyestrain.
Does this make sense?

 

[TexanInParis]

Yup. As long as you can create a "master" table with all the possible Main / From / To / Side / Sign combinations with a unique ID, you should be able to use that as a "driver" table to assign the ID to rows in other tables and then compare them.

 

[t_bard]

What I am more modestly aiming for now, in the case of my short-term comparisons, is the IDS can work w/out a Master Table. I sort out the blocks that have updates in the latest Table. Then I search those same blocks from the previous Table and make two smaller tables to compare. I give them both Unique IDs which are really shared IDs because the 4 conditions (or IDs are the same). Then I align them as mentioned above.
“Sometimes the list of Signs will be longer on the NEW side, sometimes on the OLD. The Sign field would be empty when the differences are uneven. The extras on one side would be opposite empty or blank fields on the other. These would need to stay with their Unique IDs (blocks) so that they can be compared at a glance.”
Can you possibly recommend a strategy for the more short-term goal of aligning the IDs like this?

Thanks

 

[t_bard]

Hi Texan,

What I forgot to mention was the blocks are consistent in my two sub-Tables (for comparison) because they are based on the ORDER # field which is unique for each block. Unfortunately, these order numbers do not have any location indentifiers in them. They change completely with each update.
FYI.