Crosstab query with unique count

[hoachen]

I am trying to do crosstab query that with a unique PO# count, but
unsuccessulf. How do i run this query to count the PO# and group by customer?
Right now this query pull everything and count on it.

Here is the quote
TRANSFORM Count([table1].[PO#]) AS [CountOfPO#]
SELECT [table1].[Customer#], [table1].Status, Count([table1].[PO#]) AS
[Total Of PO#]
FROM [table1]
WHERE ((([table1].Status)="opn"))
GROUP BY [table1].[Customer#], [table1].Status
PIVOT [table1].Qty;

 

[Duane Hookom]

Typically you would create a "pre" query that groups by some fields to get
you to unique values. Then create a crosstab based on the group by query

I'm having trouble understanding why you would pivot on a Qty field but
that's my issue ;-)

 

[KARL DEWEY]

I also wondered why pull [table1].Status when the only status will be "opn"
due to criteria?

--
Build a little, test a little.

Typically you would create a "pre" query that groups by some fields to get
you to unique values. Then create a crosstab based on the group by query

I'm having trouble understanding why you would pivot on a Qty field but
that's my issue ;-)

--
Duane Hookom
Microsoft Access MVP

I am trying to do crosstab query that with a unique PO# count, but
unsuccessulf. How do i run this query to count the PO# and group by customer?
Right now this query pull everything and count on it.

Here is the quote
TRANSFORM Count([table1].[PO#]) AS [CountOfPO#]
SELECT [table1].[Customer#], [table1].Status, Count([table1].[PO#]) AS
[Total Of PO#]
FROM [table1]
WHERE ((([table1].Status)="opn"))
GROUP BY [table1].[Customer#], [table1].Status
PIVOT [table1].Qty;

 

[hoachen]

no, the status have open and close. I only want the open show up

I also wondered why pull [table1].Status when the only status will be "opn"
due to criteria?

--
Build a little, test a little.

Typically you would create a "pre" query that groups by some fields to get
you to unique values. Then create a crosstab based on the group by query

I'm having trouble understanding why you would pivot on a Qty field but
that's my issue ;-)

--
Duane Hookom
Microsoft Access MVP

I am trying to do crosstab query that with a unique PO# count, but
unsuccessulf. How do i run this query to count the PO# and group by customer?
Right now this query pull everything and count on it.

Here is the quote
TRANSFORM Count([table1].[PO#]) AS [CountOfPO#]
SELECT [table1].[Customer#], [table1].Status, Count([table1].[PO#]) AS
[Total Of PO#]
FROM [table1]
WHERE ((([table1].Status)="opn"))
GROUP BY [table1].[Customer#], [table1].Status
PIVOT [table1].Qty;

 

[KARL DEWEY]

Ok, but why put status in output?

Your query as posted would give results like this --
Customer# Status Total 1 2 3 4 5
Bill opn 36 5 1
Sam opn 14 3 1
Joe opn 75 3 2

Do you not want it like this? Post how you want the results to look.


--
Build a little, test a little.

no, the status have open and close. I only want the open show up

I also wondered why pull [table1].Status when the only status will be "opn"
due to criteria?

--
Build a little, test a little.

Typically you would create a "pre" query that groups by some fields to get
you to unique values. Then create a crosstab based on the group by query

I'm having trouble understanding why you would pivot on a Qty field but
that's my issue ;-)

--
Duane Hookom
Microsoft Access MVP


:

I am trying to do crosstab query that with a unique PO# count, but
unsuccessulf. How do i run this query to count the PO# and group by customer?
Right now this query pull everything and count on it.

Here is the quote
TRANSFORM Count([table1].[PO#]) AS [CountOfPO#]
SELECT [table1].[Customer#], [table1].Status, Count([table1].[PO#]) AS
[Total Of PO#]
FROM [table1]
WHERE ((([table1].Status)="opn"))
GROUP BY [table1].[Customer#], [table1].Status
PIVOT [table1].Qty;

 

[hoachen]

I know is weired, it just a testing i will remove it eventually because all
those data is important somewhere and i want to make sure it works correctly.
as you say Build a little, test a little

Ok, but why put status in output?

Your query as posted would give results like this --
Customer# Status Total 1 2 3 4 5
Bill opn 36 5 1
Sam opn 14 3 1
Joe opn 75 3 2

Do you not want it like this? Post how you want the results to look.


--
Build a little, test a little.

no, the status have open and close. I only want the open show up

I also wondered why pull [table1].Status when the only status will be "opn"
due to criteria?

--
Build a little, test a little.


:

Typically you would create a "pre" query that groups by some fields to get
you to unique values. Then create a crosstab based on the group by query

I'm having trouble understanding why you would pivot on a Qty field but
that's my issue ;-)

--
Duane Hookom
Microsoft Access MVP


:

I am trying to do crosstab query that with a unique PO# count, but
unsuccessulf. How do i run this query to count the PO# and group by customer?
Right now this query pull everything and count on it.

Here is the quote
TRANSFORM Count([table1].[PO#]) AS [CountOfPO#]
SELECT [table1].[Customer#], [table1].Status, Count([table1].[PO#]) AS
[Total Of PO#]
FROM [table1]
WHERE ((([table1].Status)="opn"))
GROUP BY [table1].[Customer#], [table1].Status
PIVOT [table1].Qty;

 

[Duane Hookom]

So, did you get this to work or not?

--
Duane Hookom
Microsoft Access MVP

I know is weired, it just a testing i will remove it eventually because all
those data is important somewhere and i want to make sure it works correctly.
as you say Build a little, test a little

Ok, but why put status in output?

Your query as posted would give results like this --
Customer# Status Total 1 2 3 4 5
Bill opn 36 5 1
Sam opn 14 3 1
Joe opn 75 3 2

Do you not want it like this? Post how you want the results to look.


--
Build a little, test a little.

no, the status have open and close. I only want the open show up

:

I also wondered why pull [table1].Status when the only status will be "opn"
due to criteria?

--
Build a little, test a little.


:

Typically you would create a "pre" query that groups by some fields to get
you to unique values. Then create a crosstab based on the group by query

I'm having trouble understanding why you would pivot on a Qty field but
that's my issue ;-)

--
Duane Hookom
Microsoft Access MVP


:

I am trying to do crosstab query that with a unique PO# count, but
unsuccessulf. How do i run this query to count the PO# and group by customer?
Right now this query pull everything and count on it.

Here is the quote
TRANSFORM Count([table1].[PO#]) AS [CountOfPO#]
SELECT [table1].[Customer#], [table1].Status, Count([table1].[PO#]) AS
[Total Of PO#]
FROM [table1]
WHERE ((([table1].Status)="opn"))
GROUP BY [table1].[Customer#], [table1].Status
PIVOT [table1].Qty;