Crosstab Query (1 Viewer)

[Ukraine82]

I'm having trouble making a crosstab query or a query in general. I doing a Grand Theft Auto Database. The database keep track of all stolen year, make, model vehicles. I would like to have a query that will give me total (count) number of stolen vehicles each month.

For example let say I have 64 stolen vehicles in August. 30 Honda Civic/Accord, 20 Acura TL/RSX , and 10 Bmw 3series/5Series. For February we have 55 stolen vehicles. 20 Honda Civic/Accord, 20 Jeep Cherokee/Grand Cherokee, and 15 Nissan Maxima/Altima.

I would to have a query that will break down the make and model for each month. I attached a sample that you can take a look at it. I did it manually cause I'm having hard trouble getting what I want. Also my attachment doesn't have the month heading (I would like to add that too).

Michael

 

[irini]

You don't need a crosstab to do what you're looking for. Just create a query and use the Totals button in the toolbar. Your query should look as follows:

column 1 column2 column3 column4
field: Month Make Model Model
total: Group By Group By Group By Count

 

[Ukraine82]

Trust me I've tried everything. The only way I can make year make model to show up in a monthly count is by making a single field entry. I have year, make, and model as separate fields.

If anyone has any idea how do it please let me know.

Thanks,

Michael

 

[Rich]

Post a 97ver

 

[Ukraine82]

Let me know if it works.

Michael

 

[Rich]

something like this?

 

[Ukraine82]

Rich,

I've tried that way, but I was wondering if it possible to have months instead of actual dates.

Let say I'm going to make a report from the query you made. I would like to have a monthly heading along with total number of make and model.

Ex:

Attached bitmap file... I would like to have a model next to the make field.


The date I'm running from is the field called DATE REF'D CLM FPD



Michael

 

[Rich]

I assume this is what you're looking for

 

[Ukraine82]

Nope , but close I mean everything what you did it pretty much what I had. If you look at the report you will notice that the model is missing. I would like my report to look exactly the way I have it, but add a car model next to the car make.


Report Sample that I would like to have

Months . . . . . . . Jan . . . . . . Feb . . . . . . March . . . . . . April

Audi A4 . . . . . . . . 2 . . . . . . . . 6 . . . . . . . . 1 . . . . . . . . . 7 . .
Audi A6 . . . . . . . . 3 . . . . . . . . 5 . . . . . . . . 4 . . . . . . . . . 2 . .
_____________________________________________________________

Acura RSX . . . . . . 2. . . . . . . . . 3 . . . . . . . . 8 . . . . . . . . . 6 . .
Acura TL . . . . . . .4. . . . . . . . . 6 . . . . . . . . 5 . . . . . . . . . 2 . .

Michael

 

[Rich]

just altered the data source for the report and one or two fields, course your query doesn't account for years

 

[Ukraine82]

Rich,

If I knew you any better I'll do anything for you, since I don't than you get nothing. LOL

Thank you so much. Want to do my assignment for me while I sit back and relax? LOL

Thanks again,

Michael

 

[Ukraine82]

Rich,

I'm having trouble getting rid of duplicate name in a crosstab query.

Take a look at the bitmap image you will see duplicate make and model names.

(Ex: Acura Integra)

The total is giving me the correct amount, but how do i prevent duplicate make and model.

Thanks,