If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
"dynamic" column lookup
I need to use the value in one field to determine which column in another
table needs to be used to retrieve the corresponding value. In detail, I have a table which has different values for each time period for each day of the week, e.g., Monday from 9-12 has one value, Tuesday from 3-6 has another value, etc. resulitng in 8 values for each day for a total of 56 different values (columns). These values are different for each record. Based on a time stamp, I need to identify which column I need to use for a value. For example, if the result of my time stamp is Friday 9-12, then I would like to be able to do something like - Value = Table_Name.Column (Friday 9-12), where Friday 9-12 is the results of my time stamp analysis. Is this possible? I am looking for something similar to the Indirect function in Excell. Any help would be much appreciated. |
#2
|
|||
|
|||
Hi,
You may have a table like: TheWeekday, ThePeriod, TheValue 2, 4, xxxx 2, 5, yyyy .... 6, 4, zzzz .... ie, Monday (weekday=2), for the fourth period (of 3 hours, so, from 9 to 12), the value is xxx. ? DLookup("TheValue", "TableNameHere", "TheWeekday=5 AND ThePeriod = 4") would return the value for Thursday, the fourth period. There are other alternatives for the design, that's sure. The one I used is only for illustration. Hoping it may help, Vanderghast, Access MVP "xopher" wrote in message ... I need to use the value in one field to determine which column in another table needs to be used to retrieve the corresponding value. In detail, I have a table which has different values for each time period for each day of the week, e.g., Monday from 9-12 has one value, Tuesday from 3-6 has another value, etc. resulitng in 8 values for each day for a total of 56 different values (columns). These values are different for each record. Based on a time stamp, I need to identify which column I need to use for a value. For example, if the result of my time stamp is Friday 9-12, then I would like to be able to do something like - Value = Table_Name.Column (Friday 9-12), where Friday 9-12 is the results of my time stamp analysis. Is this possible? I am looking for something similar to the Indirect function in Excell. Any help would be much appreciated. |
#3
|
|||
|
|||
My static table is set up as follows:
Acct#, Time_Period_1 (Sunday 3-6), Time_Period_2 (Sunday 6-9), etc. 123456, 45, , 60, , etc. 456789 60, , 58, , etc. In my data table For account # 123456, I have a time stamp of Sunday, March 13, 2005, 06:32:45 AM From a query, I can determine that the Time Period is Time_Period_2 and I can return the value of 60. One thing that I neglected to state in my previous posting was that I am calculating depletion rates for each acccount, i.e., given a starting point (time stamp) and a quanity, I need to calculate how much of X is consumed in each 3 hour period and during which time period will I reach 0. I know the rate of consumption for each period, but I need the time stamp to know where I am starting. I have a table which allows me to identify my starting time_period, but then I need to cycle through all of the 56 time periods. I.e., in the example above, I begin in Time Period 2 and I need to cycle through to time Period_1. Will the solution below allow me to accomplish what I'm trying to do? Thanks "Michel Walsh" wrote: Hi, You may have a table like: TheWeekday, ThePeriod, TheValue 2, 4, xxxx 2, 5, yyyy .... 6, 4, zzzz .... ie, Monday (weekday=2), for the fourth period (of 3 hours, so, from 9 to 12), the value is xxx. ? DLookup("TheValue", "TableNameHere", "TheWeekday=5 AND ThePeriod = 4") would return the value for Thursday, the fourth period. There are other alternatives for the design, that's sure. The one I used is only for illustration. Hoping it may help, Vanderghast, Access MVP "xopher" wrote in message ... I need to use the value in one field to determine which column in another table needs to be used to retrieve the corresponding value. In detail, I have a table which has different values for each time period for each day of the week, e.g., Monday from 9-12 has one value, Tuesday from 3-6 has another value, etc. resulitng in 8 values for each day for a total of 56 different values (columns). These values are different for each record. Based on a time stamp, I need to identify which column I need to use for a value. For example, if the result of my time stamp is Friday 9-12, then I would like to be able to do something like - Value = Table_Name.Column (Friday 9-12), where Friday 9-12 is the results of my time stamp analysis. Is this possible? I am looking for something similar to the Indirect function in Excell. Any help would be much appreciated. |
#4
|
|||
|
|||
On Thu, 7 Apr 2005 13:59:06 -0700, xopher
wrote: My static table is set up as follows: Acct#, Time_Period_1 (Sunday 3-6), Time_Period_2 (Sunday 6-9), etc. 123456, 45, , 60, , etc. 456789 60, , 58, , etc. Sorry... that's not a table. That's a spreadsheet. Storing data (dates) in a fieldname makes it very difficult to create appropriate queries. That's why you're having trouble: your table design is not normalized; queries work best with normalized tables. Any chance you could normalize this into a tall-thin table with three fields, AcctNo (don't use # in fieldnames), TimePeriod, and Amount? This would make your query vastly easier. John W. Vinson[MVP] |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
unable to repair inobox | Sudheer Mumbai | General Discussion | 1 | February 20th, 2005 11:55 AM |
LookUp Values That Depend on Previous Column | Simula67 | Database Design | 3 | January 21st, 2005 09:09 PM |
How can I sort an entire spreadsheet from a list | prod sorter | Worksheet Functions | 4 | November 17th, 2004 03:43 AM |
Printing 2 column vakues from a Lookup Table | iccohen | Setting Up & Running Reports | 1 | August 12th, 2004 03:35 AM |
Make a field lookup dependent on the value in another field of a record? | Susan A | Database Design | 8 | May 22nd, 2004 09:10 PM |