Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038

    Auto-generate queries via VBA

    Experts:

    I need some assistance with *auto-generating* queries based on a "lookup table".

    Please find attached *sample* database with -- for illustration purposes -- only includes minimal data. Please keep in mind that my actual DB is much more extensive... that's why I'd like to automate the query development.

    Below is some background first:

    - Table [00_tbl_Master] includes 11 fields where the first 2 fields ([ID], [Source]) are considered "reference fields" (i.e, they shall be included in every query).
    - Lookup table [TargetTables] includes 2 fields ([TARGET_TABLE], [FIELD]).
    - Table [TargetTables] includes 9 records where [TARGET_TABLE] has two (2) values aka "categories": "Demographics" and "Other"
    - Also, Table [TargetTables].[FIELD] values matches the 9 fields names (besides reference fields [ID], [Source]).

    Existing Queries (manually created):
    - Since I'm only dealing with 2 "reference" fields and 9 "data" fields, I have manually created two queries:
    1. qryDemographics: It includes the 2 reference fields + the 4 fields ([LastName], [FirstName], [Gender], [Age])... based records 1 through 4 in table [TargetTables].
    2. qryOther: It includes the 2 reference fields + the 5 fields ([Email], [Phone], [Education], [Occupation], [MaritalStatus])... based records 5 through 9 in table [TargetTables].



    More background:
    a. As mentioned at the beginning of the thread, this sample database includes only a few fields. However, my actual database includes more than 200 fields in [00_tbl_Master].
    b. And, to make matters more interesting, my actual table [TargetTables] includes roughly 20 "categories" (equivalent to "Demographics" and "Other").
    c. And, finally, for the time being, there's a good chance that fields (e.g., [Education] and/or more) may be labeled as "Demographics" vs. "Other".

    Now, here's what I need some help with:
    - Based on "More background " c.), I want to auto-generate the 2 queries "qryDemographics" and "qryOther".
    - That is, in the event I re-categorize field [Education] as "Demographics" in [TargetTables], I want to **automatically** add [Education] to query "qryDemographics"...
    - ... while at the same time, remove [Education] from query "qryOther".

    So, ultimately, I want to be able generate n queries based on the information stored in table [TargetTables]. No matter which query though, the 2 "reference" fields ([ID], [Source]) must always be included in any query.

    Can the query generation/update be automated via VBA? If so, how?

    Thank you,
    Tom
    Attached Files Attached Files

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    @skydivetom
    However, my actual database includes more than 200 fields in [00_tbl_Master]
    Wow. Then I would suspect and suggest that your db is in serious need of normalization.

    So, ultimately, I want to be able generate n queries based on the information stored in table [TargetTables]. No matter which query though, the 2 "reference" fields ([ID], [Source]) must always be included in any query.
    Just being inquisitive, but what do you want to do with these queries, once you've created them.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I'm struggling to understand the net result of this.

    As per Bob, what is the desired end result, you may be using a sledgehammer to crack a nut here.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Tom,
    Here is one way of doing that.
    Cheers,
    Vlad
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Vlad -- you are my **HERO**!!!

    Your solution worked exactly as specified in the original thread. PERFECT!!!!!

    1. I ran the VBA and it generated the 2 queries (per table [TargetTables] -- GREAT!
    1. For testing purposes, I then changed Education's category from "Other" to "Demographics" in [TargetTables].
    2. I re-ran the VBA and... voila, the [Education] field shifted from qryOther to qryEducation -- GREAT!

    Just one quick follow-up question... it appears the order of the fields in qryDemographics shifted. That is, [Education] is now the 1st field after [Source].

    My question: Is it possible to insert the fields into the queries' where [ID] & [Source] will always be 1st/2nd field but then followed by remaining fields in ASC order? If not won't work, can we keep the 9 fields in order as they are listed in [TargetTables]?

    Thousand thanks!
    Tom
    Attached Thumbnails Attached Thumbnails Solution.JPG  

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Tom, glad to hear it is working for you. To include the fields order ascending just add ",[FIELD]" after Target_Table in the Currentdb.Openrecordset line (to order by both fields).
    Cheers,
    Vlad

  7. #7
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Awesome... works like a charm now!!! Again, thank you for superb help, Vlad!!

  8. #8
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Vlad:

    Again, thank you for your assistance w/ the automated query development. It works great!

    In the last 2 days, I had to step away from "this process" but now need to fine-tune it.

    Please allow me to recap first (current process):
    1. Again, this DB includes only sample data... actual DB includes additional target tables which, in turn, will result in additional queries.
    2. Now, open up form "F01_MainMenu".
    2a. Click on command button "Delete All Queries".
    2b. Click on command button "Generate ETL Products".

    ... so far so good.

    I'm wondering if the following could be achieved:
    - Once all queries have been deleted & re-generated, would it be possible (e.g., command button #3) to replicate both SELECT queries and their their duplicate into MAKE TABLE queries (mqry)?
    - So, right now, I have "qryDemographics" and "qryOther".
    - Command button "Create Make Table Queries" would then copy both queries and name them "mqryDemographics" and "mqryOther".
    - The **table name to be used** would be a function of stripping of "qry" or "mqry" and replacing it with "tbl".
    - In other words, once the two queries have been copied and changed to an "mqry" the table names would be = "tblDemographics" and "tblOther".
    - Lastly, either in the same step (or step #) I would want to execute the two make-table queries.

    Recap:
    - Step 1: Existing command button "Delete All Queries" does exactly that... all existing queries are removed
    - Step 2: Existing command button "Generate ETL Products" does what it says... it re-generates all SELECT queries based on table [TargetTables_String]
    - Step 3: New command button "Create Make Table Queries" copies all existing queries (e.g., "qryDemographics" and "qryOther") and adds same number of mqueries (in Make Table format).
    - Step 4: New command button "Execute Make Table Queries" executes all m-queries where each newly create table starts with "tbl" + "Demographics" (stripped off either qryDemographics or mqryDemographics").

    Upon executing step #4, I would have n table/query objects (in this example):
    4 tables: 00_tbl_Master_String; TargetTables_String; tblDemographics; tblOther
    4 queries: qryDemographics; qryOther; mqryDemographics; mqryOther;

    What would the required VBA for new functions (step 3 and step 4) look like?

    Thank you,
    Tom
    Attached Files Attached Files

  9. #9
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Here you are Tom, I have incorporated the last two steps in the original code as it is much easier to do it at that time when all required variables are already available.

    Cheers,
    Vlad
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  10. #10
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Just beautiful... JUST BEAUTIFUL!!!!!

    Thousand thanks for the VBA mod... it works perfectly, Vlad!!

    You are AWESOME!!!!

  11. #11
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    No worries Tom, any time!

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  12. #12
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Vlad:

    Would it be possible to provide me some additional assistance with slightly tweaking the process in file "Auto-Generate Queries_Vlad v03"?

    Below summarizes the single change:
    1. I inserted field [ALIAS_FIELDNAME] in table [TargetTables_String]. For *testing only*, I copied the values from [FIELD] but added prefix "Alias_".

    Modfied process (this is where I need some help with):
    - The overall process remains the same... that is, I still need to create queries based on table [TargetTables_String].
    - The only thing that requires a change is the the final tables (e.g., tblDemographics, tblOther) should now have fields based on the newly added field "ALIAS_FIELDNAME" (vs. "FIELD").
    - All else remains the same... i.e., data in 00_tbl_Master_String still uses the original field names.

    Please note that adding prefix "Alias_" will NOT suffice. I only used it for demo purposes. In my actual application, I need the 3rd column (ALIAS_FIELDNAME) which may include a completely different *target* name (i.e., instead of "Lastname" I may have value = PERS_LAST").

    So, again, is there any way the code can be modified which would result in the tables produced using the alias field names?

    Thank you,
    Tom

    P.S. Also, is there a way to add a counter to the GenerateQueries module? While not critical, it would be nice to see that n number of tables were produced. But again, this would be just "icing on cake" so to speak.

    P.S.S. I'll gladly open a new thread and cross-reference posts (if preferred).
    Attached Thumbnails Attached Thumbnails AliasFieldnames.JPG  
    Attached Files Attached Files
    Last edited by skydivetom; 05-20-2021 at 08:34 AM.

  13. #13
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Tom,
    Here it is. Please note that I modified the logic a bit so you only need to add the alias where different than the original FIELD name.
    Cheers,
    Vlad
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  14. #14
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Vlad -- wow... this is ****SUPER-IMPRESSIVE****. Everything works great. That is, if there's no alias field name, it goes with original field name; otherwise the alias. PERFECT!

    I love how your code is sooo dynamic! THOUSAND THANKS AGAIN!!!!

  15. #15
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You're very welcome Tom!

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Auto Generate ID Based on a Query
    By KBNETGUY in forum Programming
    Replies: 6
    Last Post: 04-03-2018, 12:41 PM
  2. Auto email generate
    By nherbert31 in forum Queries
    Replies: 4
    Last Post: 08-01-2017, 12:38 PM
  3. Use a Query to generate auto ID
    By banpreet in forum Queries
    Replies: 2
    Last Post: 07-28-2016, 01:28 PM
  4. Auto generate records
    By RokitRod in forum Database Design
    Replies: 1
    Last Post: 10-02-2012, 10:45 AM
  5. Auto Generate Record
    By mjhopler in forum Forms
    Replies: 2
    Last Post: 02-10-2010, 03:40 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