Transpose - Help with crosstab query

[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

 

[Duane Hookom]

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.

 

[Lucson]

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

 

[Duane Hookom]

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

 

[Lucson]

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

 

[Duane Hookom]

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

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

 

[Lucson]

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

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

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

 

[Duane Hookom]

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

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

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

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