Count Distinct in Query

[LadyDi]

I have a table that can have multiple records for one piece of equipment on the same date (depending on the number of transactions involving that product). The data in this database is pulled from reports sent in directly from the equipment. If the equipment has had many transactions on one day, I could get two reports splitting the transactions in half. As a result, I have two records for the same piece of equipment on the same day. I need to write a query that will sum up the transactions performed on that piece of equipment during a given timeframe and then divide the number of transactions by the number of days that equipment was active. In other words, if I ran my query for 90 days, and that equipment was only active for 80 then I would want to divide the number of transactions by 80.

I got my query to add up the number of transactions without a problem, but I can't get it to count the equipment one time for every day. No matter what I try, if the equipment generated two reports for one day, it is counted twice instead of just once. Is there a way to get the same query to sum all the transactions and count the equipment once for each day? Is there a way to add a "count distinct" column to my query? Any assistance you can provide would be greatly appreciated.

:banghead:

 

[plog]

Most likely you will need a sub-query to determine 'unique' days. Let's assume (and pray) that each piece of equipment has an ID (equipment_ID) and there exist some table (SomeTable) that has a date used to determine if the equipment was active (Trans_Date). You would use this query to first get the 'unique' days:

SELECT equipment_ID, TransDate FROM SomeTable GROUP BY equipment_ID, TransDate;

Then you would use that query in another query to count the number of TransDate values per equipment_ID to get Active days.