i have a table called Table_Tracker, in this table i capture events that occur in the day against vehicles, what im trying to do is count the same number of events in a 14 days period, so ive done this in a query, but i cannot get the query to work, a message comes up saying this query will at most only return one value
here is my table and query
table_tracker
MCUDate
VehicleAtFault_IDFK
System_IDFK
SubSystem_IDFK
Here is my query
so it looks at the VehicleAtFault_IDFK and SubSystem_IDFK within a 14 day period, and returns a count value.
here is my table and query
table_tracker
MCUDate
VehicleAtFault_IDFK
System_IDFK
SubSystem_IDFK
Here is my query
Code:
SELECT DISTINCT Table_ENG_MCU_Tracker.VehicleAtFault_IDFK, Table_ENG_MCU_Tracker.SubSystem_IDFK, (Select Sum(1) As Failures From Table_ENG_MCU_Tracker Group by VehicleAtFault_IDFK, SubSystem_IDFK) AS Failurelevel
FROM Table_ENG_MCU_Tracker
WHERE (((Table_ENG_MCU_Tracker.MCUDate) Between Date()-14 And Date()));
so it looks at the VehicleAtFault_IDFK and SubSystem_IDFK within a 14 day period, and returns a count value.