• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 215
  • Last Modified:

Retriving different columns in a query dependent on criteria

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
claude1
Asked:
claude1
  • 5
  • 4
1 Solution
 
peter57rCommented:
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
 
claude1Author Commented:
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
 
peter57rCommented:
"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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
claude1Author Commented:
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
 
peter57rCommented:
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
 
claude1Author Commented:
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
 
claude1Author Commented:
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
 
claude1Author Commented:
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
 
peter57rCommented:
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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now