Crosstab query with unique count

  • Thread starter Thread starter hoachen
  • Start date Start date
H

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;
 
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 ;-)
 
I also wondered why pull [table1].Status when the only status will be "opn"
due to criteria?

--
Build a little, test a little.


Duane Hookom said:
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


hoachen said:
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;
Click to expand...
Click to expand...
 
no, the status have open and close. I only want the open show up

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

--
Build a little, test a little.


Duane Hookom said:
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


hoachen said:
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;
Click to expand...
Click to expand...
Click to expand...
 
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.


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

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

--
Build a little, test a little.


Duane Hookom said:
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;
Click to expand...
Click to expand...
Click to expand...
 
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

KARL DEWEY said:
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.


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

KARL DEWEY said:
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;
Click to expand...
Click to expand...
Click to expand...
 
So, did you get this to work or not?

--
Duane Hookom
Microsoft Access MVP


hoachen said:
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

KARL DEWEY said:
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.


hoachen said:
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;
Click to expand...
Click to expand...
Click to expand...
 
Back
Top