Appending data based on CustomerID

[Guest]

We have exported a .csv file from a program that runs all company operations.
Upon export a table is created that lists rows of customers with associated
information.

The key data point is the CustID. When we do an export to list equipment
purchased - any multiple purchases cause the CustID to be repeated down the
column with a single piece of equipment to follow in the same row. What we
want to do is to combine all equipment purchased by one customer to appear in
one row instead of being repeated.

So - we need to find an automatic way to make these changes since our
current database is very large and it is not practical to do it manually.

Below is a graphic example of what we have and what we want.

Have:

CustID Equiptpurch
1ADE0001 Item 1234
1ADE0001 Item 5465
1ADE0001 Item 8987

Want:

CustID Equiptpurch1 Equiptpurch2 Equiptpurch3
1ADE0001 Item 1234 Item5465 Item 8987

Looks simple to write - not so simple for us to accomplish. I am just not
sure the best technique to use in Access.

 

[John W. Vinson]

Have:

CustID Equiptpurch
1ADE0001 Item 1234
1ADE0001 Item 5465
1ADE0001 Item 8987

Want:

CustID Equiptpurch1 Equiptpurch2 Equiptpurch3
1ADE0001 Item 1234 Item5465 Item 8987

Looks simple to write - not so simple for us to accomplish. I am just not
sure the best technique to use in Access.

You'll need some VBA to do it. See
http://www.mvps.org/access/modules/mdl0004.htm
for some sample code you can adapt.

John W. Vinson [MVP]

 

[Guest]

John,

That was it. Thank you so much! I couldn't believe how fast you answered.

 

[Guest]

John, correct me if i'm wrong...but without resorting to the vba, as
highlighted in your response, could the desired effect be accomplished by
importing or linking both csv files into an access database and defining a
1-to-many relationship between them? if so, how d you set keys on an
imported/linked table

(i don't have access to Access at home, so not able to replicate
this....just trying to refresh my my memory!!!)

jimaldo

 

[John W. Vinson]

John, correct me if i'm wrong...but without resorting to the vba, as
highlighted in your response, could the desired effect be accomplished by
importing or linking both csv files into an access database and defining a
1-to-many relationship between them? if so, how d you set keys on an
imported/linked table

Not easily - Access makes it easy to display one record per line on a screen
or page, but it's considerably harder to spread one record per *column*
across.

Crosstab queries can do this, but they're not really appropriate for this
question.

John W. Vinson [MVP]

 

[Guest]

thanks again John, i'll have a wee read at the vba code you mentioned. could
come in handy for me as i never ever got to grips with crosstabs!
fortunately i never had any call for them, but it's always worth finding out
how they work...just in case!

jimaldo