make one record from many

  • Thread starter Thread starter supercooper
  • Start date Start date
S

supercooper

I have a table like so:
-----------------------------------------------------------------------------------
ID API PRODID NAME NUM
6231 00123 2365 SMITH 1-C
5692 00123 4595 JOE 3


I would like to get a result set that looks like so:

-----------------------------------------------------------------------------------
ID1 ID2 API PRODID1 PRODID2 NAME1 NAME2 NUM1 NUM2
6231 5692 00123 2365 4595 SMITH JOE 1-C 3


essentially grouping on API, but taking all other field values from
both records and aliasing them into new fields...to get everything with
the same API into one record.

any ideas?
 
Are there always two copies of each API value? Do you care which records
create the "1" values and which records create the "2"s?
 
Duane....there could be one to 5 or 6 copies of each API value. And no,
it doesn't matter which one creates 1, 2, 3, etc.

Lynn...I will look into the cross-tab

thanks for the replies....
 
The crosstab is probably the best choice. You will need to create a column
that identifies 1,2,3.... from your group based on some value in the table.
 
Duane...so would API be my row heading, and the 1,2,3 fields be the
column headings? Could you elaborate on what you mean by saying:

"You will need to create a column
that identifies 1,2,3.... from your group based on some value in the
table"

I'm new to cross-tabs, as if you couldnt tell...
 
You need to use DCount() to generate the 1,2,3... values for each API.

SELECT [like so].*, DCount("*","[like so]","API='" & [API] & "' AND ID<=" &
[ID]) as Rank
FROM [like so];

Once you get this working you can use the Rank to create column headings in
a crosstab query.

Is the result of this going into a report?
 
Duane, that worked. I might not have done it the most efficient of
ways, but I got the data I needed, in the format I needed. I wrote
crosstab queries to get the data into groups, then did a series of self
joins, calling the crosstabs, to get them all combined into one. Takes
a couple of minutes for the final query to run though. Thanks for the
help. Couldnt have figured it out without your help.
 
Rather than creating multiple crosstabs and then joining them into one, you
might be able to use my solution for a multi-value crosstab. Search google
groups on multi value crosstab group:*access.queries* author:hookom.
 
Back
Top