Results 1 to 4 of 4
  1. #1
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038

    Have CNT query based on numeric values... try to accompllish CNT query based on text fields now

    Experts:

    I need some assistance with the development of a query. Attached is a database with a single table and one query. Allow me to provide some background first.

    Table "T103_N1S_Billets":
    - Includes a total of 1,648 records
    - Contains eight (8) numeric fields. Note: In my actual database, these are my **current** fields. Naturally, there are additional fields as well... these are not relevant for this question though.
    - Contains eight (8) text fields. Note: These are for testing purposes only at this time.

    Query "Q147_N1S_BilletsAuthorizedOnboard":


    - Query includes a total of 8 expresions (linked to the 8 numeric fields).
    - Also, it includes of 2 additional "Total" expressions summing up the first 4 "BA.." fields and then the next 4 "OB.." fields
    - This query works great! The output of the counted numeric values (where criteria = 1) PLUS the two totals (expressions) outputs the the correct count!

    ... so far so good!

    Additional information:
    - Per the attached table, I'm currently storing the fields as numeric values. However, I'm considering storing these binary values as "text" (i.e., "Yes", "No").
    - Thus, for testing purposes, I added 8 additional fields and marked them with a suffix of "_Text".
    - Then, I ran update queries where 1 equals "Yes" and 2 equals "No".

    What I Need Some Help With:

    - Ultimately, I want to output the same exact results of the existing query; however, I want these based on the "text" fields (vs. numeric fields).
    - Also, just like in this example, I'd like this to be accomplished in a single query (vs. having to use a "helper" query first).
    - Note: I my actual database, I need to add another table (as part of the query) IOT include one additional field criteria. I presume linking an additional table won't make a difference on the query.

    So, my question: Can the existing query be replicated using the text fields? If so, how?

    Thank you,
    Tom
    Attached Files Attached Files

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    So for all of these fields they can only be yes or no? (0 or 1)

    Your current expressions are like
    Code:
    BA_Off: Sum(IIf([Acct_BA_Off]=1,1,0))
    I'm not sure why the IIF statement is necesssary at all, just sum it up directly
    Code:
    BA_Off: Sum([Acct_BA_Off])
    .
    To answer your question directly to count the text fields you could simply do this
    Code:
    BA_Off: Sum(IIf([Acct_BA_Off]="Yes",1,0))
    BUT I wouldn't do it this way. Storing these fields as text wastes space and is inefficient.

    I would use integer fields, and in microsoft access Yes = True = -1 (that's negative one), and No = False = 0. So create yes no type fields or integer fields and have a validation rule of "-1 Or 0"

    http://allenbrowne.com/NoYesNo.html

    Your data entry forms can still utilize check boxes or combo boxes to display "yes" or "no".

    Why are you wanting to use only a single query and not utilize subqueries?

  3. #3
    Bullschmidt's Avatar
    Bullschmidt is offline Freelance DB Developer
    Windows 10 Office 365
    Join Date
    Mar 2020
    Location
    USA
    Posts
    64
    BA_Off: Sum(IIf([Acct_BA_Off]=1,1,0))
    And actually Microsoft Access / VBA store a Boolean True as -1 not 1...

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    VBA store a Boolean True as -1 not 1...
    Well, not quite. In vba, False is always zero. Any other number is True.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 3
    Last Post: 07-26-2016, 02:12 PM
  2. Replies: 3
    Last Post: 06-23-2014, 02:02 PM
  3. Replies: 1
    Last Post: 10-29-2012, 08:15 AM
  4. Replies: 2
    Last Post: 05-16-2012, 04:45 PM
  5. Replies: 10
    Last Post: 07-02-2011, 11:51 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