I am building an Access database to automate the printing of COA's. I have one table (table 1) of the test data 50+ tests, and one table (table 2) of what specific tests specific customers require. I need to build a query that will show me the only the test results for the tests required by a specific customer. So I need the tests specified in a query of table 2 to become the field names in a query of table 1. Does any one have and example of how something like this can be done? I am fairly new to writing code, so I may need a bit more explanation of the code.
Eng-Tips is the largest engineering community on the Internet
Intelligent Work Forums for Engineering Professionals
-
Congratulations waross on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Building a dynamic query in Access
- Thread starter xqv
- Start date
"So I need the tests specified in a query of table 2 to become the field names in a query of table 1."
Not necessarily. Yes you can build queries in code, but in this case it may be that you can use a join query to link the two tables and a filter to select your customer. Or perhaps a third table is needed for the test results.
More info would get a better answer, and you might get a better response on Tek-Tips.com
Francis
Not necessarily. Yes you can build queries in code, but in this case it may be that you can use a join query to link the two tables and a filter to select your customer. Or perhaps a third table is needed for the test results.
More info would get a better answer, and you might get a better response on Tek-Tips.com
Francis
- Thread starter
- #3
Thank you, I will also try Tek-tips.
Here is more information. We have various customers that require a certificate of analysis for our products. One customer might require test1 test 2 and test 3 be reported, another might require test2 and test3. I have one table that has the data for tests1-20. In the end I need to create a report that based on which customer I am preparing a COA for, I get only those tests they require. I could simply run a query to gather the results from tests1-20, then prepare individual reports for each customer selecting from that query the fields I need, but then I would have to construct a new report for each customer. I am trying to do this so I can use one query and one report for all customers. To do this, I believe I need one table to specify which tests a customer requires. Then those tests need to become the fields in query to retrieve the test results from the data table for the products the custoerm ordered. Make sense?
Here is more information. We have various customers that require a certificate of analysis for our products. One customer might require test1 test 2 and test 3 be reported, another might require test2 and test3. I have one table that has the data for tests1-20. In the end I need to create a report that based on which customer I am preparing a COA for, I get only those tests they require. I could simply run a query to gather the results from tests1-20, then prepare individual reports for each customer selecting from that query the fields I need, but then I would have to construct a new report for each customer. I am trying to do this so I can use one query and one report for all customers. To do this, I believe I need one table to specify which tests a customer requires. Then those tests need to become the fields in query to retrieve the test results from the data table for the products the custoerm ordered. Make sense?
- Status
Similar threads
- Locked
- Question
- Locked
- Question
