Creating a field 2

[mvernon]

It has been many years since I have worked with Access and am looking for some help as I am really lost and don't know if this is even possible.

On a daily basis I will be importing a .csv file containing customer information. The information is presented in a form where if something is purchased, the first item has all of the customers information, subsequent purchases on that day the some of the information is not there. What they ordered is, and their user name. I want to assign a consecutive number based on the day the records were imported to each customer.. (Note not record).

That way I can put everything customer x ordered into the same box.

Help...

Really confused.

 

[Remou]

It the user name a safe bet? Do you have either a list of user names or a means of identifying the user name in the imported information (eg username: xyxy )? If so, you could use VBA.

 

[mvernon]

Yes there is a common field that I can use. Does anyone have an example or can you point me to an example that I can use as a reference to accomplish this type of programming?

 

[Remou]

Please supply some sample data. Please include details of the common field? That is, do you have a table listing user names?

 

[mvernon]

Sure..

Here you go everything below the line is part of my test csv file on the data I will actually get..
----------------------------------
User Id,Buyer Fullname,Buyer Phone Number,Buyer Email,Buyer Address 1,Buyer Address 2,Buyer City,Buyer State,Buyer Zip,Buyer Country,Item Title,Custom Label,Quantity,Sale Price,Shipping and Handling
john_doe,John Doe,700-555-1212,john.doe@doe.net,1234 Somewhere St,,Lost Town,ND,12345,United States,,,6,$171.40 ,$32.99
john_doe,,,,,,,,,,Bought Something Expensive,Expensive,1,$144.00 ,
john_doe,,,,,,,,,,Something a little cheaper,Cheaper,2,$8.99 ,
john_doe,,,,,,,,,,Very Cheap,Cheap,3,$3.14 ,
jane_doe,Jane Doe,700-555-1111,jane.doe@doe.net,1234 Somewhere Else Lane,,Hidden Town,ND,12346,United States,Plant,Plant,1,$29.99 ,$11.99
jennifer_doe,Jennifer Doe,700-555-2222,jen.doe@doe.us,1234 Other Place Rd,,Small Town,ND,12347,United States,Something Plastic,Plastic,1,$5.94 ,$8.00
jim_doe,Jim Doe,700-555-3333,jim@doe.ws,1255 Forever Lost Circle,,Large Town,ND,12348,United States,,,15,$184.00 ,$62.99
jim_doe,,,,,,,,,,Basketball,Basketball,1,$27.99 ,
jim_doe,,,,,,,,,,Football,Football,1,$28.99 ,
jim_doe,,,,,,,,,,Golf Ball,Golf Ball,1,$5.94 ,
jim_doe,,,,,,,,,,Golf Tee,Tee,1,$4.99 ,
jim_doe,,,,,,,,,,Hoop,Hoop,1,$11.99 ,
jim_doe,,,,,,,,,,Paint,Paint,1,$6.99 ,
jim_doe,,,,,,,,,,Chauk,Chauk,3,$7.99 ,
jim_doe,,,,,,,,,,Scratch Remover,Remover,1,$1.99 ,
jim_doe,,,,,,,,,,Dent Remover,Dent,2,$12.99
jim_doe,,,,,,,,,,Battery,Battery,1,$3.99
jim_doe,,,,,,,,,,Button,Button,1,$10.19
jim_doe,,,,,,,,,,String,String,1,$30.99

 

[Remou]

Try something on these lines:

'Requires reference to Microsoft DAO 3.x Object Library
Dim db As Database
Dim rs As DAO.Recordset

strTableName = "WhatEverItIs"

Set db=CurrentDB
strSQL="ALTER TABLE " & strTableName & " ADD COLUMN UserKey Long"
db.Execute strSQL, dbFailOnError

strSQL="SELECT UserKey,[User ID] AS UID " _
& "FROM strTableName " _
& "ORDER BY [User ID]"

Set rs=db.Openrecordset(strSQL)

intCounter=1
Do While Not rs.EOF
   strUser=rs!UID

   Do While rs!UID = strUser
      rs.Edit 
      rs.UserKey=intCounter
      rs.Update

      rs.MoveNext
      If rs.EOF Then Goto ExitHere
   Loop

   intCounter=intCounter+1
Loop

ExitHere:
rs.Close
Set rs=Nothing
Set db=Nothing

 

[mvernon]

I am getting a compile error, method or data member not found on rs.UserKey

I got this originally with the code, so I seperated the alter table into its own procedure from the select statement.

The alter statement did add the new table column so I'm not understanding I'm getting the same error.

 

[MajP]

I assume that is a field so the syntax would be

rs!userKey
or
rs.fields("userKey")

 

[mvernon]

That is exacty what it needed. It works like a charm..

THANK YOU!!! SO MUCH!!