Results 1 to 8 of 8
  1. #1
    charly.csh is offline Competent Performer
    Windows 11 Office 365
    Join Date
    Nov 2014
    Posts
    186

    Query with the statement COUNT(*) and WHERE

    Hi everyone,

    I have a question regarding a statement and I am not clear how to define this:

    I have a query named [Task_qry] and into the query the fields [Department_Responsible] and [Status]

    Into the field [Department_Responsible] (droplist field) the data "Quality", "Production", "Maintenance", "Logistic"
    Into the field [Status] (droplist field also) the data "Complete" and "Pending"

    What I want to get after execute is

    Department Complete Pending
    Quality 3 5
    Production 5 10
    Maintenance 1 6


    I was thinking something like make the query with this conditions

    Field: Responsible_Action
    Table: Task_tbl
    Total: Group By




    Field: Complete: Count(*)
    Table:
    Total: Expr
    Criteria: I don't know how to define this---- WHERE [Task_Query]![Status] = "Complete" (I know this is wrong... but how can I state this)


    Field: Pending: Count(*)
    Table:
    Total: Expr
    Criteria: I don't know how to define this---- WHERE [Task_Query]![Status] = "Pending"


    I highly appreciate the help or ideas how to do this

  2. #2
    xps35's Avatar
    xps35 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2022
    Location
    Schiedam, NL
    Posts
    229
    Take a look at crosstab queries.
    https://support.microsoft.com/en-us/...ss%20the%20top.

  3. #3
    CarlettoFed is online now Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2019
    Posts
    255
    You can use the following query:
    Code:
    SELECT Task_tbl.Department_Responsible, Nz(DCount("*","Task_tbl","Department_Responsible = """ & [Department_Responsible] & """ AND Status = 'Complete'"),0) AS Complete, nz(DCount("*","Task_tbl","Department_Responsible = """ & [Department_Responsible] & """ AND Status = 'Pending'"),0) AS Pending
    FROM Task_tbl
    GROUP BY Task_tbl.Department_Responsible;

  4. #4
    charly.csh is offline Competent Performer
    Windows 11 Office 365
    Join Date
    Nov 2014
    Posts
    186
    Good idea to use the crosstab query personallly I had not used that before in the past and I did the trial and it works!!
    Just quick question, how do you set on it the conditions?

    I mean I want to filter between some dates and it shows like an error message

  5. #5
    charly.csh is offline Competent Performer
    Windows 11 Office 365
    Join Date
    Nov 2014
    Posts
    186
    Excellent this worked pretty good too
    I had issues on the statement and works pretty good!!

    Thank you very much!!!

  6. #6
    CarlettoFed is online now Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2019
    Posts
    255
    In order to be able to help you on date filters it is better if you attach a sample file with some data.

  7. #7
    charly.csh is offline Competent Performer
    Windows 11 Office 365
    Join Date
    Nov 2014
    Posts
    186
    Hi CarlettoFed

    You can find attached the example I loaded, basically as you can see into in the queries what basically I want to accomplish is the conditions set to "Qsys_GraficoAccResp2_Qry" make the same for "Qsys_GraficoAccPeriodo2_Qry"

    I have some issues to define the statement for the SQL for dates

    I highly appreciate the help!!!

  8. #8
    charly.csh is offline Competent Performer
    Windows 11 Office 365
    Join Date
    Nov 2014
    Posts
    186
    Example for DCOUNT with dates.zip

    Enclosed you can find my example.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Nested Count Iif Statement
    By Topflite66 in forum Queries
    Replies: 3
    Last Post: 02-27-2018, 08:23 AM
  2. Replies: 5
    Last Post: 02-08-2017, 05:52 PM
  3. Adding a Count function to current select statement
    By johnson8809 in forum Queries
    Replies: 2
    Last Post: 02-21-2015, 07:32 PM
  4. Count IIf statement error
    By Huddle in forum Access
    Replies: 6
    Last Post: 07-08-2014, 02:25 PM
  5. Count Iif statement
    By seth.murphine in forum Queries
    Replies: 3
    Last Post: 04-23-2012, 12:36 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums