Results 1 to 6 of 6
  1. #1
    Simonhtc4 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    30

    count function for report from query

    Hello Guys and Girls,



    Im trying to run a query that will run on a report that will show total.
    All details are coming from the same table (Questions_tbl)
    my query consists of 4 fields:
    Field 1:
    Supervisor name (I want this to display on the report even if there is not data there for them)

    Field 2:
    Date Check Requested (This will show/count the amount of dates (Between Start date and end- user asked, enter them self's)

    Field 3:
    File Check Status, this has "Requested","Checked". i only want the amount that are checked between the dates as in Field2.

    Field 4:
    Another file Check Status but this time i want it to count the amount "Requested" but i want to count any outstanding NOT Between the dates in field 2.

    All details will go onto a report to show the figures for each Field.

    Can anyone help, i have tried all sorts but keep ending in failure

    I used Total and the count but it keeps showing the Supervisor name serveral times (i just want figures for that supervisor only), i also tried totals but in this case i think i need to use expression builder but it is a week area for me.

    Can anyone help me or point me in the right direction?

    Thank you Simon

  2. #2
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Knowing fields in questions_tbl would help here I think


    Sent from my iPhone using Tapatalk

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Make an outer join query,show ALL emps,
    bring in the fields you want.
    set the report to the query ,
    create groups, turn on footers,
    add the field you want to count inside this group footer,
    Set the field =Count([field])

    to count employees: =Count([employee])
    to count other fields,just enter the field name in the count function.

  4. #4
    Simonhtc4 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    30
    Hi Andy, The fields in the query are the same as they are in the questions_tbl.

  5. #5
    Simonhtc4 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    30
    Hi ranman,

    thanks for the input, i know how to get the final count in the footer but i need it in the details too but everytime i do this i can get one to work and not the other, also i counts Requested and complete in the same report box.

  6. #6
    Simonhtc4 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    30
    Hi Guys,

    Hopefully this will help more.

    Here is the a picture of the Questions_tbl


    From this table im only running a query on the Fields: Supervisor/Date_Check_Requested and File Check Status

    Here is my Query:

    Please ignore the extra file check staus on the end, there should be only 2 file check staus's, If i do Totals and change the count then i i get an error "Data type mismatch in criteria expression"

    This is the report:

    in the supervisor field i want the supervisor name to have one instance.
    In the date requested i want the count of the search between dates the admin want to look for.
    In the File check status (Shown as Expr1002) I want a count of complete in the filed that corrispondes to the supervisor.
    In the File check status (Shown as File Check Status) i want the i total of all the requested regardless of dates asked for.

    I have this details helps to get me to my goal.

    Thank you for the time reading and helping.

    Regards
    Simon

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

Similar Threads

  1. Help with SQL query count function
    By fluffyvampirekitten in forum Queries
    Replies: 4
    Last Post: 08-18-2015, 07:06 PM
  2. COUNT Group Count Records COUNT FUNCTION
    By PMCOFFEY in forum Access
    Replies: 9
    Last Post: 11-09-2012, 09:40 PM
  3. Count Function in query
    By tjstromquist in forum Queries
    Replies: 1
    Last Post: 04-03-2012, 10:40 AM
  4. Count function on query
    By yousillygoose in forum Queries
    Replies: 1
    Last Post: 02-15-2010, 09:58 PM
  5. Query using count function
    By wasim_sono in forum Queries
    Replies: 0
    Last Post: 11-28-2007, 03:16 AM

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