Count with multiple criteria (1 Viewer)

kobiashi

Registered User.
Local time
Today, 11:27
Joined
May 11, 2018
Messages
258
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
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.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:27
Joined
May 7, 2009
Messages
19,169
use Total Query instead.
 

Ranman256

Well-known member
Local time
Today, 07:27
Joined
Apr 9, 2015
Messages
4,337
where [mcudate] between DateAdd("d",-14, Date()) and Date()

but if you want the count, turn on Summation symbol in the query.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 11:27
Joined
Feb 19, 2013
Messages
16,553
you have two problems and question:

1. the subquery is set to return counts of all vehicles/faults because you have not set a criteria - hence the error message, it is returning multiple values, not 1
2. the sub query uses the same table as the main query so you need to alias it so the access engine can differentiate it
3. why are you doing it this way anyway - you don't need a subquery

try

Code:
SELECT VehicleAtFault_IDFK, SubSystem_IDFK, Sum(1)  AS Failurelevel
FROM Table_ENG_MCU_Tracker
WHERE MCUDate Between Date()-14 And Date()
GROUP BY VehicleAtFault_IDFK, SubSystem_IDFK
 

kobiashi

Registered User.
Local time
Today, 11:27
Joined
May 11, 2018
Messages
258
you have two problems and question:

1. the subquery is set to return counts of all vehicles/faults because you have not set a criteria - hence the error message, it is returning multiple values, not 1
2. the sub query uses the same table as the main query so you need to alias it so the access engine can differentiate it
3. why are you doing it this way anyway - you don't need a subquery

try

Code:
SELECT VehicleAtFault_IDFK, SubSystem_IDFK, Sum(1)  AS Failurelevel
FROM Table_ENG_MCU_Tracker
WHERE MCUDate Between Date()-14 And Date()
GROUP BY VehicleAtFault_IDFK, SubSystem_IDFK

thanks for the reply

im doing this so i have quick visibility in terms of a repeat failure, so if the vehicle has the same failure type in 14 days, it will highlight it in levels, ie level 1, level 2 etc.

so i have a continuous form, with all the entries on there, then the failure level will display in a field, so what im trying to do is each line there is similar failure type the number will increase in the failure level field
 

CJ_London

Super Moderator
Staff member
Local time
Today, 11:27
Joined
Feb 19, 2013
Messages
16,553
not sure if the solution provided is what you want. If not, provide some example data and what that data should show as the result you want
 

kobiashi

Registered User.
Local time
Today, 11:27
Joined
May 11, 2018
Messages
258
yeh it counted but not per line

ive added a screen shot a an excel sheet, but the output is the same as access
 

Attachments

  • ss.jpg
    ss.jpg
    102.5 KB · Views: 89

CJ_London

Super Moderator
Staff member
Local time
Today, 11:27
Joined
Feb 19, 2013
Messages
16,553
I repeat

provide some example data and what that data should show as the result you want

your query inpost #1 looks nothing like the output required in your last post
 

kobiashi

Registered User.
Local time
Today, 11:27
Joined
May 11, 2018
Messages
258
ok

do you want me to upload a database with the data or screenshots?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 11:27
Joined
Feb 19, 2013
Messages
16,553
a database with data plus the query you are trying to get to work then an excel file showing what that query should return - based on the data in the database
 

kobiashi

Registered User.
Local time
Today, 11:27
Joined
May 11, 2018
Messages
258
hi cj_London

so i have created a test database with some date in there, i have place the query i was trying to get to work (subquery1)

and i have added an example excel sheet with what i wanted it to look like.
 

Attachments

  • example.accdb
    1.3 MB · Views: 91
  • exampleform.xlsx
    8.7 KB · Views: 72

kobiashi

Registered User.
Local time
Today, 11:27
Joined
May 11, 2018
Messages
258
i hate to bump posts, but does anyone how i can achieve this please.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 11:27
Joined
Feb 19, 2013
Messages
16,553
if I'm understanding you correctly



you want to increment the failure level count by 1 for each repeat of vehicle and sub system. Is this correct? i.e. sub system is the same as failure type?

Assuming the above is correct then this query produces the required result

