Transpose and append data?

[peishun]

I have a very large text file that I've imported into Access. One of
the fields imported allows multiple values and causes extra rows. I
want to append this extra data to the field above it so to consolidates
the data to one row for each record.

Here's how it looks now

ID Drug Pharm Class
1 Acetaminophen Analgesic
2 Aspirin Analgesic
NSAID
Antipyretic
3 Ibuprofen Analgesic
NSAID
4 Naproxen Analgesic
5 etc etc1
etc2
etc3

Here's how I'd like it to look if possible

ID Drug Pharm Class
1 Acetaminophen Analgesic
2 Aspirin Analgesic, NSAID, Antipyretic
3 Ibuprofen Analgesic, NSAID
4 Naproxen Analgesic
5 etc etc1, etc2, etc3

I'm a total newbie to Access and appreciate any help!

 

[Douglas J. Steele]

I'd advise very strongly against what you're suggesting. Denormalizing the
data like that will make it extremely difficult to work with.

Leave the data the way it is. Add a concatenation function to your
application, and use it in a query. You can then use the query wherever you
would otherwise have used the denormalized data.

Take a look, for instance, at
http://www.mvps.org/access/modules/mdl0008.htm at "The Access Web". Copy
everything between Code Start and Code End, and paste it into a new Module
(make sure it's not a Class Module). Do not name the module fConcatFld:
Modules cannot be named the same as routines within them.

Now, create a new query that returns just the ID and Drug values (1 row for
each unique combo: you do this by looking at the properties of the query,
and setting the UniqueValues property to True). Let's say the original table
is Table1, and the query is Query1.

Create a new query based on Query1. Add ID and Drug to the grid, then in a
empty cell on the Field row, type

Classes: fConcatFld("Table1", "ID", "Pharm Class", "string", [ID])

 

[John Nurick]

When it comes to importing data from a text file like this the problem
is that the meaning of the data is dependent on the lines being kept in
the original order. But relational databases like Access don't promise
to do that: in other words, after importing the data there's no way of
telling whether the record
, , NSAID
belongs to Acetaminophen, Aspirin or some other drug.

This has to be fixed before importing (by modifying the text file) or
during the import process (which means using custom code to do the
import).

I'd do it by changing the text file to be like this, filling every gap
by repeating the value from the previous line:

ID Drug Pharm Class
1 Acetaminophen Analgesic
2 Aspirin Analgesic
2 Aspirin NSAID
2 Aspirin Antipyretic
3 Ibuprofen Analgesic
3 Ibuprofen NSAID
4 Naproxen Analgesic

This can easily be imported into Access and subsequently normalised into
three tables

tblDrugs
DrugID*
DrugName (with index to prevent duplicate entries)

tblPharmClasses
PharmClass*

tblDrugsPharmClasses
DrugID*
PharmClass*

It's always very important to think hard about data structures. For
example, some drugs have more than one name even if you ignore trade
names. Maybe you need

tblDrugs
DrugID*
SystematicName
'maybe other fields unique to the drug

tblDrugNames
DrugID
DrugName

so you can have multiple names for a single drug.