Query to build fields dynamically

[jtokach]

Hi there,

I have a table where columns are added frequently. I'd like to create a query, in Access, that only list some fields based on the field name. For instance, five columns are prefixed with "MS04", like MS04-011 and MS04-012. The query should build the results table dynamically and should only contain fields that begin with MS04.

Thanks!

 

[dhookom]

Your table is not normalized. If you normalize your table you will:
1 probably never have to add more fields
2 have the ability using a crosstab to limit the returned "columns/fields"

Is there a good reason why you have fields like this? Is it possible to normalize your data?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[jtokach]

The table contains the following:

workstation hotfix01-01 hotfix01-02 hotfix02-01 hotfix02-02 etc.
------------------------------------------------------------------
computer1   YES         NO          YES         YES
computer2   YES         YES         NO          YES

Since M$ comes out with a new hotfix frequently, I setup MBSA to scan the boxes, generate data, compare the data columns with the DB columns, if there is a new hotfix, a new column is created. This is a very hands off approach, and while it fails ACID, it saves time and $$$. I can manually add the columns to the Access query, but would rather autogenerate the query table.

I am open to suggestions regarding my format.

 

[dhookom]

I would use a more normalized table structure that has a structure like:

tblWSHotFix
WorkStation
HotFixNumber
DateOf

tblHotFixes
HotFixNumber
HotFixDate

tblWorkStations
WorkStation

Then, when a new hot fix comes out, it gets added to tblHotFixes. When a workstation gets updated, it adds a record to tblWSHotFixes. You can then create a crosstab query with the HotFix number as the column heading.

If this doesn't work, you may need to use code to modify the SQL of a saved query to display a limited group of fields.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.