COUNT query question

[PaulT]

Hi,

Please help me as I am trying to create a query which will count up the total of various fields.

For example say I had a database of fruit. Now I wanted to count up how many apples I had and also how many oranges I had and display these in a report. I have managed to to get a query which does this using UNION but it means that the totals appear in the same column (only one column) in the query.

So it would say:
CountofFruitType
11
28

11 might be apples and 28 might be oranges but I want a separate column for each:

CountofApples CountofOranges
11.......................28

I hope this all makes sense. Sorry for using fruit but it seemed the easiest!!

Many thanks in advance,

Paul.

 

[Roo]

This sounds extremely similar to what I'm trying to do, without much success.

I need two count figures from the same table, I tried the UNION and it did the same thing for me.

 

[PaulT]

I might have had a breakthrough so I'll keep you posted Roo.

 

[Mile-O]

Why do you need a union query?

Is this because your table design in poor?

 

[PaulT]

I don't need a UNION query but I was just trying it.

My breakthrough hasn't come too much anyway so I'm still struggling!

 

[PaulT]

OK using the following I am able to get two separate columns but it seems to be ignoring my criteria and is giving me a total number of pieces of fruit in both columns. Any ideas?


SELECT Count([Fruit Type]= "Apples") AS Apples,
Count([Fruit Type]= "Oranges") AS Oranges
FROM tblFruit;

 

[Mile-O]

Use a crosstab query.

 

[PaulT]

OK many thanks.

Now how would I do that then?

 

[Mile-O]

Start by looking at the Crosstab Query Wizard option that appears when you hit 'New Query'.

 

[PaulT]

Ahhhh ok I see - many thanks!

 

[Roo]

You're describing exactly the same processes I've been going through, I also tried the crosstab wizard but to be honest I got in a bit of a mess with it, it didn't give me what I needed, but I probably did it wrong.
If you succeed, I would appreciate any clues!

Thanks

 

[Mile-O]

Roo, you would probably find it more helpful to start your own thread with your own problem instead of hijacking PaulT's thread.

 

[Roo]

I do have a thread, but I guess I was just trying to bounce off ideas with someone who was describing the same problem I was having.
Apologies, and apologies to PaulT.