converting a list to fields

  • Thread starter Thread starter Zb Kornecki
  • Start date Start date
Z

Zb Kornecki

I am trying to convert data obtained from an outside system that comes in the
form of a list. Each account "acct" can have anywhere from 1-4 proc_codes as
shown in the first example below. I would like to convert this data to the
formate listed in the 2nd example below. Any help with this would be greatly
appreciated. Thank-you Zb

-----data list------
acct proc_code
12621167 8408
12621167 9300
12621167 E9179
12621168 5770
12621168 78701
12621168 25000
12621168 4019
12621169 462
12621169 7840
12621169 78900
12621169 78703

--------desired table
acct Proc1 proc2 proc3 proc4
12621167 8408
12621167 9300 E9179
12621168 5770 78701 25000 4019
12621169 462 7840 78900 78703
 
I am trying to convert data obtained from an outside system that comes in the
form of a list. Each account "acct" can have anywhere from 1-4 proc_codes as
shown in the first example below. I would like to convert this data to the
formate listed in the 2nd example below. Any help with this would be greatly
appreciated. Thank-you Zb

-----data list------
acct proc_code
12621167 8408
12621167 9300
12621167 E9179
12621168 5770
12621168 78701
12621168 25000
12621168 4019
12621169 462
12621169 7840
12621169 78900
12621169 78703

--------desired table
acct Proc1 proc2 proc3 proc4
12621167 8408
12621167 9300 E9179
12621168 5770 78701 25000 4019
12621169 462 7840 78900 78703
Click to expand...

Ummmm...

WHY?

You're taking properly normalized data and proposing to put it into a
non-normalized, wide-flat "spreadsheet". You might be able to do so using a
crosstab query, but your resulting table structure would still be incorrect!

What's the context?
 
John I have 3 other tables all with "acct" as the primary key. my goal is to
get all the data for each account into 1 record by joining the tables on the
acct field.
Also please loook at the tables again, I corrected a typo in the 1st acct
number so the table now has no duplicate acct fields so that it could be
used as the primary key. Also while every record would be required to have a
value in proc1 the remaining fields proc2-4 can have null values. Thank-you
Zb
 
This is something similair to what I am looking for to make a mail merge
much simpler. It won't be anything special other than a mail merge table
that gets created on the fly when the qry is ran.
 
John I have 3 other tables all with "acct" as the primary key.
Click to expand...

Again: WHY? One to one relationships such as this are uncommon.
my goal is to
get all the data for each account into 1 record by joining the tables on the
acct field.
Click to expand...

Still... why? What benefit do you get from having the data incorrectly
normalized?
Also please loook at the tables again, I corrected a typo in the 1st acct
number so the table now has no duplicate acct fields so that it could be
used as the primary key. Also while every record would be required to have a
value in proc1 the remaining fields proc2-4 can have null values. Thank-you
Click to expand...

You can do this with a really hairy self-join. Can you *guarantee* that there
will never be a proc5 (or is there some predictable upper limit)?

I spent a fair bit too much time trying to put together the self join with no
limits on the number of procs... maybe someone else can suggest something, but
I'm still inclined to argue that you're trying to solve the wrong problem!
 
This is something similair to what I am looking for to make a mail merge
much simpler. It won't be anything special other than a mail merge table
that gets created on the fly when the qry is ran.
Click to expand...

For a mail merge, would it be sufficient to return a comma-delimited string of
the subrecord values (rather than an arbitrary number of new fields)? If so
see

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