Count Values

[Nikki]

Ok, I'm working with a database that I didn't create. I've been
working with Access for years, but I understand only a little VB and
SQL. So if somebody has a solution involving either, please be as
specific as possible.

I've tried to narrow down the explanation to what I think matters to
the issue, but if I left something out let me know. I have two
tables.

Table1
Table1ID
Manager
Director

Table2
Table2ID
Manager
Status

Table1 has a one to many relationship with table2 connected by
Table1ID to Table2.Manager. Status is a drop down field with four
selections, A,B,C,D.

I would like to create a report that shows Manager, Director, and a
count for each of the Status values. At the end, I would like a total
for each Status value.

So it would look like:

Manager Director A B C D
John Sally 1 0 0 2
Jane Bob 0 1 0 1

Totals: 1 1 0 3

I cannot figure out how to do this. Any assistance would be
appreciated.

 

[Guest]

1. Create a query that joins the two tables together along with any criteria
to filter out any records that you don't want to see.

2. At the database window go to Queries on the objects bars. Next go to New,
Crosstab query wizard. It may take a couple of tries to get it to work, but a
crosstab query is what you want.

3. Search these forums for things like crosstab, crosstab headings, crosstab
and reports. You'll probably run into some problems getting the crosstab
query to do exactly what you want.

 

[Nikki]

Ok, I actually got it on the first try, so I feel kinda silly for
having to ask, but I had no idea that was a crosstab query. I guess
I've never needed to use one before. Thanks so much!

 

[Guest]

Great! I hope that you notice that I forgot to mention to build the crosstab
query upon the first query. Also if you use parameters in the first query, be
very sure to to to Query, Parameters in design view and define the parameter.
Otherwise a crosstab can bomb out.