troubleshooting Question

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

Avatar of Sivasan
SivasanFlag for United States of America asked on
Microsoft Access
7 Comments1 Solution256 ViewsLast Modified:
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.
Get vaccinated; Social distance; Wear a mask
Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros