Pivot table like results from query

[Timothy.Rybak]

I know this should be easy, but I can' t figure this one out to save
me.

I have two tables, linked by unique serial number. I am pulling
date/time from one table and their process codes from the other.
Currently, the results of the query show one line item for every
date/process code, based on a date range given by the user.

Date/Time Processcode
5/30/2006 5:00:00 AM C2
5/30/2006 5:00:01 AM C2
5/30/2006 5:00:02 AM C3
5/30/2006 5:00:03 AM C3
5/30/2006 5:00:04 AM C4

What I would like is something like a pivot table in Excel, where I am
getting count of the process codes for the given date/time range.

Processcode CountOfProcesscode
C2 2
C3 2
C4 1

I know this should be easy, but I cannot figure it out.

Any help would be appreciated, and I prefer non-SQL based help if
possible.

 

[Guest]

SQL is the way to go with this one. You need a Crosstab query. Use your
existing query and build a crosstab upon it.

In the Database Window go to Queries, then New. The Crosstab Query Wizard
should be one of the options. Click on the Both option button so that you
can see both your tables and queries. Find the original query and let the
wizard guide you through the process. Don't be discouraged if you have to try
it 2 or 3 times to get it right.

Also if you are using a parameter in the original query to prompt for
information, make sure that while in design view you go to Query, Parameters
and specifically define the parameter datatype. Crosstabs are picky if you
don't.

 

[Timothy.Rybak]

I still don't get how to get it to summarize, or count, the text based
data (i.e. how many C3 parts in a given time).

I'll continue to play with the crosstab query, but it you could give a
little more specific data, I would appreciate it.

Tim