Code:
SELECT Table_ENG_MCU_Tracker.MCUTracker_ID, Table_ENG_MCU_Tracker.VehicleAtFault_IDFK, Table_ENG_MCU_Tracker.SubSystem_IDFK, Count(Table_ENG_MCU_Tracker_1.MCUTracker_ID) AS FailureLevel
FROM Table_ENG_MCU_Tracker INNER JOIN Table_ENG_MCU_Tracker AS Table_ENG_MCU_Tracker_1 ON (Table_ENG_MCU_Tracker.MCUTracker_ID [COLOR=red]>= [/COLOR]Table_ENG_MCU_Tracker_1.MCUTracker_ID) AND (Table_ENG_MCU_Tracker.VehicleAtFault_IDFK = Table_ENG_MCU_Tracker_1.VehicleAtFault_IDFK) AND (Table_ENG_MCU_Tracker.SubSystem_IDFK = Table_ENG_MCU_Tracker_1.SubSystem_IDFK)
GROUP BY Table_ENG_MCU_Tracker.MCUTracker_ID, Table_ENG_MCU_Tracker.VehicleAtFault_IDFK, Table_ENG_MCU_Tracker.SubSystem_IDFK;
Note this query cannot be viewed in the query builder because of the modified join (shown in red). If you do go into the query builder you will be warned, but easy to recreate. Just remake the join between the two tables then go into sql view and modify the = to >=
 

Attachments

  • Capture.JPG
    Capture.JPG
    24.1 KB · Views: 910
Last edited:

kobiashi

Registered User.
Local time
Today, 11:27
Joined
May 11, 2018
Messages
258
WOW


Thank you so much, i have been trying to solve this for weeks
 

kobiashi

Registered User.
Local time
Today, 11:27
Joined
May 11, 2018
Messages
258
apologies on re-opening this thread again, but how would i be able to filter the data based on data, so i want the lookup to be 14 days, so the failure level will only trigger if its within 14 days if the first event, i thought it would be this
Code:
SELECT 
Table_ENG_MCU_Tracker.MCUTracker_ID, 
Table_ENG_MCU_Tracker.VehicleAtFault_IDFK, 
Table_ENG_MCU_Tracker.SubSystem_IDFK, 
Table_ENG_MCU_Tracker.MCUDate,
Count(Table_ENG_MCU_Tracker_1.MCUTracker_ID) AS FailureLevel
FROM 
Table_ENG_MCU_Tracker INNER JOIN Table_ENG_MCU_Tracker AS Table_ENG_MCU_Tracker_1 ON
(Table_ENG_MCU_Tracker.MCUTracker_ID >= Table_ENG_MCU_Tracker_1.MCUTracker_ID)
AND
(Table_ENG_MCU_Tracker.MCUDate  between Date() and Date()-14 <= Table_ENG_MCU_Tracker_1.MCUDate) 
AND 
(Table_ENG_MCU_Tracker.SubSystem_IDFK = Table_ENG_MCU_Tracker_1.SubSystem_IDFK) 
AND 
(Table_ENG_MCU_Tracker.VehicleAtFault_IDFK = Table_ENG_MCU_Tracker_1.VehicleAtFault_IDFK)

GROUP BY 
Table_ENG_MCU_Tracker.MCUTracker_ID, 
Table_ENG_MCU_Tracker.VehicleAtFault_IDFK, 
Table_ENG_MCU_Tracker.SubSystem_IDFK,
Table_ENG_MCU_Tracker.MCUDate;

but it still returned the same data
 

CJ_London

Super Moderator
Staff member
Local time
Today, 11:27
Joined
Feb 19, 2013
Messages
16,553
not clear what you want. Is count of failure level to be where Table_ENG_MCU_Tracker_1 is within 14 days of Table_ENG_MCU_Tracker?

or something else.

Either way you are trying to apply a criteria to a join - it needs to be in the where part. Assuming the count required is as above then try

Code:
SELECT 
Table_ENG_MCU_Tracker.MCUTracker_ID, 
Table_ENG_MCU_Tracker.VehicleAtFault_IDFK, 
Table_ENG_MCU_Tracker.SubSystem_IDFK, 
Table_ENG_MCU_Tracker.MCUDate,
Count(Table_ENG_MCU_Tracker_1.MCUTracker_ID) AS FailureLevel
FROM 
Table_ENG_MCU_Tracker INNER JOIN Table_ENG_MCU_Tracker AS Table_ENG_MCU_Tracker_1 ON
(Table_ENG_MCU_Tracker.MCUTracker_ID >= Table_ENG_MCU_Tracker_1.MCUTracker_ID)
AND 
(Table_ENG_MCU_Tracker.SubSystem_IDFK = Table_ENG_MCU_Tracker_1.SubSystem_IDFK) 
AND 
(Table_ENG_MCU_Tracker.VehicleAtFault_IDFK = Table_ENG_MCU_Tracker_1.VehicleAtFault_IDFK)

[COLOR=red]WHERE Table_ENG_MCU_Tracker_1.MCUDate  between Table_ENG_MCU_Tracker.MCUDate-14 and Table_ENG_MCU_Tracker.MCUDate[/COLOR]

GROUP BY 
Table_ENG_MCU_Tracker.MCUTracker_ID, 
Table_ENG_MCU_Tracker.VehicleAtFault_IDFK, 
Table_ENG_MCU_Tracker.SubSystem_IDFK,
Table_ENG_MCU_Tracker.MCUDate;
 

Users who are viewing this thread

Top Bottom