Adjust Field Queried automatically
-
I have a query problem:
Table is set up as follows:
Table name: April
Fields: 1 through 30
I need to dynamically (automatically) query the fields for "A" based on the current date.
IE - Today I would query April.[18] for "a" as well as April.[17] for "a".
Tommorow I would query April.[19] for "A" as well as April.[17] for "a".
Question: Is it possible to have the query automatically adjust the criteria to the appropriate field without the user manually making the change?
-
The question is more "Why are you designing the table like that instead of having a single date column?".
-
Due to specific reports that need to be run - this was the simplist way to set the tables up. I agree that a single date column would have been much easier, but as such it was fudging up a report. So I am trying to get a fix for the current set up rather than trying to start fresh.
-
The Curse and Blessings of Dynamic SQL
I would strongly suggest a set based solution but this might be a hard one compared to dynamic sql.
-
>>Due to specific reports that need to be run - this was the simplist way to set the tables up. I agree that a single date column would have been much easier, but as such it was fudging up a report. So I am trying to get a fix for the current set up rather than trying to start fresh. <<
To be honest you are going against the proper thing DB normalization!
I would fix the DESIGN and not the Query. If that table grows you will be in trouble without doubts. Better now than later
On the other end can you post some DDL of your table so that some dynamic SQL can be suggested (and Please use it as your last Resort)
* Noel -
BTW I've already created a report that looks a lot like a calendar (only 1 week at the time). I too will resuggest that you use dynamic sql only as a last resort... it's always easier to represent normalized data than to normalize a flawed model.
-
IMHO you are asking for big trouble this way, sooner or later - probably rather soon.. If you are at it, you might as well make a table called Current_month and at the end of the month move all data to table Previous_month
I can only second Remi and noeld in their suggestion to repair the source of problems, and not to put fixes around it. It's like plastering a broken leg without reposition of bones... it will heal eventually, but the patient will walk with a limp for the rest of his/her life - or someone will have to break the bone again later, and reposition it.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply