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.
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 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;
Any assistance is greatly appreciated.
Code: Select all
SELECT Count(ARDate) as CountDate FROM (SELECT DISTINCT ARDate FROM tblAllRecords)