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
SivasanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aikimarkCommented:
Normalize your data!!!

You wouldn't need to ask this question if your data were normalized.

====================
In your current configuration, create a query with ten DLookup() columns.  You would construct the name of the column, based on the week.
DLookup("Wk" & Format(Date(),"ww"), "Orderstbls", "Item=123") As CU,
DLookup("Wk" & Format(Date(),"ww") +1, "Orderstbls", "Item=123") As CU1,
...
DLookup("Wk" & Format(Date(),"ww") +9, "Orderstbls", "Item=123") As CU10

Open in new window


To wrap the weeks, you will need to use modulo division with all of the Format(Date(),"ww") +# values.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
aikimarkCommented:
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

SivasanAuthor Commented:
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
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

aikimarkCommented:
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.
SivasanAuthor Commented:
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
SivasanAuthor Commented:
Thanks a million, I worked out the solution
aikimarkCommented:
I assume you are using
"Item=" & item

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.