Need to Collapse Data in Access 1

[deevaetodin]

I need help on importing a flat text file into Access and specifying rules when importing.

My scenario involves data whereby we have a unit holder(id#)their address and the unit that they have purchased.

In all we offer 70 different units. In a listing of 500,000 unit holders we find that very often unit holders are duplicated. Meaning that if a client were to purchase unit 10 and unit 30 he would have two entries into our current table for each unit purchased.

What I need to do is to take this generated text file and import it into access so that duplicate unit holders are collapsed into one entry and that I preserve all of the units that they have currently purchased.

For example:
HOLDER ADDRESS UNIT
123456 123AnywhereStreet 10
123456 123AnywhereStreet 13
123456 123AnywhereStreet 60

would become:
#123456 123AnywhereStreet 10,13,60

Any advice would be of great assistance and appreciation.

 

[Databaseguy]

Make a customer ID table. The fields will be Holder and Address. make BOTH of the fields the primary key field. Run an append quey and drop all holders and adderesses in there. Access will give you a message saying "X" number of records couldn't be added because of key violations.This is OK, and actually what you want.

Now, make another table. Include Holder, Address and Unit and a customer ID field. Use an update query to update the customr ID field to the value stored in the customer ID table.Do this by joining Holder to Address in your query. Once you've got the customer ID in this table, delete the Holder and address feilds.

Now, you can include the customer table and the units table in a query, and join them by Customer ID. Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[deevaetodin]

Thanks, This is great.