Assign a Row Number

[voslica]

I have a table that contains a person's ID and their address (there may be multiple addresses per person). Each row of the table contains a different address. I am trying to assign a row number per address for each person (Address Count). Here's an example of what I need it to look like:

ID Address Count Address
1 1 111 Main Street
1 2 P.O. Box 111
2 1 555 Anywhere Street
3 1 12345 Market Avenue

 

[Kryst51]

What is the purpose of this row number? You're table thing you put in your post is a little unclear. Is it actually ID, Count, Address?

And, does you address table contain an autonumber pk for the address itself? Or any other kind of pk?

Can you explain what you are trying to do with this query?

 

[voslica]

Yes, it's ID, Count, Address. I'm trying to get a unique identifier per row per person. Each time there is a new person in the row, the Count should begin with 1. Does this help?

 

[Kryst51]

Yeah, what I really think you need is an added field in your table, that is an autonumber (ID) that is unique to that address, contact combination. Then set that autonumber as the pk of your table. this should make querying easier for you.

 

[voslica]

ID = Person's unique identifier in table
Address = Unique address of person
Count = Assign a number to each address row (begins with 1 each time the person ID changes).

 

[voslica]

How do I assign an autonumber in a query?

 

[Kryst51]

At the table level, not the query level, for the autonumber.

Why do you want the numbering? What are you trying to accomplish?

 

[voslica]

I'm sure I'm going about this the wrong way. What I'm trying to accomplish is turning my rows into columns. My original table has the addresses listed in rows and I need to get the addresses into columns.The end result is to have a table that will look like this:

ID --- Name --- Address1 ------------ Address2
01--- John D--- 111 Main ------------ P.O.Box123
02--- Mary J--- 555 AnyWhere
etc.

I am sure there is a better way to get the rows into columns. Any help is much appreciated.

 

[voslica]

and I can't use a crosstab query because it returns blank values in some of the columns...I need them to all start on the left hand side of the query

 

[Kryst51]

Is your ultimate goal a report?

 

[voslica]

The ultimate goal is a Table

 

[Kryst51]

But you already have a table. If you are wanting to store the information in the way you are saying, then that goes against normalization rules....

 

[voslica]

Would it help if I attached a sample document of what my table currently looks like and the result I am trying to accomplish?

 

[Kryst51]

Yeah, that would be great.

 

[voslica]

Attached is a sample document

 

[Kryst51]

OK, If you are looking to do this in a table (Which I think you are), that goes agains normalization rules. I suggest reading up on normalization. Access is not meant to handle data this way, and forcing it to do this would make it harder for you to analyze anything.

If you could explain why you need to do this, maybe I could help you figure out a better way to achieve the result without the poor normalization, I also suspect that your tables are not structured in the best manner, so you might want to clean you data out of your db, put some dummy data in and then attach it, that way some suggestions towards better structure can be made.

 

[c_smithwick]

Make a new empty table with four fields: ID (long), NAME (Text), ADDRESS1 (Text), ADDRESS2 (Text) and put the following Subroutine in a module. Run the Subroutine and it will pull all of your data from the first table into the new one

Public Sub makeNewTable()
 
Dim rstOriginal As DAO.Recordset
Dim rstNew As DAO.Recordset
Dim strTemp As String
Dim lngID As Long
 
Set rstOriginal = CurrentDb.OpenRecordset("SELECT tblTest.* FROM tblTest ORDER BY tblTest.ID") 'Change tblTest here to match your actual table name
Set rstNew = CurrentDb.OpenRecordset("tblTest1", dbOpenDynaset) 'Change tblTest1 here to match the name of your new four field table
 
With rstOriginal
 
    Do While Not .EOF
    
        If lngID <> ![ID] And strTemp <> ![Name] Then
            lngID = ![ID]
            strTemp = ![Name]
            rstNew.AddNew
            rstNew![ID] = ![ID]
            rstNew!Name = strTemp
            rstNew![ADDRESS1] = !Address
            rstNew.Update
        Else
            rstNew.AddNew
            rstNew![ID] = ![ID]
            rstNew!Name = strTemp
            rstNew![ADDRESS2] = !Address
            rstNew.Update
        End If
        
        .MoveNext
    Loop
    
End With

Set rstOriginal = Nothing
Set rstNew = Nothing
 
End Sub

Now you should have a table you can run a simple select query on and get the info in the format you need

 

[Kryst51]

Make a new empty table with four fields: ID (long), NAME (Text), ADDRESS1 (Text), ADDRESS2 (Text) and put the following Subroutine in a module. Run the Subroutine and it will pull all of your data from the first table into the new one

Public Sub makeNewTable()
 
Dim rstOriginal As DAO.Recordset
Dim rstNew As DAO.Recordset
Dim strTemp As String
Dim lngID As Long
 
Set rstOriginal = CurrentDb.OpenRecordset("SELECT tblTest.* FROM tblTest ORDER BY tblTest.ID") 'Change tblTest here to match your actual table name
Set rstNew = CurrentDb.OpenRecordset("tblTest1", dbOpenDynaset) 'Change tblTest1 here to match the name of your new four field table
 
With rstOriginal
 
    Do While Not .EOF
 
        If lngID <> ![ID] And strTemp <> ![Name] Then
            lngID = ![ID]
            strTemp = ![Name]
            rstNew.AddNew
            rstNew![ID] = ![ID]
            rstNew!Name = strTemp
            rstNew![ADDRESS1] = !Address
            rstNew.Update
        Else
            rstNew.AddNew
            rstNew![ID] = ![ID]
            rstNew!Name = strTemp
            rstNew![ADDRESS2] = !Address
            rstNew.Update
        End If
 
        .MoveNext
    Loop
 
End With
 
Set rstOriginal = Nothing
Set rstNew = Nothing
 
End Sub

Now you should have a table you can run a simple select query on and get the info in the format you need

Just out of curiosity (As I am happy you were able to answer the question ) Do you not think this goes against normalization? I want to know, so that I know better how to advise, so instead of drawing something out like I have been in this thread, I know that this isn't an issue.

 

[c_smithwick]

Technically yes, it does go against normalization but, in your case it seems you are working with already existing data and need to restructure it for a specific use. If you are designing a database from scratch, with considerations as to how best to store data and subsequently manipulate it, then you should normalize where appropriate. However, sometimes when you are working with pre-existing data that might not be in the best form you have to get creative.

 

[wjburke2]

This is not a uncomon task, I am sure there is are good examples out there. Do you have a data entry screen, that gives the compamy info and the address(s) listed for the company? You could use the index on the subform as your counter. You might check out northwind sample database.