Results 1 to 10 of 10

VBA function that "synchronizes" fields value

  1. #1
    skydivetom is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    77

    VBA function that "synchronizes" fields value

    Experts:

    I need some assistance with, potentially, writing a module that scans through a number of fields and **synchronizes** values in the event either blank or inconsistent value appear for the same GROUP-ID number?

    As a picture is worth a thousand words, I have attached two documents:

    1. Excel file… it shows the sample data “before” and “after” plus some additional information.
    2. Access file… it includes the following:
      1. Table “tblSampleTable” includes the BEFORE sample data
      2. Query “qry01_Concatenate_FirstThreeFields” … successfully handles the concatenation (based on module “mod_Concatenate”).
      3. Query “qry02_Concatenate_FirstThreeFields_PlusCity” … this is where I need some help with… once I throw additional fields into the query, my record count increased past four (4) records.



    My question: Does anyone know of a function that “synchronizes” e.g., [Age] in the event any of the age values (for same GroupID) is either missing or is different than the other age values for the same GroupID?

    Thank you for your help in advance,
    EEH
    Attached Files Attached Files

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,482
    No, there is no 'function' to synchronize. How should Access know to replace FEMALE with MALE or vice versa.

    If it doesn't matter which value is returned for those additional fields, why include them? But if you must include, try using Max() function on those fields in the aggregate query.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    skydivetom is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    77
    June7:

    Thanks for chiming in... conceptual logic is as follows:

    - scan through records with alike GROUPID
    - where GROUPID is the same, take value of first record and replace subsequent records (for same field)
    - if first record has a null value, take value of next record <> null and update values of records with alike GROUPID

    Is there no chance this could be programmed?

    Thanks,
    EEH

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,482
    It can be programmed. You really want to change data to replace MALE with FEMALE?

    However, do you care which record is considered 'first'? FEMALE will sort before MALE so FEMALE will always be 'first' unless there are nulls because Null will sort to top ASCENDING. Or should the sort be based on unique record ID?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    skydivetom is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    77
    I just tried "First" (vs. GroupBy) and I show only four records when the additional five columns (City, AllegedOffender_Age, AllegedOffender_Gender, Victim_Age, and Victim_Gender) are added to the query. However, for those records where the first value is empty, the grouped/merged value is also empty. Ideally, I would want to not show any null values if secondary or tertiary records have a value.

    Wrt to your question, ideally, "majority counts". If first value = male but secondary and tertiary values = female, I then would want to update to "female". Right now, however, it's most important that I can group these records. There's some preliminary cleanup (which I didn't elaborate on) prior to running this query... more than likely the male/female difference would have been cleaned. Maybe a better example should have been "City"... don't really care too much if someone writes "Virginia Beach" or "VA Bch"....

    Any recommendations (besides using the "First" option) would be greatly appreciated.

    Lastly, I forgot to mention... my actual data set has approximately 5,000 records and I need to apply the *synchronization* probably across 30+ columns. I tried the concatenation process with this dataset and at some point the query failed indicating "record is too large". I only mention this since any proposed solution must allow to process a relatively large # of records across a substantial # of fields.

    Thank you again, June7. I appreciate your help.

    EEH

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    13,913
    This is one approach along the line that June suggested. I chose Min rather than Max but am unsure of what you need and why.

    SELECT tblSampleTable.GroupID
    , Min(tblSampleTable.AllegedOffender_Age) AS MinOfAllegedOffender_Age
    FROM tblSampleTable
    GROUP BY tblSampleTable.GroupID;

    Are you creating some data for some purpose ( a mock up)?
    You could use a Random number approach but it seems a bit like "fudging data we forgot to collect".
    Perhaps a clearer explanation would help.

  7. #7
    skydivetom is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    77
    orange, June7:

    Thank you... I missed the "max" comment by June7... my apologies.

    Wrt to string values, what's the difference between MIN and MAX? Also, I had tried the GroupBy for a larger data set at some point... what's the likelyhood that this approach would succeed with 5k records across 30+ columns?

    Thank you both for assisting me w/ this question.

    EEH

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,482
    Use Max or Min as suggested and Null will be ignored. Having 'majority rule' would be far more complicated.

    Again, why include these fields if their data is irrelevant to the analysis?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    13,913
    Min Max would apply to number type data.
    Can you step back and tell us in real simple terms what you are trying to accomplish?
    I don't see 5 or 50 thousand records being a problem---the devil is always in the details.

    I'm sure the forum can help, but we need to understand the issue.

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,482
    No, those also work with text, just maybe less efficient than numbers.

    FEMALE sorts before MALE so FEMALE returns with Min and MALE with Max.

    I still don't understand why you would include these fields when their values are irrelevant.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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. Suppress "Requery" and/or "Repaint" when running function
    By GraeagleBill in forum Programming
    Replies: 3
    Last Post: 12-08-2017, 12:04 AM
  3. Replies: 3
    Last Post: 04-20-2016, 02:50 PM
  4. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  5. Replies: 2
    Last Post: 11-04-2011, 02:45 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
  •  
Tech Forums: Microsoft Office Forums