Access/VBA - is this posible?

  • Thread starter Thread starter xm
  • Start date Start date
X

xm

Hello,

I have a .csv file I load into Access. The file contains the same ID
multiple times, but with a different VAL. Is it possible to combine
the values from the VAL fields, so each ID only occurs one time, and
then put the data into a "new" table as shown below.


Table content after loading the .csv file
ID VAL
10 A
10 B
11 S
12 M
13 T
13 K
14 Q
15 O
16 P

Desired table content
ID VAL
10 A, B
11 S
12 M
13 T, K
14 Q
15 O
16 P


Currently I have a macro doing this in Excel, but I would like to
avoid this step completely, if possible.
 
Table content after loading the .csv file
ID VAL
10 A
10 B
11 S
12 M
13 T
13 K
14 Q
15 O
16 P

Desired table content
ID VAL
10 A, B
11 S
12 M
13 T, K
14 Q
15 O
16 P
Click to expand...

That seems to be going the wrong direction! You have a properly
normalized table wit atomic fields; you're proposing denormalizing it,
making it MUCH harder to search for data in the VAL field.

Why!?


John W. Vinson[MVP]
 
That seems to be going the wrong direction! You have a properly
normalized table wit atomic fields; you're proposing denormalizing it,
making it MUCH harder to search for data in the VAL field.

Why!?


John W. Vinson[MVP]
Click to expand...

John,

Thanks for your time!
The value in ID should be unique, and the value in VAL contains
informations that doesn't need to be searched on.
The data is not to be stored permanently, but instead to merge several
tables, and then output the data to a spreadsheet.

This table (ID,VAL) is to be joined with data from other tables where
ID is the matching parameter, so if I have ID multiple times in the
above table, I will also have this after the two tables are joined,
which I don't want.
Maybe there's a solution to this that is much easier?

Fundamentally the final output should be something like this...

TBL1
ID VAL
10 A, B
11 S
12 M
13 T, K
14 Q
15 O
16 P

TBL2
ID VAL2
10 aa
11 bb
12 cc
13 dd
14 ee
15 ff
16 gg

FINAL TBL
10 A,B aa
11 S bb
etc...
 
The value in ID should be unique, and the value in VAL contains
informations that doesn't need to be searched on.
The data is not to be stored permanently, but instead to merge several
tables, and then output the data to a spreadsheet.
Click to expand...

ok... thanks for the explanation. You don't need to put this into a
Table in order to merge it, though - you can export from a Query just
as easily as from a Table!

To convert the multiple records into a comma-separated string you'll
need some VBA code; see

http://www.mvps.org/access/modules/mdl0004.htm

for sample code.

John W. Vinson[MVP]
 
Back
Top