Solved

Retriving different columns in a query dependent on criteria

Posted on 2008-10-06
9
197 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

746 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

14 Experts available now in Live!

Get 1:1 Help Now