Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie
Hi there,
There is an issue with role permissions that is being worked on at the moment.
If you are having trouble with access or permissions on regional forums please post here to get access: https://www.boards.ie/discussion/2058365403/you-do-not-have-permission-for-that#latest

(Access) Multiple Counts from the Same Table

  • 07-04-2011 10:23pm
    #1
    Gator
    Registered Users, Registered Users 2 Posts: 602 ✭✭✭


    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


Comments

  • #2
    Seth Brundle
    Moderators, Politics Moderators Posts: 41,246 Mod ✭✭✭✭Seth Brundle


    DCount? Much slower though.


  • #3
    Gator
    Registered Users, Registered Users 2 Posts: 602 ✭✭✭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!!!


  • #4
    Seth Brundle
    Moderators, Politics Moderators Posts: 41,246 Mod ✭✭✭✭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


  • #5
    Gator
    Registered Users, Registered Users 2 Posts: 602 ✭✭✭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"));


Advertisement