Crosstab query - not getting results expected

[PhoneTech2]

I have a table called work orders. It goes back to 1999.
I build (using the Access query wizard) a cross tab query.

I have one field that defines type of work, A=amc S=service.

I want to count the number of each per month.

I first created a query based on the table so that minium information is in the query - Type, Work order number and completion date. The cross tab query is only using two fields type and date. The wizard assisted me in settin up the date in a monthly baisis.

When I run the cross tab query with the entire database I get totals for all years. When I attempt to filter the data so it only reports for this year, I am missing work completed in this year. (When I run without filter, I have a total of 75 work orders completed for June 2002, but when I filter for 2002 it only shows 6 work orders being completed.

The dates are all entered the same way in the original form.

Any ideas what I am doing wrong?

Thank you.

 

[raymondo]

I have a similar query and have no problems. It might help if you post the SQL for the query so that people can see exactly what's going on.
Probably a silly question but do you run the query + filter through code or do you set up the where condition in the query itself?
If it's run through code then you might want to post the appropriate code fragment as well.

 

[PhoneTech2]

ramondo,

Not to get too technical but, huh!
I am sorry but I am a very basic user of Access. I use Windows Access 97 and relie almost 100% on what Microsoft built into the program to do what I need to do. I don't think I would recognize SQL if I saw it, so I can not post it.

What I did was first set up a basic query with only a few fields.

I then used Access Wizard to create a cross tab query based on the query I just mentioned.

This works, I get all work orders listed.

However, when I go to the basic query, and set a critera *2002 meaning only work orders completed in the year 2002 I seem to get all of Jan, Feb, March, April, but only a small number of them from June and July.

Is there something I am doing when setting the criteria (the field is a date field).

Thank you