Trying to count multiple in one

[Windsor302]

Hey there, I need to count 4 different totals. Using 4 categories and only unique values. I was wondering if I could do all 4 in one query. Thanks

 

[Guus2005]

Yes, i believe it can.
First you have to eliminate doubles.
qryUnique:

select distinct field1, field2, field3, Category from table1

then use a group by query to count the fields

select count(field1) as countoffield1, count(field2) as countoffield2, count(field3) as countoffield3, Category 
from qryUnique
group by category

HTH

 

[Windsor302]

Thanks, the second (group query) is a seperate query right?

 

[Windsor302]

I got it to work, thanks again, I have another question about a different query if you dont mind?

 

[Guus2005]

How did you get it to work? What did you go wrong?

Ask any question you like. If it is a complete different question, please post it in a new thread.

 

[Windsor302]

it worked out great, i was trying to do it all in one query, there was a third step to get it to percentages which all worked out great, thanks again. I posted a new thread with my other issue in the query forum if you wouldnt mind taking a look, thanks

 

[Windsor302]

I actually have a question about this query, is there a way to set it between specific dates, for example i need to print out a report every 10 days of the previous 10 days only

 

[Guus2005]

select count(field1) as countoffield1, count(field2) as countoffield2, count(field3) as countoffield3, Category 
from qryUnique
where somedate is between date and date-10
group by category

Enjoy!

 

[Windsor302]

would i make this query from the percentages query or the step before? thanks

 

[Windsor302]

this code gives me a missing operator for the dates? what is missing, thanks.


SELECT Count([SCOPE 3-COUNT].Weld_No) AS WeldCompleted, Count([SCOPE 3-COUNT].VT_No) AS VTCompleted, Count([SCOPE 3-COUNT].RT_No) AS RTCompleted, Count([SCOPE 3-COUNT].MT_No) AS MTCompleted
FROM [SCOPE 3-COUNT]
Where [weld_date] is between [24/07/2011] and [26/07/2011]
From [TRO SCOPE 3]

 

[Guus2005]

Where [weld_date] is between #24/07/2011# and #26/07/2011#

Use [ and ] when you have a field with a special character in them like a space or a slash...

# is typically used for dates.

Using dates is tricky when you use dd-mm-yyyy. Access expects mm-dd-yyyy. setting the regional setting doesn't help. If you are getting strange results in your query remind that 2-6-2011 is different from 6-2-2011!

Enjoy!

 

[Windsor302]

thanks, I have three queries built from each other to get the one that i use, could i apply this to the last query? would i put:

where [weld_date] = #date# and #date#
from table [scope 3]

thanks again

 

[Guus2005]

Not sure what you are asking. What do you want with "From [TRO SCOPE 3]"

The sql syntax is wrong, the where clause should be last and there should only be one FROM keyword in one query.

"date" is a function. the where clause is wrong.

SELECT Count([SCOPE 3-COUNT].Weld_No) AS WeldCompleted, Count([SCOPE 3-COUNT].VT_No) AS VTCompleted, Count([SCOPE 3-COUNT].RT_No) AS RTCompleted, Count([SCOPE 3-COUNT].MT_No) AS MTCompleted
FROM [SCOPE 3-COUNT]
Where [SCOPE 3-COUNT].[weld_date] is between #24/07/2011# and #26/07/2011#

The table "SCOPE 3-COUNT" should exist in your database.

HTH

 

[Windsor302]

This query is derived from and existing query counting the weldscompleted etc, from Table [TRO SCOPE 3], in this query i am trying to get the weldscompleted between specific dates. Maybe I need a seperate query for this? Thanks.

 

[Guus2005]

I don't know what you mean.
A select query works on a table or a set of joined tables.

"From [TRO SCOPE 3]" in your query doesn't belong to any select statement.

Perhaps you want:

SELECT Count([TRO SCOPE 3].Weld_No) AS WeldCompleted, Count([TRO SCOPE 3].VT_No) AS VTCompleted, Count([TRO SCOPE 3].RT_No) AS RTCompleted, Count([TRO SCOPE 3].MT_No) AS MTCompleted
FROM [TRO SCOPE 3]
Where [TRO SCOPE 3].[weld_date] is between #24/07/2011# and #26/07/2011#

or

SELECT Count([SCOPE 3-COUNT].Weld_No) AS WeldCompleted, Count([SCOPE 3-COUNT].VT_No) AS VTCompleted, Count([SCOPE 3-COUNT].RT_No) AS RTCompleted, Count([SCOPE 3-COUNT].MT_No) AS MTCompleted
FROM [SCOPE 3-COUNT] inner join [TRO SCOPE 3] on [SCOPE 3-COUNT].ID = [TRO SCOPE 3].ID
Where [TRO SCOPE 3].[weld_date] is between #24/07/2011# and #26/07/2011#

Ofcourse i don't know which fields are in your database.

HTH

 

[Windsor302]

Sorry, Weld_No, VT_No and Weld_Date are all columns in TRO SCOPE 3, i need to cound the "Distinct" welds and VT's completed between certain dates. Does that help? Thanks again.

 

[Guus2005]

If you need to count each distinct field then you need three select statements to count each distinct field.