A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

"dynamic" column lookup



 
 
Thread Tools Display Modes
  #1  
Old April 7th, 2005, 03:57 PM
xopher
external usenet poster
 
Posts: n/a
Default "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  
Old April 7th, 2005, 06:36 PM
Michel Walsh
external usenet poster
 
Posts: n/a
Default

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  
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.




  #4  
Old April 8th, 2005, 12:30 AM
John Vinson
external usenet poster
 
Posts: n/a
Default

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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
unable to repair inobox Sudheer Mumbai General Discussion 1 February 20th, 2005 12:55 PM
LookUp Values That Depend on Previous Column Simula67 Database Design 3 January 21st, 2005 10:09 PM
How can I sort an entire spreadsheet from a list prod sorter Worksheet Functions 4 November 17th, 2004 04: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


All times are GMT +1. The time now is 12:25 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.