Access 2002 query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 3 tables linked together. Cutomer, fillers and service

I have the date of each time we provide service for a customer. I need to
extract the number of times we were there in 05 and 06 in two seperate
columns.

This works fine for one year and one column.
Year([service]) service is the field with the date we were there
and I add 2005 to the criteria field.

Thanks for your help
 
1. Create a query using the table that has the service field and the
customerid.

2. Change it to a Crosstab query (Crosstab on Query menu.)
Access adds rows labelled Total and Crosstab to the grid.

3. Drag the Customer field into the grid.
Accept Group By in the Total row under this field.
Choose Row Heading in the Crosstab row.

4. Type your calculated field into the grid: Year([service])
Accept Group By in the Total row.
Choose Column Heading in the Crosstab row.

5. Drag the primary key field into the grid.
Choose Count in the Total row.
Choose Value in the Crosstab row.

The query will now show:
- a row for each customer (from step 3),
- a column for each year (from step 4),
- the count of records at the intersection point (from step 5.)

If you wish to limit the query to only dates in 2005/06, drag the service
field into the grid again, choose Where in the Total row, and enter the
Criteria you wish. If you wish to use parameters, you need to declare them
(Parameters on Query menu), and/or specify the Column Headings in the
Properties of the query.
 
Back
Top