make one record from many

[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?

 

[Duane Hookom]

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?

 

[Lynn Trapp]

You might be able to do that with a cross-tab query (check Help for that),
but it could also get pretty unwieldy depending on how many records you have
per individual APIs.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[supercooper]

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....

 

[Duane Hookom]

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.

 

[supercooper]

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...

 

[Duane Hookom]

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?

 

[supercooper]

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.

 

[Duane Hookom]

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.