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 912 has one value, Tuesday from 36 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 912, then I would like to be able to do something like  Value = Table_Name.Column (Friday 912), where Friday 912 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. 
Ads 
#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 912 has one value, Tuesday from 36 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 912, then I would like to be able to do something like  Value = Table_Name.Column (Friday 912), where Friday 912 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 36), Time_Period_2 (Sunday 69), 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 912 has one value, Tuesday from 36 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 912, then I would like to be able to do something like  Value = Table_Name.Column (Friday 912), where Friday 912 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 36), Time_Period_2 (Sunday 69), 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 tallthin 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 