Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2004
    Posts
    364

    Unanswered: Query and totals question

    Hello, I have a few questions regarding Access 2003.

    1) What is the best way to record (in a table) a yes/no response? E.g. if someone arrived to an appointment or not - Yes they arrived, or no they didnt. Should it be a combobox value list of "yes" and "no"?

    2) What is the best way to enable a query count the total of "Yes" and the total of "No" from that table? E.g. Yes=45, No=35.

    3) How can I display the total of "yes" and the total of "no" each in a textbox on a form? I have tried the following Dcount
    Code:
    =DCount("[arrived]","[arrival_qry]","[arrived] = yes")
    and
    Code:
    =DCount("[arrived]","[arrival_qry]","[arrived] = no")
    - but only the "yes" function actually gives an answer. The no function returns "0" in the textbox.

    Thankyou

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. You store the value as a boolean (yes/no) into the table. The way you present it in a form (option buttons or radio buttons in an option group, textbox, combo, etc does not matter as far as the storage of the value is concerned.

    2. SELECT COUNT(<Column>) FROM <Table or Query> WHERE <Column> = True (or = False)

    3. It should be "[arrived] = True" and "[arrived ] = False" or "[arrived] = -1" and "[arrived ] = 0" or even "[arrived] <> 0" and "[arrived ] = 0"
    Have a nice day!

  3. #3
    Join Date
    Aug 2004
    Posts
    364
    Thankyou for your reply.

    I cannot get both Yes or No totals in the same query. I can get one or the other to work with this for arrived = YES -
    Code:
    SELECT Count( * ) AS arrived
    FROM visit
    WHERE (((visit.arrived)='Yes'));
    and for arrived = NO -
    Code:
    SELECT Count( * ) AS arrived
    FROM visit
    WHERE (((visit.arrived)='No'));
    But if I try to make the query display both YES and No totals, with the following code I get "0" for both results -
    Code:
    SELECT Count( * ) AS yes, Count( * ) AS [no]
    FROM visit
    WHERE (((visit.arrived)='Yes' And (visit.arrived)="no"));
    I dont mind having two seperate queries for each result, but I would prefer one single query if possible.

    Can it be done?

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can create two queries: one that returns arrived when arrived = True and the second that returns arrived when arrived = false, and then combine both queries in a third performing the Count operations. In SQL you can do that in one step with subqueries: SELECT ... FROM ( SELECT ... ), but Access is a bit limited with that syntax.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •