Run Queries based on number of fields

[thebellguy]

Hi, I am probably making this much more difficult than it needs to be but hopefully someone out there has an answer.

I have an excel data file that is updated monthly (and I am not able to have the source file format changed of course) that will be my linked table for Access 2007.

The file reports monthly data and adds the new month to the right of the historic in a layout like this:

Tree | Orchard | Province | Jan % red | Jan % green | # Limbs | Feb % red | Feb % green... etc so each month it adds 3 new fields to the data.

I have queries (rightly or wrongly) that will calculate the number of apples that month and report on those higher than a certain percentage. I would like to do this without having to create a static table to append to each month as the new data file will always show the full YTD results anyway (and I don't like to store data in my database).

I have a query that counts the number of fields in the raw data file and am wondering if there is a way to have access only run the number of queries required based on the number of fields. i.e. if 11 fields then run queries 1 thru 5, if 14 run 1 thru 6 etc.

Even this will be a long and clunky way to do this but can't seem to get my head around anything else aside from manipulating the raw data file first.

Any ideas?

 

[Pat Hartman]

You can't change the columns in a query on the fly. You would have to do it by rebuilding the query each month either with VBA or manually. A better solution is to normalize the data as you inport it so that you have a row for each month. That way all your analysis queries are standard. They just work with whatever rows are present.

 

[thebellguy]

I will make that work. Was hoping to be able to pass this off to different people to run each month but no luck now haha.

Thanks.

 

[Pat Hartman]

If you normalze the spreadsheet data as you import it, why wouldn't others be able to run that process?