Manipulating datas on access table

[3rdeye]

Hi! Just started using access at work and don't know how to do a lot of things.. If I want to do something like below:

I have a table like this: where the first and second columns have duplicates in table. for example Column A would be a street number and Column B would be the name, and rest is information about that address.


87 james 3 h 7
87 james 5 6 4

When there are duplicates in rows(same address), I want to copy the rest of the information in that row excluding the address, so Column C, D and E, and paste it beside the first row(so F G H), so there are no duplicate address in rows and all the information about that address is stored in the row. Like below:

87 james 3 h 7 5 6 4

I have over million records...How do I do this? thank you!

[weekend00]

Do you want "3 h 7 5 6 4" in one field or seprated fields?

[3rdeye]

separate fields.

[weekend00]

Not easy to put seprate fields, how do you want the field names? f1,f2...?

[weekend00]

I am looking for similar post some weeks ago, but not found yet. Somebody wrote some code for this issue.

[3rdeye]

field names can be f1,f2...as of now, because I think I can edit that accordingly later...and does the code include removing the duplicates so there is only one row for each address? I realized I would also need that done simultaneously as well in order to have clean data. thank you so much for your help!!

[weekend00]

I think the better way is to create a new table(the code works like this). if you really don't need the old one after finish, just delete it.

But i can't find the post yet.