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