Solved

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

Posted on 2014-11-19
7
221 Views
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.
thanks
0
Comment
Question by:Sivasan
  • 4
  • 3
7 Comments
 
LVL 45

Accepted Solution

by:
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.
0
 
LVL 45

Expert Comment

by:aikimark
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

0
 

Author Comment

by:Sivasan
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.
thanks
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 45

Expert Comment

by:aikimark
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.
0
 

Author Comment

by:Sivasan
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?
thanks
0
 

Author Comment

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

Expert Comment

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

Open in new window

0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

747 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

13 Experts available now in Live!

Get 1:1 Help Now