Link to home
Start Free TrialLog in
Avatar of Sivasan
SivasanFlag for United States of America

asked on

Picking the value of a field based on it's field name in access query

Hi There,
I have a table in access that has orders for the 52 weeks. I like to find the orders for current and the current + next 10 weeks.
so Orderstbls has

Item  Wk1  Wk2  Wk3  Wk4 Wk5 Wk6 Wk7 Wk8 Wk9 Wk10 Wk11 Wk12 Wk13 ... Wk53
123    10     100   30     20    10    2       3     7       9      10     3           7      22           100
223    11     200   10     22    17   42      4     8       19     11    13         33      22         600

I want to do a query it checks for current week Format(Date(),"ww") and then based on it pull the orders for current and next 10 weeks. If I do a check by using if statement  iif( Format(Date(),"ww") =1,WK1, Format(Date(),"ww") =2,WK2 so on for each of the 11 weeks I want to pull, the query becomes too complex to compute, access is unable to do it throwing an error too complex, because of so many iff statements.
Is there any other method I can use the field name as it has the week indicated on it.
I want the query result assume query name OrderforWeeks

Item  CU    Cu1     CU2  CU3  CU4  CU5  CU6  CU7  CU8  CU9 CU10
123   30      20        10     30    40     70    89     77     100   20    100
123   20      40        11     33    41     17    8      27     10    200    100

the CU is for current week, which would be the 47th week, CU1 would be for the 48.. so on, if it exceed 53rd, I take the one form WK1 which is actually the data for next year.
Any idea how I can do this without having to do iif( Format(Date(),"ww") =1,WK1, Format(Date(),"ww") =2,WK2 so on for each of the 11 weeks.
thanks
ASKER CERTIFIED SOLUTION
Avatar of aikimark
aikimark
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
With the modulo function this should resemble this
DLookup("Wk" & (((Format(Date(),"ww")  mod 53)+1)+3), "Orderstbls", "Item=123) As CU3

Open in new window

Avatar of Sivasan

ASKER

Hi Aikimark,
Thank you but why give "Item=123" ? I just gave an example of Item value to be 123, there are so many items.
I'm not an expert, if you wouldn't mind, can you explain it further.
thanks
If you have a field named Item, you would normally use it in place of the 123 literal.  Since both of your example lines were 123, I thought it would be simpler for you to understand.
Avatar of Sivasan

ASKER

Sorry my bad was a typo, it should have been something like this

Item  CU    Cu1     CU2  CU3  CU4  CU5  CU6  CU7  CU8  CU9 CU10
123   30      20        10     30    40     70    89     77     100   20    100
223   20      40        11     33    41     17    8      27     10    200    100
abc   21      10        12     23    41     11    80     127   20   100    500
.
..
..
other items
I tried DLookup("Wk" & Format(Date(),"ww"), "Orderstbls", "Item=item") As CU  ( to test I'm just trying the first column)
I get the value of the first item's 47 week order for all the items in the result
so i get
item  CU    
123   30      
223   30      
abc   30  
ccc    30
AbD   30
...       30
...       30

30 being the Order for Wk 47 for item 123. Am I doing something wrong?
thanks
Avatar of Sivasan

ASKER

Thanks a million, I worked out the solution
I assume you are using
"Item=" & item

Open in new window