Transpose - Help with crosstab query

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

Lucson

Hello,

I have a query with 5 to 6 columns and 2000+ rows. The main column is a
serial number column, one serial number has no more than 95 part numbers.
Each serial number is unique with set of part numbers. I like to pivot the
part numbers column to horizontally display part numbers, per serial number.
I created a UnionQuery, then I created a CrossTab query, When I run my
crosstab query, I get a message that have too many row. I thought my crosstab
will give me a new table with each serial number, with its other elements in
one row.
Example:
Original Union Query:
Serial# Description BuildDate PartNumber Comments
A2 Desc1 11/14/2007 123AAA Special Order
A2 Desc1 11/14/2007 456ABA Special Order
A2 Desc1 11/14/2007 BAA213 Special Order
A1 DescP 04/02/2008 21A25B City of Livermore
municipality
A1 DescP 04/02/2008 215B23 City of Livermore
municipality

Here is what I want:
Serial# Description BuildDate PartNumber PartNumber PartNum Comments
A2 Des1 11/14/07 123AAA 456ABA BAA213
Special Order
A1 DesP 04/02/07 21A25B 215B23
City of Liv...

Could someone tell what I am doing wrong? or the best way to approach this?

-
Always Learning
 
I think you need to create a sequence/rank column that can be used as the
Column Heading for your crosstab. The expression for this column would be
something like:
ColHead: "PartNum" & DCount("*","[Original Union Query]","[Serial#]='" &
[Serial#] & "' AND PartNumber <='" & [PartNumber] & "'")

Your resulting ColHead could then be used as the Column Heading in the
crosstab query.
 
Duane,

I am doing something wrong with your syntax...
I set this inside my Transform but it won't even read..

More guidance please

ColHead: "ModuleName" & DCount("*","[UnionQuery]","[Serial]='" &
[Serial] & "' AND ModuleName <='" & [ModuleName] & "'")

--
Always Learning


Duane Hookom said:
I think you need to create a sequence/rank column that can be used as the
Column Heading for your crosstab. The expression for this column would be
something like:
ColHead: "PartNum" & DCount("*","[Original Union Query]","[Serial#]='" &
[Serial#] & "' AND PartNumber <='" & [PartNumber] & "'")

Your resulting ColHead could then be used as the Column Heading in the
crosstab query.
--
Duane Hookom
Microsoft Access MVP


Lucson said:
Hello,

I have a query with 5 to 6 columns and 2000+ rows. The main column is a
serial number column, one serial number has no more than 95 part numbers.
Each serial number is unique with set of part numbers. I like to pivot the
part numbers column to horizontally display part numbers, per serial number.
I created a UnionQuery, then I created a CrossTab query, When I run my
crosstab query, I get a message that have too many row. I thought my crosstab
will give me a new table with each serial number, with its other elements in
one row.
Example:
Original Union Query:
Serial# Description BuildDate PartNumber Comments
A2 Desc1 11/14/2007 123AAA Special Order
A2 Desc1 11/14/2007 456ABA Special Order
A2 Desc1 11/14/2007 BAA213 Special Order
A1 DescP 04/02/2008 21A25B City of Livermore
municipality
A1 DescP 04/02/2008 215B23 City of Livermore
municipality

Here is what I want:
Serial# Description BuildDate PartNumber PartNumber PartNum Comments
A2 Des1 11/14/07 123AAA 456ABA BAA213
Special Order
A1 DesP 04/02/07 21A25B 215B23
City of Liv...

Could someone tell what I am doing wrong? or the best way to approach this?

-
Always Learning
Click to expand...
Click to expand...
 
Can you provide your actual SQL view? What do you see if you change this from
a crosstab to a select query? What do you actually see? I don't understand
"won't even read".

--
Duane Hookom
Microsoft Access MVP


Lucson said:
Duane,

I am doing something wrong with your syntax...
I set this inside my Transform but it won't even read..

More guidance please

ColHead: "ModuleName" & DCount("*","[UnionQuery]","[Serial]='" &
[Serial] & "' AND ModuleName <='" & [ModuleName] & "'")

--
Always Learning


Duane Hookom said:
I think you need to create a sequence/rank column that can be used as the
Column Heading for your crosstab. The expression for this column would be
something like:
ColHead: "PartNum" & DCount("*","[Original Union Query]","[Serial#]='" &
[Serial#] & "' AND PartNumber <='" & [PartNumber] & "'")

Your resulting ColHead could then be used as the Column Heading in the
crosstab query.
--
Duane Hookom
Microsoft Access MVP


Lucson said:
Hello,

I have a query with 5 to 6 columns and 2000+ rows. The main column is a
serial number column, one serial number has no more than 95 part numbers.
Each serial number is unique with set of part numbers. I like to pivot the
part numbers column to horizontally display part numbers, per serial number.
I created a UnionQuery, then I created a CrossTab query, When I run my
crosstab query, I get a message that have too many row. I thought my crosstab
will give me a new table with each serial number, with its other elements in
one row.
Example:
Original Union Query:
Serial# Description BuildDate PartNumber Comments
A2 Desc1 11/14/2007 123AAA Special Order
A2 Desc1 11/14/2007 456ABA Special Order
A2 Desc1 11/14/2007 BAA213 Special Order
A1 DescP 04/02/2008 21A25B City of Livermore
municipality
A1 DescP 04/02/2008 215B23 City of Livermore
municipality

Here is what I want:
Serial# Description BuildDate PartNumber PartNumber PartNum Comments
A2 Des1 11/14/07 123AAA 456ABA BAA213
Special Order
A1 DesP 04/02/07 21A25B 215B23
City of Liv...

Could someone tell what I am doing wrong? or the best way to approach this?

-
Always Learning
Click to expand...
Click to expand...
Click to expand...
 
Here is my new SQL crosstab, When I run it, it tells me that I have 1300 row
When I use your suggestion, it won't even execute...

TRANSFORM Count(Qty)
SELECT Serial,Qty,Make, BuildDate, ModuleName
FROM [UnionQuery]
GROUP BY Serial,Qty,Make, BuildDate, ModuleName
PIVOT [ModuleName];
--
Always Learning


Duane Hookom said:
Can you provide your actual SQL view? What do you see if you change this from
a crosstab to a select query? What do you actually see? I don't understand
"won't even read".

--
Duane Hookom
Microsoft Access MVP


Lucson said:
Duane,

I am doing something wrong with your syntax...
I set this inside my Transform but it won't even read..

More guidance please

ColHead: "ModuleName" & DCount("*","[UnionQuery]","[Serial]='" &
[Serial] & "' AND ModuleName <='" & [ModuleName] & "'")

--
Always Learning


Duane Hookom said:
I think you need to create a sequence/rank column that can be used as the
Column Heading for your crosstab. The expression for this column would be
something like:
ColHead: "PartNum" & DCount("*","[Original Union Query]","[Serial#]='" &
[Serial#] & "' AND PartNumber <='" & [PartNumber] & "'")

Your resulting ColHead could then be used as the Column Heading in the
crosstab query.
--
Duane Hookom
Microsoft Access MVP


:

Hello,

I have a query with 5 to 6 columns and 2000+ rows. The main column is a
serial number column, one serial number has no more than 95 part numbers.
Each serial number is unique with set of part numbers. I like to pivot the
part numbers column to horizontally display part numbers, per serial number.
I created a UnionQuery, then I created a CrossTab query, When I run my
crosstab query, I get a message that have too many row. I thought my crosstab
will give me a new table with each serial number, with its other elements in
one row.
Example:
Original Union Query:
Serial# Description BuildDate PartNumber Comments
A2 Desc1 11/14/2007 123AAA Special Order
A2 Desc1 11/14/2007 456ABA Special Order
A2 Desc1 11/14/2007 BAA213 Special Order
A1 DescP 04/02/2008 21A25B City of Livermore
municipality
A1 DescP 04/02/2008 215B23 City of Livermore
municipality

Here is what I want:
Serial# Description BuildDate PartNumber PartNumber PartNum Comments
A2 Des1 11/14/07 123AAA 456ABA BAA213
Special Order
A1 DesP 04/02/07 21A25B 215B23
City of Liv...

Could someone tell what I am doing wrong? or the best way to approach this?

-
Always Learning
Click to expand...
Click to expand...
Click to expand...
 
I don't see the SQL of the crosstab that I suggested with DCount(). You also
didn't state what results you got if you changed the crosstab to a select
query.

--
Duane Hookom
Microsoft Access MVP


Lucson said:
Here is my new SQL crosstab, When I run it, it tells me that I have 1300 row
When I use your suggestion, it won't even execute...

TRANSFORM Count(Qty)
SELECT Serial,Qty,Make, BuildDate, ModuleName
FROM [UnionQuery]
GROUP BY Serial,Qty,Make, BuildDate, ModuleName
PIVOT [ModuleName];
--
Always Learning


Duane Hookom said:
Can you provide your actual SQL view? What do you see if you change this from
a crosstab to a select query? What do you actually see? I don't understand
"won't even read".

--
Duane Hookom
Microsoft Access MVP


Lucson said:
Duane,

I am doing something wrong with your syntax...
I set this inside my Transform but it won't even read..

More guidance please

ColHead: "ModuleName" & DCount("*","[UnionQuery]","[Serial]='" &
[Serial] & "' AND ModuleName <='" & [ModuleName] & "'")

--
Always Learning


:

I think you need to create a sequence/rank column that can be used as the
Column Heading for your crosstab. The expression for this column would be
something like:
ColHead: "PartNum" & DCount("*","[Original Union Query]","[Serial#]='" &
[Serial#] & "' AND PartNumber <='" & [PartNumber] & "'")

Your resulting ColHead could then be used as the Column Heading in the
crosstab query.
--
Duane Hookom
Microsoft Access MVP


:

Hello,

I have a query with 5 to 6 columns and 2000+ rows. The main column is a
serial number column, one serial number has no more than 95 part numbers.
Each serial number is unique with set of part numbers. I like to pivot the
part numbers column to horizontally display part numbers, per serial number.
I created a UnionQuery, then I created a CrossTab query, When I run my
crosstab query, I get a message that have too many row. I thought my crosstab
will give me a new table with each serial number, with its other elements in
one row.
Example:
Original Union Query:
Serial# Description BuildDate PartNumber Comments
A2 Desc1 11/14/2007 123AAA Special Order
A2 Desc1 11/14/2007 456ABA Special Order
A2 Desc1 11/14/2007 BAA213 Special Order
A1 DescP 04/02/2008 21A25B City of Livermore
municipality
A1 DescP 04/02/2008 215B23 City of Livermore
municipality

Here is what I want:
Serial# Description BuildDate PartNumber PartNumber PartNum Comments
A2 Des1 11/14/07 123AAA 456ABA BAA213
Special Order
A1 DesP 04/02/07 21A25B 215B23
City of Liv...

Could someone tell what I am doing wrong? or the best way to approach this?

-
Always Learning
Click to expand...
Click to expand...
Click to expand...
 
Here we go Duane, here is one of the many attempts

TRANSFORM Count(Qty)
SELECT Serial, Qty, Make, BuildDate, ModuleName
FROM UnionQuery
ColHead: "ModuleName" & DCount("*","[UnionQuery]","[Serial]='" &
[Serial] & "' AND ModuleName <='" & [ModuleName] & "'")
GROUP BY Serial, Qty, Make, BuildDate, ModuleName
PIVOT [ModuleName];

--
Always Learning


Duane Hookom said:
I don't see the SQL of the crosstab that I suggested with DCount(). You also
didn't state what results you got if you changed the crosstab to a select
query.

--
Duane Hookom
Microsoft Access MVP


Lucson said:
Here is my new SQL crosstab, When I run it, it tells me that I have 1300 row
When I use your suggestion, it won't even execute...

TRANSFORM Count(Qty)
SELECT Serial,Qty,Make, BuildDate, ModuleName
FROM [UnionQuery]
GROUP BY Serial,Qty,Make, BuildDate, ModuleName
PIVOT [ModuleName];
--
Always Learning


Duane Hookom said:
Can you provide your actual SQL view? What do you see if you change this from
a crosstab to a select query? What do you actually see? I don't understand
"won't even read".

--
Duane Hookom
Microsoft Access MVP


:

Duane,

I am doing something wrong with your syntax...
I set this inside my Transform but it won't even read..

More guidance please

ColHead: "ModuleName" & DCount("*","[UnionQuery]","[Serial]='" &
[Serial] & "' AND ModuleName <='" & [ModuleName] & "'")

--
Always Learning


:

I think you need to create a sequence/rank column that can be used as the
Column Heading for your crosstab. The expression for this column would be
something like:
ColHead: "PartNum" & DCount("*","[Original Union Query]","[Serial#]='" &
[Serial#] & "' AND PartNumber <='" & [PartNumber] & "'")

Your resulting ColHead could then be used as the Column Heading in the
crosstab query.
--
Duane Hookom
Microsoft Access MVP


:

Hello,

I have a query with 5 to 6 columns and 2000+ rows. The main column is a
serial number column, one serial number has no more than 95 part numbers.
Each serial number is unique with set of part numbers. I like to pivot the
part numbers column to horizontally display part numbers, per serial number.
I created a UnionQuery, then I created a CrossTab query, When I run my
crosstab query, I get a message that have too many row. I thought my crosstab
will give me a new table with each serial number, with its other elements in
one row.
Example:
Original Union Query:
Serial# Description BuildDate PartNumber Comments
A2 Desc1 11/14/2007 123AAA Special Order
A2 Desc1 11/14/2007 456ABA Special Order
A2 Desc1 11/14/2007 BAA213 Special Order
A1 DescP 04/02/2008 21A25B City of Livermore
municipality
A1 DescP 04/02/2008 215B23 City of Livermore
municipality

Here is what I want:
Serial# Description BuildDate PartNumber PartNumber PartNum Comments
A2 Des1 11/14/07 123AAA 456ABA BAA213
Special Order
A1 DesP 04/02/07 21A25B 215B23
City of Liv...

Could someone tell what I am doing wrong? or the best way to approach this?

-
Always Learning
Click to expand...
Click to expand...
Click to expand...
 
The ColHead column would be used as your Column Heading.
TRANSFORM Count(Qty)
SELECT Serial, Make, BuildDate
FROM UnionQuery
GROUP BY Serial, Make, BuildDate
PIVOT "ModuleName" & DCount("*","[UnionQuery]","[Serial]='" &
[Serial] & "' AND ModuleName <='" & [ModuleName] & "'");

--
Duane Hookom
Microsoft Access MVP


Lucson said:
Here we go Duane, here is one of the many attempts

TRANSFORM Count(Qty)
SELECT Serial, Qty, Make, BuildDate, ModuleName
FROM UnionQuery
ColHead: "ModuleName" & DCount("*","[UnionQuery]","[Serial]='" &
[Serial] & "' AND ModuleName <='" & [ModuleName] & "'")
GROUP BY Serial, Qty, Make, BuildDate, ModuleName
PIVOT [ModuleName];

--
Always Learning


Duane Hookom said:
I don't see the SQL of the crosstab that I suggested with DCount(). You also
didn't state what results you got if you changed the crosstab to a select
query.

--
Duane Hookom
Microsoft Access MVP


Lucson said:
Here is my new SQL crosstab, When I run it, it tells me that I have 1300 row
When I use your suggestion, it won't even execute...

TRANSFORM Count(Qty)
SELECT Serial,Qty,Make, BuildDate, ModuleName
FROM [UnionQuery]
GROUP BY Serial,Qty,Make, BuildDate, ModuleName
PIVOT [ModuleName];
--
Always Learning


:

Can you provide your actual SQL view? What do you see if you change this from
a crosstab to a select query? What do you actually see? I don't understand
"won't even read".

--
Duane Hookom
Microsoft Access MVP


:

Duane,

I am doing something wrong with your syntax...
I set this inside my Transform but it won't even read..

More guidance please

ColHead: "ModuleName" & DCount("*","[UnionQuery]","[Serial]='" &
[Serial] & "' AND ModuleName <='" & [ModuleName] & "'")

--
Always Learning


:

I think you need to create a sequence/rank column that can be used as the
Column Heading for your crosstab. The expression for this column would be
something like:
ColHead: "PartNum" & DCount("*","[Original Union Query]","[Serial#]='" &
[Serial#] & "' AND PartNumber <='" & [PartNumber] & "'")

Your resulting ColHead could then be used as the Column Heading in the
crosstab query.
--
Duane Hookom
Microsoft Access MVP


:

Hello,

I have a query with 5 to 6 columns and 2000+ rows. The main column is a
serial number column, one serial number has no more than 95 part numbers.
Each serial number is unique with set of part numbers. I like to pivot the
part numbers column to horizontally display part numbers, per serial number.
I created a UnionQuery, then I created a CrossTab query, When I run my
crosstab query, I get a message that have too many row. I thought my crosstab
will give me a new table with each serial number, with its other elements in
one row.
Example:
Original Union Query:
Serial# Description BuildDate PartNumber Comments
A2 Desc1 11/14/2007 123AAA Special Order
A2 Desc1 11/14/2007 456ABA Special Order
A2 Desc1 11/14/2007 BAA213 Special Order
A1 DescP 04/02/2008 21A25B City of Livermore
municipality
A1 DescP 04/02/2008 215B23 City of Livermore
municipality

Here is what I want:
Serial# Description BuildDate PartNumber PartNumber PartNum Comments
A2 Des1 11/14/07 123AAA 456ABA BAA213
Special Order
A1 DesP 04/02/07 21A25B 215B23
City of Liv...

Could someone tell what I am doing wrong? or the best way to approach this?

-
Always Learning
Click to expand...
Click to expand...
Click to expand...
 
Back
Top