I need some assistance with creating dynamic queries. Allow me to provide some background on the MS-Access (test) database first (see attachment).
- It includes two fields: [2-DigitNumber] and [3-DigitNumber].
- For testing purposes, I've added four arbitrary records (2-digit numbers and four 3-digit numbers), respectively.
- This query could be deleted afterwards.
- I've merely used the query wizard to generate the SQL code such as: "SELECT Avg(Table1.[2-DigitNumber]) AS Average FROM Table1;"
- It includes an unbound list box with the following VBA-driven row source (which have not been tied to actions yet):
-- "Run Average of 2-Digit Numbers"
-- "Run Average of 3-Digit Numbers"
My goal (again, testing purposes only right now):
- User opens the database (frmLogin)
- If user selects the list box option "Run Average of 3-Digit Numbers", then a report (or query) opens.
- The data source is dynamically generated based on the list box's value section of either "Run Average of 2-Digit Numbers" or "Run Average of 3-Digit Numbers".
- Thus, SQL code for the report's data source is either "SELECT Avg(Table1.[2-DigitNumber]) AS Average FROM Table1;" or "SELECT Avg(Table1.[3-DigitNumber]) AS Average FROM Table1;"
Given that "Run Average of 2-Digit Numbers" is NOT the field name, I somehow have to cross-reference the string to field, e.g., [3-DigitNumber].
- If that's accomplished, then the new SQL brings up a report that shows "552.5" (the average of the four 3-digit records) vs. "18.5" (the average of the four 2-digit records).
Again, this simple example (using averages) is for creating a general framework first. Once that works, I will expand the framework in support of the actual database.
Any thoughts as to how this can be accomplished? Please see attached database as a starting point.
”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.
-Mike Kapnisakis, Warner Bros
With your subscription - you'll gain access to our exclusive IT community of thousands of IT pros. You'll also be able to connect with highly specified Experts to get personalized solutions to your troubleshooting & research questions. It’s like crowd-sourced consulting.
We can't always guarantee that the perfect solution to your specific problem will be waiting for you. If you ask your own question - our Certified Experts will team up with you to help you get the answers you need.
Our certified Experts are CTOs, CISOs, and Technical Architects who answer questions, write articles, and produce videos on Experts Exchange. 99% of them have full time tech jobs - they volunteer their time to help other people in the technology industry learn and succeed.
We can't guarantee quick solutions - Experts Exchange isn't a help desk. We're a community of IT professionals committed to sharing knowledge. Our experts volunteer their time to help other people in the technology industry learn and succeed.