How to Create a Dynamic Query

[Beto1967 via AccessMonster.com]

I have a query where its possible to have up to 25 col of data at any giving
week. Can somebody walk me thru the steps in how I can create a Dynamic
query where it would report out on the datafields that are present in the
query. For example if I have
gb1, gb2, gb3 for this week the query will only show me gb1,gb2 and gb3 next
week I've gb1, ict2 and doaa3 when I run the query it would show those. If it
would count every instance of each diffrent datafield for example gb1 had 3
and gb2 had 4 .... thank you for your assistance in this matter FYI at
present if I include all 25 col and I only have 5 that has data I get and
error that the col don't exist which is a true statement

 

[Michel Walsh]

Normalize your data and use a crosstab query as source of your report.

By 'normalize', I mean, instead of

someId, gb1, gb2, gb3, ict1, ict2, doaa1, doaa2, doaa3 'fields
1010 1 null 3 3 -1 null null 16
'data sample


try:

someId, ofType, theValue ' fields
1010 'gb1' 1
1010 'gb3' 3
1010 'ict1' 3
1010 'ict2' -1
1010 'doaa3' 16 ' data



as table, and the crosstab:

TRANSFORM SUM(theValue)
SELECT someID
FROM yourNewTable
GROUP BY someID
PIVOT ofType




Hoping it may help,
Vanderghast, Access MVP

 

[Jerry Whittle]

If the number of columns in a table change weekly, it's pretty obvious that
your data isn't properly normalized. You need to figure out a way to pour
this data into a table that a query can handle the data without the need for
being dynamic.

Possibly you could post some sample data, deatils of it's meaning, and your
existing SQL statements. Then we could help you create a table and a way to
pour the data into this table.