"dynamic" column lookup

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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
 
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.
Click to expand...

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]
 
Back
Top