Query Help 1

[Jerry2004]

I am trying to retrieve a "sum" of a "count". I guess that can be done. Here is the count query. How do I get a total for the count? Thanks in advance.

SELECT tblBicycles.Make, tblBicycles.Model, tblBicycles.Size, tblBicycles.Color, Count(tblBicycles.Warehouse) AS CountOfWarehouse
FROM tblBicycles
WHERE (((tblBicycles.Status)=2))
GROUP BY tblBicycles.Make, tblBicycles.Model, tblBicycles.Size, tblBicycles.Color;

 

[mordja]

Sum of a count, you just want the total count of bikes with a status = 2 ?

SELECT Count(*) AS CountOfWarehouse
FROM tblBicycles
WHERE (((tblBicycles.Status)=2));

Mordja

 

[Jerry2004]

Mordja,
Yes, a total count of bikes with a status = 2. Also, does this have to be a separage query or can it be contained within the query I posted?
Thanks.

 

[mordja]

Jerry2004,

The query I posted will return a single value. If you want the count of each group and then a total at the bottom you could do

SELECT tblBicycles.Make, tblBicycles.Model, tblBicycles.Size, tblBicycles.Color, Count(tblBicycles.Warehouse) AS CountOfWarehouse
FROM tblBicycles
WHERE (((tblBicycles.Status)=2))
GROUP BY tblBicycles.Make, tblBicycles.Model, tblBicycles.Size, tblBicycles.Color

UNION


SELECT "Total", "Total", "Total", "Total", Count(*) AS CountOfWarehouse
FROM tblBicycles
WHERE (((tblBicycles.Status)=2));

 

[Jerry2004]

Mordja:

Thanks for the help. I know your posting will solve my problem, I'm just not smart enough to follow what you're saying. I am truely a rookie Access user. The original query I posted is run when a report is generated. It returns values for bikes that are in the warehouse (value=2). But if I want to know the total number of bikes in the warehouse and display that on the report, that's where I'm hung. The report does not have any grouping of items, just a simple listing. I tried to use [Count(*)] in a text box, but that only returns the number of records in the report and if one make/model of bike is duplicated, the original query returns one record with an incremented number and [Count(*)] sees that as a single record. For example, if Bike A returns a value of "1" and Bike B returns a value of "2", the total is "3" but [Count(*)] returns a value of "2". Thanks so much for your time. I think I need to put this down for a little while and let my brain relax.

 

[Jerry2004]

I got it! Thanks for the help!!!!!!!!!

 

[mordja]

Jerry2004,

If you just want to count the total number of distinct bike makes in the warehouse then you will to do something like

SELECT COUNT(*)
FROM (SELECT tblBicycles.Make
FROM tblBicycles.Make
WHERE (((tblBicycles.bstatus)="2"))
GROUP BY tblBicycles.Make);


Hope this helps