Use A Query to Count Unique Dates Given Other Criteria

[EnginerdUNH]

Hi,

I am working on database that has a main table, tblAllRecords, where all of the data that is of interest to me is stored. The table is related to the other tables in the database via ID fields. Right now, I have the following query which joins tblEquipment and tblAllRecords and counts the total number instances where the value of AREquipment is equal to tblEquipment.EquipmentID.

Code: Select all

SELECT tblEquipment.EquipmentID, tblEquipment.Equipment, Count(tblAllRecords.AREquipment) AS CountOfAREquipment, (select Count(ARID) From tblAllRecords) AS Total, [CountOfAREquipment]/[Total] AS PercentOfAREquipment
FROM tblEquipment LEFT JOIN tblAllRecords ON tblEquipment.EquipmentID = tblAllRecords.AREquipment
GROUP BY tblEquipment.EquipmentID, tblEquipment.Equipment;

what this means is that if I have 2 or more instances of EquipmentID = 1 on a particular day, each instance is included in this total. Rather, what I would like to do is count the number of days which each EquipmentID comes up in tblAllRecords. So, as an example, if I have 3 days where the number of instances of EquipmentID = 1 equals 2, 4, and 7, instead of the total count being 13 as that is the number of times that EquipmentID = 1 comes up, the total count that the query would give me is 3. I have successfully written the following query which counts the total number of unique days in tblAllRecords but I cannot figure out how to re-write the query above or combine it with the query below to do what I want.

Code: Select all

SELECT Count(ARDate) as CountDate
FROM (SELECT DISTINCT ARDate FROM tblAllRecords)

Any assistance is greatly appreciated.

[HansV]

Could you create a stripped-down copy of the database with only those two tables and some records without sensitive data, then zip it and attach the zip file to a reply?

[EnginerdUNH]

Hans, no problem, please see the attached. I left the two queries that I've created which I referenced in my original post as well as the tables that the main table feeds off of just in case. There's no sensitive data in the database so none of the records had to be removed.

[HansV]

I'd do this in two steps - see the attached version.

PR Tracker_DateTotals.zip

[EnginerdUNH]

Cross checking the side math that I did when I exported the data to excel, the two queries that you did give me the numbers I was expecting. Thank you!!