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

Posted on 2014-11-19
Last Modified: 2014-11-19
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.
Question by:Sivasan
  • 4
  • 3
LVL 45

Accepted Solution

aikimark earned 500 total points
ID: 40453407
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.
LVL 45

Expert Comment

ID: 40453446
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


Author Comment

ID: 40453789
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.
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

LVL 45

Expert Comment

ID: 40453795
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.

Author Comment

ID: 40453862
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?

Author Comment

ID: 40454000
Thanks a million, I worked out the solution
LVL 45

Expert Comment

ID: 40454157
I assume you are using
"Item=" & item

Open in new window


Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Sending email from List Data 2 51
Explanation of Access VBA code 2 36
Tags from access to excel 3 28
How do I refer to a session variable in a query? 4 19
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now