Pulling data from one column based on the data from another column (1 Viewer)

robruhr

Registered User.
Local time
Today, 08:49
Joined
Mar 2, 2009
Messages
11
So, I'm pretty new to Access, and have been teaching myself as I go along. I am also new to forums to I apologize if I don't explain myself well.
I am trying to create a query that will bring up a specific price of a specific style based on the qty on the order. Eventually, I'd like to create a form where someone can enter the style number and the qty on the order, and then it would bring up the price. The table is currently layed out as such:

Style -- Desc -- Price_1-99 -- Price_100-999 -- Price_1000-4999 -- Price_5000-14999 -- Price_15000-19999 -- Price_20000+
AFR701 -- SS Tee -- $11.10 -- $5.90 -- $5.55 -- $5.43 -- $5.25 -- $5.06
AFR801 -- short -- $13.60 -- $7.20 -- $6.80 -- $6.57 -- $6.35 -- $6.11
AFR201 -- pants -- $11.60 -- $6.20 -- $5.80 -- $5.66 -- $5.46 -- $5.27

What I would like is if the order is 15 units, for style AFR701, for the query to bring up the $11.10 price. Or, if the order is 155000, to bring up the $5.25 price. Is this possible? Thank you.
 

mcalex

Registered User.
Local time
Today, 23:49
Joined
Jun 18, 2009
Messages
141
It's possible, but a bit tricky.

imo, you want tables on the styles, the order groups and the prices.

Style table info would include the code & description.
OrderGroup table info would include columns for group names (Price_1000-4999 etc) and the max and min amounts for that group (1000 and 4999 respectively in the group i mentioned).
Price table info would include a reference to the style, a reference to the order group, and the price for that style when an amount in that order group is ordered.

Then your query searches the Price table to find where the style and the amount match the info you are looking for.

hope this makes sense and helps.

mcalex
 

robruhr

Registered User.
Local time
Today, 08:49
Joined
Mar 2, 2009
Messages
11
Thanks mcalex, for such a fast reply. but, I'm sorry, I'm not following you on the OrderGroup table. I'm attaching a spreadsheet to show what I think you mean. Please let me know if I am way off. Thanks again for your help.:)
 

Attachments

  • Prices.xls
    22 KB · Views: 110

mcalex

Registered User.
Local time
Today, 23:49
Joined
Jun 18, 2009
Messages
141
hi robruhr,

cheers ;-)

I've reattached the spreadsheet with how i see the ordergroup & prices tables

see if that makes sense, fill in the rest of the data, and then try to get your query going.

mcalex

PS: I've added keys (the ID column) to show where one table points to another. You need keys to link the tables. To make life easy, I've chosen to add an artificial key to all tables. Other options for instance could be to use the Style code (which looks to be unique for all style records), or a multiple-value key (eg in the attached prices sheet the key could be a combination of the styleid and the ordergroupid)
 

Attachments

  • Prices.xls
    31 KB · Views: 137

robruhr

Registered User.
Local time
Today, 08:49
Joined
Mar 2, 2009
Messages
11
mcalex, thank you so much. This worked perfectly. I was able to create a couple qrys and link them, to create another qry that will have it ask the style and the qty, and then it will bring up the price associated with the qty based on the range. So, thanks again!!
 

Users who are viewing this thread

Top Bottom