Access 2003 - Transpose

  • Thread starter Thread starter Lucson
  • Start date Start date
L

Lucson

Hello, I heve been beating around the bush with Union Query. I am giving up
and need some help here.
I have a query that extract lots of data, I need to transpose one column and
change how my report output looks.
Example:

SerialNumber Quantity DATE PRICE BOMPart#
SS4422 12 01/12/07 A AC2-456
SS4422 12 01/12/07 A BH1-313
SS4422 12 01/12/07 A HHT-190
SS4422 12 01/12/07 A CHR-123
SS4422 12 01/12/07 A OLF-890
SR2233 6 05/14/07 B QPR-233
SR2233 6 05/14/07 B SCC-231
SR2233 6 05/14/07 B LOP-245
SR2233 6 05/14/07 B TOL-357
SR2233 6 05/14/07 B SLP-951
AND321 8 06/09/07 C WER-258
AND321 8 06/09/07 C DGO-569
AND321 8 06/09/07 C CFR-248
AND321 8 06/09/07 C HFS-256
AND321 8 06/09/07 C XSP-268
AND321 8 06/09/07 C BPY-456

I need this:
SerialNumber Quantity DATE PRICE BOMPart# BOMPart# BOMPart# BOMPart# BOMPart# BOMPart# BOMPart# BOMPart#
SS4422 12 01/12/07 A AC2-456 BH1-313 HHT-190 CHR-123 OLF-890
SR2233 6 05/14/07 B QPR-233 SCC-231 LOP-245 TOL-357 SLP-951
AND321 8 06/09/07 C WER-258 DGO-569 CFR-248 HFS-256 XSP-268 BPY-456
 
