Want to combine two fields into a 3rd unique field 1

[swimtiger]

Hello,

I have done something like this before with SPPS and Excel.

What I wish to do is combine the 'TYPE' field with the 'USERID' field into a new field called 'CODE.' So, if someone's 'TYPE' was 01 and their 'USERID' was 0997, their 'CODE' would be 010997. Doing this would help me greatly with reports that I generate monthly.

Thanks in advance.

Tim
timbibo@mail.com

 

[JPeters]

Would you like to do this via code? Or query? Within a form? There are several ways to do this. In a form you could use something like this for each record respectively.

After they update the ID and Type, do something like Me.Code = Type & UserID

or reverse them. Code is what the current code field (text box, whatever) has been named. Type is the name of the Type text box, UserID is the name of the userID textbox. It will combine the two and place them into the Code Text box. You could also use recordsets and accomplish this in VBA like this:

Sub RecordsetHelp()

Dim db as Database
Dim rs as Recordset
Set db = CurrentDB()
Set rs = db.OpenRecordset("tblMyTable&quot

With rs
rs.MoveFirst
Do While Not RS.EOF
rs.edit
rs!Code = rs!Type & rs!UserID
rs.Movenext
Loop
End With

End Sub

Something like that. I didn't test this, obvoiusly since I don't have your DB. But if it doesn't work for you, reply and I'll setup a test database and test it myself when I get a chance - busy day at work today

-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------

 

[swimtiger]

Josh thank you for your advice. I must admit that it is a little over my head. Is there a way that I can accomplish this via a query? Or maybe you can help me with the code that you typed in above. I am assuming that I can type that code into a new module?

 

[JPeters]

Well SwimTiger,
It really depends on how you want to implement this feature. We can do this a number of ways, but one will be most efficient. How exactly are you altering this data? In a form? Module? Get down to the nitty gritty, tell me what you're doing exactly, and we'll build from there.

-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------

 

[swimtiger]

Ok, I will try to explain this the best that I can. I want to create this new field 'Code' in the 'Placement' table. The reason that I want this 'Code' field is to be able to query it for duplicates. The number of duplicates would be an indicator of how successful pieces of our organization are. I would like to be able to accomplish this in the most simple way possible, as I am not familiar at all with SQL, VBA, and etc . . . I know that I could do similar things to this project with Excel. For example, I could say B3=(B1*10000)+B2. So, if B1 was equal to 01 and B2 was equal to 799, then B3 would look like 010799. I am trying my best to communicate. Please let me know if I can clarify anything.
THANKS

 

[swimtiger]

Also, I would prefer not to use forms because there are 12,000 records in this DB, and I am assuming that each record would have to be updated if we did this with a form?

 

[JPeters]

Every record being update...
We covered that with the loop function, though Swimtiger.

But let's talk about how to do this in a query. You'll need to add this new Code field to the table, then add your 3 fields (userid, type, code) into the query design mode.

Change the query type to an Update query, and in the 'Code' Update To box, add this:

[tblName]![Type] & [tblName]![UserID]

CHange tblName to your table name, and fieldnames respectively.
Now when you run this, it will combine the two, and throw it into the currently Empty code field. You can also run this anytime, it will update the records, but (unless they're new) it won't change the data (unless it's been changed).

Now build new queries off of your table and set criteria accordingly. ;-)

-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------

 

[swimtiger]

Thanks so much for your patience and advice. I am going to give this a try!