Solved

Retriving different columns in a query dependent on criteria

Posted on 2008-10-06
9
198 Views
Last Modified: 2013-11-29
Hi, I am quite new at building Access and still have a spreadsheet mindset.  I have 3 tables:
One with various products and their details
One with order dates
One which lists these products and their prices at different times (i.e. the first column has product name, the second has price in say November 2007, the third the price in February 2008, the fourth the price in July 2008).  
In a query I need to be able to specify that the correct price is retrived according to the order date.  In Excel I would probably use some kind of Offset and Vlookup comand, but I don't know how to approach this in Access - thanks
0
Comment
Question by:claude1
  • 5
  • 4
9 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 22647845
If we are dealing with pricing on orders then I start from a position of factual information overriding any niceties of database design.  The price is the price that was actually charged on the the order and should be recorded.
Lookup tables for pricing have their place in some situations, but only going forward, not retrospectively, in my view.
So although you might use a rule to look up the price on a new order - maybe based on delivery date for example,or some other criteria, once you have worked it out it should be held on the order record.

Therefore, my view is that you shouldn't be doing what you are proposing, for historical prices.



 
0
 

Author Comment

by:claude1
ID: 22647978
Hi Peter,
Thanks for getting back to me with that.  You are right, however I think I did not frame the question properly.  We are indeed bringing in the historical prices, but what I need to look up is the cost of the product to us (i.e. so I can work out the profit).  The cost to us changed over time (i.e. a the same product might cost x in one month and y in another) so I need to be looking these costs up.  Sorry for not making this clear in the question
0
 
LVL 77

Expert Comment

by:peter57r
ID: 22648138
"the second has price in say November 2007, the third the price in February 2008, the fourth the price in July 2008)"

So are these always (the prices for) these dates?  Otherwise how do indicate which dates apply to each price?
0
 

Author Comment

by:claude1
ID: 22648352
Hi Peter,
I want to be able to have a column for each time there is a change in the cost of the product.  I.e. if the cost of the product went up in July 2008, I want to have a column called July 2008 with all the products listed with their costs at this time.  If there was a cost rise in September 2008, I want to add another column with the prices at this time.  So for all goods shipped where the shipped date in my "Orders" table is July and August 2008, the costs are bought in from the July 2008 cost column and for all goods shipped in September onwards, the costs are bought in from the September column
Thanks very much for your time with this


 
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 77

Accepted Solution

by:
peter57r earned 125 total points
ID: 22648606
But surely different products will change cost prices at different times- and surely they don't all change on the 1st of the month?
0
 

Author Comment

by:claude1
ID: 22648989
Hi Peter
In this case, costs are only updated on the first of the month - there are not likely to be more than a couple of changes per year and these changes will capture all the price updates from our supplier.
So really what I am trying to get the system to do is say if order date is between date of heading 2 and heading 3, use the cost from heading 2.  It could be that I am thinking about this wrongly and there is a better way to approach the problem?
Thanks
0
 

Author Comment

by:claude1
ID: 22649860
Hi Peter
I have been thinking about this the wrong way and believe that if I change the structure of the costs table to have headings Item, Cost, Date it will sort it
Thanks for your help
0
 

Author Closing Comment

by:claude1
ID: 31503409
Hi Peter
Thanks for your help with this - I don't think I framed the question very well, but think I know how I need to structure table
Thanks
0
 
LVL 77

Expert Comment

by:peter57r
ID: 22650218
I suggest you use a FromDate and  ToDate in your table to make life a lot easier.
It is possible to work  it with just a From date but it's just painful to do so.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

914 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

18 Experts available now in Live!

Get 1:1 Help Now