Changing the column name a query is looking for.

[ajw5173]

Hi

I'm dumping historical data into access by week. Each column is its own week. i want to write queries off this that change which columns of data they are selecting depending on the date. So if it's may week 1 select that. When it is is may week 2 select that column.

Thanks!
-Andy

 

[Joe4]

What you are describing is not a normalized data structure. Databases that are not normalized can be very difficult to work with!
For rules on Data Normalization, see: https://support.microsoft.com/en-us/kb/283878

Basically, in a normalized database, you would not have similar repeating fields, and new fields should not have to be created every time you import new data.
Instead of having a column for each week, you would have a week number field, and a value field.
So, if you had 10 different weeks, instead of having one record with 10 different columns worth of data, you would have 10 records, one for each week.
It makes working with the data much easier. For example, in order to search for any week, you only need to search one column (not multiple columns).

If, for presentation purposes, you need to show weeks going across, you would use a Cross Tab Query to do that.
https://support.office.com/en-us/ar...ab-query-8465b89c-2ff2-4cc8-ba60-2cd8484667e8

 

[ajw5173]

Hi!

Thank you for your response. I totally agree with you but this was the request that I was given and I wanted to see how difficult it would be to do it. I think I will end up structuring the table the way that you are saying.

Thanks!
Andy