simple or not

F

federico

The simple(?) problem is:

We have a Table 'Batch', with 2 columns: Batch and State, with this data:

Batch State
A 3
A 1
A 1
B 2
B 2
B 3
C 1
C 1
C 2


and we want this return

Batch State 1 State 2 State 3
A 2 0 1
B 0 2 1
C 2 1 0

counting the number of lines in each state grouped by Batch

Which is the SQL command to do that?, simple or not?
 
G

George Nicholson

Very simple.

You want a crosstab query, but you don't want to use the crosstab query
wizard (the wizard requires at least 3 fields in the table and you say you
only have 2).

-Create a new query in Design view (no wizards).
-View>SQL
-Paste the following, changing Field & Table Names as appropriate.
TRANSFORM Count(State) AS CountOfState
SELECT Batch
FROM [MyTable]
GROUP BY Batch
PIVOT "State " & [State];

That should do it.

Alternative method:
(In Query Design view):
-Select the table
-QueryType s/b CrossTab
-Batch
s/b RowHeading (Total = GroupBy)
-Exp1: "State " & [State]
s/b ColumnHeading (Total = GroupBy)
-State
s/b Value (Total = Count)

The one problem with this is that blanks will appear in the results rather
than zeros. AFAIK, nothing you can do about that here (unless you want to
change your table stucture & data). However, that cosmetic problem can be
easily dealt with by using NZ() or formatting in whatever is going to be
using this query (a form, a report or other query).

HTH
 
J

John Spencer (MVP)

George,

Try something like the following to get a zero value in the query.

TRANSFORM CLng(NZ(Count(State),0)) AS CountOfState
SELECT Batch
FROM [MyTable]
GROUP BY Batch
PIVOT "State " & [State];

George said:
Very simple.

You want a crosstab query, but you don't want to use the crosstab query
wizard (the wizard requires at least 3 fields in the table and you say you
only have 2).

-Create a new query in Design view (no wizards).
-View>SQL
-Paste the following, changing Field & Table Names as appropriate.
TRANSFORM Count(State) AS CountOfState
SELECT Batch
FROM [MyTable]
GROUP BY Batch
PIVOT "State " & [State];

That should do it.

Alternative method:
(In Query Design view):
-Select the table
-QueryType s/b CrossTab
-Batch
s/b RowHeading (Total = GroupBy)
-Exp1: "State " & [State]
s/b ColumnHeading (Total = GroupBy)
-State
s/b Value (Total = Count)

The one problem with this is that blanks will appear in the results rather
than zeros. AFAIK, nothing you can do about that here (unless you want to
change your table stucture & data). However, that cosmetic problem can be
easily dealt with by using NZ() or formatting in whatever is going to be
using this query (a form, a report or other query).

HTH
--
George Nicholson

Remove 'Junk' from return address.

federico said:
The simple(?) problem is:

We have a Table 'Batch', with 2 columns: Batch and State, with this data:

Batch State
A 3
A 1
A 1
B 2
B 2
B 3
C 1
C 1
C 2


and we want this return

Batch State 1 State 2 State 3
A 2 0 1
B 0 2 1
C 2 1 0

counting the number of lines in each state grouped by Batch

Which is the SQL command to do that?, simple or not?
Click to expand...
Click to expand...
 
G

George Nicholson

Huh, I swear I tried that before posting but it had no effect. Maybe I used
Count(nz(State,0)) by mistake.

Thanks, John.

--
George Nicholson

Remove 'Junk' from return address.


John Spencer (MVP) said:
George,

Try something like the following to get a zero value in the query.

TRANSFORM CLng(NZ(Count(State),0)) AS CountOfState
SELECT Batch
FROM [MyTable]
GROUP BY Batch
PIVOT "State " & [State];

George said:
Very simple.

You want a crosstab query, but you don't want to use the crosstab query
wizard (the wizard requires at least 3 fields in the table and you say
you
only have 2).

-Create a new query in Design view (no wizards).
-View>SQL
-Paste the following, changing Field & Table Names as appropriate.
TRANSFORM Count(State) AS CountOfState
SELECT Batch
FROM [MyTable]
GROUP BY Batch
PIVOT "State " & [State];

That should do it.

Alternative method:
(In Query Design view):
-Select the table
-QueryType s/b CrossTab
-Batch
s/b RowHeading (Total = GroupBy)
-Exp1: "State " & [State]
s/b ColumnHeading (Total = GroupBy)
-State
s/b Value (Total = Count)

The one problem with this is that blanks will appear in the results
rather
than zeros. AFAIK, nothing you can do about that here (unless you want to
change your table stucture & data). However, that cosmetic problem can be
easily dealt with by using NZ() or formatting in whatever is going to be
using this query (a form, a report or other query).

HTH
--
George Nicholson

Remove 'Junk' from return address.

federico said:
The simple(?) problem is:

We have a Table 'Batch', with 2 columns: Batch and State, with this
data:

Batch State
A 3
A 1
A 1
B 2
B 2
B 3
C 1
C 1
C 2


and we want this return

Batch State 1 State 2 State 3
A 2 0 1
B 0 2 1
C 2 1 0

counting the number of lines in each state grouped by Batch

Which is the SQL command to do that?, simple or not?
Click to expand...
Click to expand...
Click to expand...
 
Top