Building a dynamic query in Access

[xqv]

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.

 

[FrancisL]

"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

http://www.controldraw.co.uk

http://www.s88control.blogspot.com

 

[xqv]

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?

 

[ramys]

You can do that using any .net language like C# with Ado.net or linq