combining duplicate data onto a single record

[craig.brenizer]

I have a table of data that has duplicate values in the pagenumber
field.
How can I combine the data so that the page numbers of duplicate part
numbers are on one record for that part number?
Here is what "table1" looks like now. Note that partnumbers 10-20 and
20-62 duplicate data, except for the pagenumbers, which are different.

partnumber price pagenumber
10-20 25.00 B5
10-20 25.00 C2
14-30 20.00 H2
20-62 35.20 B22
20-62 35.20 D5
49-63 1.23 A22
50-32 58.56 B19
Below is the way i want the table to look after the data are
recombined.

partnumber price pagenumber partnumber2
10-20 25.00 B5 C2
10-30 20.00 H2
20-62 35.20 B22 D5
49-63 1.23 A22
50-32 58.56 B19

How can I do this?

Thanks,
Craig

[dedejavu]

There may people out there who are smarter than I am and who have a
better answer, but here is one for you.
You only showed a max of 2 page numbers, but I am guessing that you may
have more. If you only have two, this could be done this way.
SELECT aPage1.partnumber,MaxPrice,pagenumber1,pagenumber2
FROM
(SELECT partnumber, Max(price) AS MaxPrice, Min(pagenumber) AS
pagenumber1
FROM Table1
GROUP BY partnumber) as aPage1 LEFT JOIN
(SELECT partnumber, Max(pagenumber) AS pagenumber2 FROM Table1
GROUP BY partnumber
HAVING count(pagenumber)>1) as aPage2 ON
aPage1.partnumber=aPage2.partnumber

If you need it to be dynamic (so that you can have x number of pages),
you'll need to buld a table so that access can build it's execution
plan for the crosstab. These are pretty easy and can be done without
the user ever knowing - using vb.

First Build a table:
SELECT Table1.partnumber, Table1.price, Table1.pagenumber, "PageNumber"
& (select count(pagenumber) from table1 as t WHERE
t.partnumber=table1.partnumber AND t.pagenumber<=table1.pagenumber) AS
PageCount INTO PageCount
FROM Table1;

Then run your crosstab query on the built table:
TRANSFORM Max(PageCount.pagenumber) AS MaxOfpagenumber
SELECT PageCount.partnumber, Max(PageCount.price) AS MaxPrice
FROM PageCount
GROUP BY PageCount.partnumber
PIVOT PageCount.PageCount;

Hope that's not too confusing
Pachydermitis

[craig.brenizer]

Well, that worked perfectly for the first table.
Thank you very much for your help.

Now, I have another table that has similar data, but an extra column.

This other table has a 4th column, "disc" . This is a discount code.
How do I run the queries you created perviously and list the "disc"
value once, not twice

For example, using the pervious data, here is the current table with
duplicates.

partnumber price pagenumber disc
10-20 25.00 B5 C
10-20 25.00 C2 C
14-30 20.00 H2 F
20-62 35.20 B22 G
20-62 35.20 D5 G
49-63 1.23 A22 C
50-32 58.56 B19 F

The new table would look like this...

partnumber price pagenumber partnumber2 disc
10-20 25.00 B5 C2 C
10-30 20.00 H2
F
20-62 35.20 B22 D5 G
49-63 1.23 A22
C
50-32 58.56 B19
F

I tried to modify the previous queries, but nothing worked.

Thanks again for you help on this.
Craig.

[dedejavu]

Craig,
I'm assuming you want the disc that corresponds to pagenumber1. If so
Just add table1 back in again and link the two.
The reason it didn't work is probably because access changes the '(' on
the derived tables or sub queries to a '['. It's retarded because as
long as you only view it in design view you are fine, but the moment
you go into sql view the query won't work until you change them back.

SELECT aPage1.partnumber, aPage1.MaxPrice, aPage1.pagenumber1,
aPage2.pagenumber2, table1.disc
FROM ((SELECT partnumber, Max(price) AS MaxPrice, Min(pagenumber) AS
pagenumber1 FROM Table1 GROUP BY partnumber) AS aPage1 LEFT JOIN
(SELECT partnumber, Max(pagenumber) AS pagenumber2 FROM Table1 GROUP
BY partnumber HAVING count(pagenumber)>1) AS aPage2 ON
aPage1.partnumber = aPage2.partnumber) INNER JOIN table1 ON
aPage1.pagenumber1 = table1.pagenumber;

Good Luck
Pachydermitis