Solved

Retriving different columns in a query dependent on criteria

Posted on 2008-10-06
9
204 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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

Industry Leaders: 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!

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

691 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