Avatar of Sivasan
Sivasan
Flag 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
Microsoft Access

Avatar of undefined
Last Comment
aikimark

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
aikimark

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
aikimark

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

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
aikimark

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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
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
Sivasan

ASKER
Thanks a million, I worked out the solution
aikimark

I assume you are using
"Item=" & item

Open in new window

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.