(Access) Multiple Counts from the Same Table

Gator

I am looking to produce multiple counts for a table in one query.

Currently I can get the count to work for one condition

SELECT Count(Risks.[Appeal Status]) AS [0 - 5 Days]

FROM Risks INNER JOIN Customers ON Risks.cust_id = Customers.Cust_id

WHERE (((Risks.[Opened Date]) Between Date() And Date()-5) AND ((Risks.[5 Day])=No

Or (Risks.[5 Day])=Yes) AND ((Risks.[Appeal Status])="Open"));

What I would like to do is add another count for

WHERE (((Risks.[Opened Date]) Between Date()-6 And Date()-10) AND ((Risks.[5 Day])=No Or (Risks.[5 Day])=Yes) AND ((Risks.[Appeal Status])="Open"));

and return that into a column AS [5 - 10 Days] ( I have about 6 to run in total but understanding how to do two should point me in the right direction)

When I try to add this it assumes it as an AND operator and returns 0 for the two columns so obviously my second query is upsetting it.

I take it I need to nest the second query but I cant get the correct syntax...

again, any help would be greatly appreciated

Seth Brundle

DCount? Much slower though.

Gator

kbannon wrote: »

DCount? Much slower though.

I had looked at that and yes...thats all it kept popping up..really slow etc...

even if I could get the correct syntax to work I could maybe work it out, im sure its one of those easy queries when you see it but at the moment I feel like a twat as I had left it to the end as the easy part and now I cant do it!!!

Seth Brundle

You would then new to break down your query into a few queries and use IIF to create your columns it might be faster

Gator

Got it this morning

SELECT Count(Risks.[Appeal Status]) AS [0 - 5 Days],

(Select Count(Risks.[Appeal Status]) AS [5 - 10 Days] FROM Risks INNER JOIN

Customers ON Risks.cust_id = Customers.Cust_id

WHERE (((Risks.[Opened Date]) Between Date()-5 And Date()-10) AND ((Risks.[5 Day])

=No Or (Risks.[5 Day])=Yes) AND ((Risks.[Appeal Status])="Open"))) AS [5 - 10 Days]

FROM Risks INNER JOIN Customers ON Risks.cust_id=Customers.Cust_id

WHERE (((Risks.[Opened Date]) Between Date() And Date()-5) AND ((Risks.[5 Day])=No

Or (Risks.[5 Day])=Yes) AND ((Risks.[Appeal Status])="Open"));