Solved

Retriving different columns in a query dependent on criteria

Posted on 2008-10-06
9
202 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
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…
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.

726 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