TRANSFORM SUM(quantity)
SELECT serialNumber, date, price
FROM tableName
GROUP BY serialNumber, date, price
PIVOT [BOMPart#]



Is not exactly the result of what you typed, as output, but maybe it is
close enough for what you need?

Vanderghast, Access MVP
 
First Query to get a ranking of the part numbers for each serial number and
Part Number.

SELECT A.SerialNumber, A.[BOMPart#],
Count(B.BOMPart#) as Rank
FROM SomeTable as A INNER JOIN SomeTable as B
ON A.SerialNumber = B.SerialNumber
AND A.[BOMPart#] >= B.[BomPart#]
GROUP BY A.SerialNumber, A.[BomPart#]

Second query uses the above saved query and your table to get the desired
results in a crosstab query.

TRANSFORM First(A.[BomPart#]) as PartNum
SELECT SomeTable.SerialNumber, SomeTable.Quantity, SomeTable.[Date]
, SomeTable.Price
FROM SomeTable INNER JOIN QRank
ON SomeTable.SerialNumber = Qrank.SerialNumber
AND SomeTable.[BomPart#] = QRank.[BomPart#]
GROUP BY SomeTable.SerialNumber, SomeTable.Quantity, SomeTable.[Date]
, SomeTable.Price
PIVOT Rank



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Thanks Michel, I twicked your suggestion to match what I need. I got this
"Too many crosstab column headers (1331)"

Does Access (or crosstab query) has a limited number of column headers?
--
Always Learning


Michel Walsh said:
TRANSFORM SUM(quantity)
SELECT serialNumber, date, price
FROM tableName
GROUP BY serialNumber, date, price
PIVOT [BOMPart#]



Is not exactly the result of what you typed, as output, but maybe it is
close enough for what you need?

Vanderghast, Access MVP


Lucson said:
Hello, I heve been beating around the bush with Union Query. I am giving
up
and need some help here.
I have a query that extract lots of data, I need to transpose one column
and
change how my report output looks.
Example:

SerialNumber Quantity DATE PRICE BOMPart#
SS4422 12 01/12/07 A AC2-456
SS4422 12 01/12/07 A BH1-313
SS4422 12 01/12/07 A HHT-190
SS4422 12 01/12/07 A CHR-123
SS4422 12 01/12/07 A OLF-890
SR2233 6 05/14/07 B QPR-233
SR2233 6 05/14/07 B SCC-231
SR2233 6 05/14/07 B LOP-245
SR2233 6 05/14/07 B TOL-357
SR2233 6 05/14/07 B SLP-951
AND321 8 06/09/07 C WER-258
AND321 8 06/09/07 C DGO-569
AND321 8 06/09/07 C CFR-248
AND321 8 06/09/07 C HFS-256
AND321 8 06/09/07 C XSP-268
AND321 8 06/09/07 C BPY-456

I need this:
SerialNumber Quantity DATE PRICE BOMPart# BOMPart# BOMPart# BOMPart#
BOMPart# BOMPart# BOMPart# BOMPart#
SS4422 12 01/12/07 A AC2-456 BH1-313 HHT-190 CHR-123 OLF-890
SR2233 6 05/14/07 B QPR-233 SCC-231 LOP-245 TOL-357 SLP-951
AND321 8 06/09/07 C WER-258 DGO-569 CFR-248 HFS-256 XSP-268 BPY-456
Click to expand...
Click to expand...
 
Yes, you have a limit of 255 total fields, in a query.


Vanderghast, Access MVP


Lucson said:
Thanks Michel, I twicked your suggestion to match what I need. I got this
"Too many crosstab column headers (1331)"

Does Access (or crosstab query) has a limited number of column headers?
--
Always Learning


Michel Walsh said:
TRANSFORM SUM(quantity)
SELECT serialNumber, date, price
FROM tableName
GROUP BY serialNumber, date, price
PIVOT [BOMPart#]



Is not exactly the result of what you typed, as output, but maybe it is
close enough for what you need?

Vanderghast, Access MVP


Lucson said:
Hello, I heve been beating around the bush with Union Query. I am
giving
up
and need some help here.
I have a query that extract lots of data, I need to transpose one
column
and
change how my report output looks.
Example:

SerialNumber Quantity DATE PRICE BOMPart#
SS4422 12 01/12/07 A AC2-456
SS4422 12 01/12/07 A BH1-313
SS4422 12 01/12/07 A HHT-190
SS4422 12 01/12/07 A CHR-123
SS4422 12 01/12/07 A OLF-890
SR2233 6 05/14/07 B QPR-233
SR2233 6 05/14/07 B SCC-231
SR2233 6 05/14/07 B LOP-245
SR2233 6 05/14/07 B TOL-357
SR2233 6 05/14/07 B SLP-951
AND321 8 06/09/07 C WER-258
AND321 8 06/09/07 C DGO-569
AND321 8 06/09/07 C CFR-248
AND321 8 06/09/07 C HFS-256
AND321 8 06/09/07 C XSP-268
AND321 8 06/09/07 C BPY-456

I need this:
SerialNumber Quantity DATE PRICE BOMPart# BOMPart# BOMPart# BOMPart#
BOMPart# BOMPart# BOMPart# BOMPart#
SS4422 12 01/12/07 A AC2-456 BH1-313 HHT-190 CHR-123 OLF-890
SR2233 6 05/14/07 B QPR-233 SCC-231 LOP-245 TOL-357 SLP-951
AND321 8 06/09/07 C WER-258 DGO-569 CFR-248 HFS-256 XSP-268 BPY-456
Click to expand...
Click to expand...
Click to expand...
 
John, I think your approach uses crosstab, which as I discovered yesterday,
has a limit on crosstab row. I want to transpose BOMPart#, the BOMPart# can
sometimes contains 2000 distinct items unde one SerialNumber.

My inclined has been so far to use Union query
--
Always Learning


John Spencer said:
First Query to get a ranking of the part numbers for each serial number and
Part Number.

SELECT A.SerialNumber, A.[BOMPart#],
Count(B.BOMPart#) as Rank
FROM SomeTable as A INNER JOIN SomeTable as B
ON A.SerialNumber = B.SerialNumber
AND A.[BOMPart#] >= B.[BomPart#]
GROUP BY A.SerialNumber, A.[BomPart#]

Second query uses the above saved query and your table to get the desired
results in a crosstab query.

TRANSFORM First(A.[BomPart#]) as PartNum
SELECT SomeTable.SerialNumber, SomeTable.Quantity, SomeTable.[Date]
, SomeTable.Price
FROM SomeTable INNER JOIN QRank
ON SomeTable.SerialNumber = Qrank.SerialNumber
AND SomeTable.[BomPart#] = QRank.[BomPart#]
GROUP BY SomeTable.SerialNumber, SomeTable.Quantity, SomeTable.[Date]
, SomeTable.Price
PIVOT Rank



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Hello, I heve been beating around the bush with Union Query. I am giving up
and need some help here.
I have a query that extract lots of data, I need to transpose one column and
change how my report output looks.
Example:

SerialNumber Quantity DATE PRICE BOMPart#
SS4422 12 01/12/07 A AC2-456
SS4422 12 01/12/07 A BH1-313
SS4422 12 01/12/07 A HHT-190
SS4422 12 01/12/07 A CHR-123
SS4422 12 01/12/07 A OLF-890
SR2233 6 05/14/07 B QPR-233
SR2233 6 05/14/07 B SCC-231
SR2233 6 05/14/07 B LOP-245
SR2233 6 05/14/07 B TOL-357
SR2233 6 05/14/07 B SLP-951
AND321 8 06/09/07 C WER-258
AND321 8 06/09/07 C DGO-569
AND321 8 06/09/07 C CFR-248
AND321 8 06/09/07 C HFS-256
AND321 8 06/09/07 C XSP-268
AND321 8 06/09/07 C BPY-456

I need this:
SerialNumber Quantity DATE PRICE BOMPart# BOMPart# BOMPart# BOMPart# BOMPart# BOMPart# BOMPart# BOMPart#
SS4422 12 01/12/07 A AC2-456 BH1-313 HHT-190 CHR-123 OLF-890
SR2233 6 05/14/07 B QPR-233 SCC-231 LOP-245 TOL-357 SLP-951
AND321 8 06/09/07 C WER-258 DGO-569 CFR-248 HFS-256 XSP-268 BPY-456
Click to expand...
Click to expand...
 
Yes, that does use a crosstab. Since you can never have more than 255 columns
in a query or a table, you have a problem.

Also, you are going to have a problem with forms and reports because they are
also limited to 255 fields. You could overcome that limit to an extent by
using subforms/subreports - each of which would also be limited to 255 fields.

Good luck on figuring out a solution.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Thanks John, I have been working with the team today to narrow the choice to
about 200 column. Once they finish, I will set the query with their list as
criteria. From there I will try to transpose.
 
Back
Top