how to query specific fields based on other table field requirements?

[MsAxes]

Hi all.

I have a table named 'Query_Req' that contains the fields that are required for that specific type of customer. I also have a table with data that I would like to query (up to 10k records) but only return the fields that customer requires.

Would it be possible to have my query automatically return the required fields from the 'Query_Req'? This table will be updated on an annual basis and required fields might be added/removed.



Ex. For ex. Customer_B only needs four fields, so a query would only return those.

I understand I would need two queries to achieve this and that's okay. I just don't know if this is possible or how to start.

[Welshgasman]

I would build the query statement by reading through the recordset of your customer fields?

If you have them horizontally instead of vertically, then read through each field in the record.

[Gicu]

Please have a look at the attached sample, I think it should give you what you need .

Cheers,

[MsAxes]

Thanks Gicu - how did you get the query to change to the required fields and filter? I am trying to understand but lost there a bit.

[Gicu]

Have a look at the AfterUodate event of the customer type combo box. I use a public VBA function to build the comma- separated string of field names for the selected type then use that to update the SQL property of the query (using the DAO.QueryDef.SQL property).
Cheers,

[MsAxes]

thank you - I see that now.