Results 1 to 5 of 5
  1. #1
    Thomasso is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250

    Count of Yes/No fields set as "Yes"


    Hello everyone!

    I store telephone numbers and e-mails of my business partners in my Access database. Each person can have multiple numbers and e-mails.

    There are tables:
    tblPeople
    tblPhoneNumbers
    tblEmails

    The relationships are obviously 1:N in both cases.

    Now, I have a form where all the people are listed, along with their MAIN contact information. I want everyone to have exactly ONE main telephone number and ONE main e-mail address.

    There is a Yes/No field in the 2 latter tables that indicate this. However, I want to forbid the DB users to accidentally set multiple records as the main information.


    The idea is that I create a query that lists all people and COUNT information of how many contacts have been set to MAIN. Then I want to put VBA code on CloseForm event - simply said - If the count is NOT 1, then write error and don't do anything. It it is 1, then save the changes.


    How would this query look like? I can't seem to get good results.

    Thanks!
    Tomas

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    I made assumptions about what your field names are, you'll need to modify the code below. This sql will return a count of main numbers and emails for each person in the people table.
    Code:
    SELECT
       tblPeople.ID,
       tblPeople.FirstName,
       (
          SELECT
             Count(*) 
          FROM
             tblPhoneNumbers 
          WHERE
             PersonID = tblPeople.ID 
             AND IsMain = True
       )
       AS MainPhoneNumbers,
       (
          SELECT
             Count(*) 
          FROM
             tblEmails 
          WHERE
             PersonID = tblPeople.ID 
             AND IsMain = True
       )
       AS MainEmails 
    FROM
       tblPeople;
    EDIT:
    The following sql will only return the people with invalid counts:
    Code:
    SELECT
       qryMainCount.ID,
       qryMainCount.FirstName,
       qryMainCount.MainPhoneNumbers,
       qryMainCount.MainEmails 
    FROM
       (
          SELECT
             tblPeople.ID,
             tblPeople.FirstName,
             (
                SELECT
                   Count(*) 
                FROM
                   tblPhoneNumbers 
                WHERE
                   PersonID = tblPeople.ID 
                   AND IsMain = True
             )
             AS MainPhoneNumbers,
             (
                SELECT
                   Count(*) 
                FROM
                   tblEmails 
                WHERE
                   PersonID = tblPeople.ID 
                   AND IsMain = True
             )
             AS MainEmails 
          FROM
             tblPeople 
       )
       AS qryMainCount 
    WHERE
       (
    ((qryMainCount.MainPhoneNumbers) <> 1)
       )
       OR 
       (
    ((qryMainCount.MainEmails) <> 1)
       )
    ;
    Then in your vba if that returns anything you know you need to cancel the form close

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    when user click a field to YES, in the AFTERUPDATE event,
    run a query the sets ALL phone recs for that Contact to false EXCEPT the current PhoneID that was just marked yes.

    Code:
    sub txtDefault_Afterupdate()
      docmd.openquery "quClearContactPhoneFlags"
    
    end sub

    query quClearContactPhoneFlags:
    update tContactPhones set [phoneDflt] = false where [contactID] = forms!fContact!txtID and [PhoneID]<> forms!fContact!subform!form!txtPHoneID

  4. #4
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    ranman's idea is much better!

  5. #5
    Thomasso is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250
    Thank you both! I agree, I will use the ranman's solution. But thank you both :-)

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

Similar Threads

  1. Replies: 1
    Last Post: 05-12-2018, 11:26 PM
  2. Replies: 2
    Last Post: 01-23-2017, 07:06 PM
  3. Replies: 4
    Last Post: 12-20-2015, 12:16 PM
  4. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  5. "Count" and "Countif" in Reports
    By JMantei in forum Reports
    Replies: 1
    Last Post: 06-20-2006, 02:20 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