Making a query that posts data in a matrix like presentation?

[Steven Seagal]

I have data that is organized by a group and method in which these numbers are processed. It looks like this:

Number	Group	Method
100-A	Alpha	Auto
100-B	Alpha	Auto
100-C	Alpha	None
100-D	Alpha	Manual
101-DA	Alpha	Manual
100-B	Alpha	None
171 - CD	Omega	Auto
171 -B48	Omega	Auto
551-3	Beta	Manual
4829-1	Beta	Manual
3424	beta	Manual

Now, I want the data to look like this when I run a query in Access:

Group	Auto	Manual	None
Alpha	2	2	2
Omega	2	0	0
Beta	0	3	0

How can I do that? Thanks

Note the formatting on the second table is a bit weird, but I think you all know where I'm getting at.

[RayMilhon]

Use something like the following query

Select Group, Sum(IIF([method]='Auto',1,0)) As Auto, sum(IIF([method]='Manual',1,0)) As Manual, sum(iif([method]='None',1,0)) as None

You could also try a crosstab where the Row header is group the column header is method and the value is Count.

[Steven Seagal]

I did exactly that and only that got an error that says "The Select statement includes a reserved word or an argument name that is misspelled or missing or the punctuation is incorrect"

I then stuck a From [Source]; above the line but still no luck. I only get "invalid SQL statement...."

[pbaldy]

You might also play with the crosstab query wizard.

[RayMilhon]

My post was only showing the Select portion of a standard query I did not include the From Clause, Where clause and Group by Clause. You did not specify any information for use in the from or where clause The group by would have group as the only field. Your best bet at this point is to use the crosstab wizard to create your query.

[Steven Seagal]

Thanks all. the wizard was very helpful

[pbaldy]

Happy to help. Developing databases is definitely a step up from making movies!