View Single Post
  #3  
Old April 7th, 2005, 09:59 PM
xopher
external usenet poster
 
Posts: n/a
Default

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.