Lookups, a beginner's question

[al183]

A beginner here.

I'm not sure how to explain this and there is probably a simple answer that I have overlooked.

I've tried to search for the answer using various key words, but to no avail.

I have a table with multiple prices for the same item and I want to grab the appropriate result using a code entered elsewhere on the form.

eg.:

PriceCode Price1 Price2 Price3

176 50.00 60.00 70.00

On input form, before entering the PriceCode, one enters a PriceScale (say 1 to 3).

So if the PriceScale is 2, then entering PriceCode 176 retrieves 60.00

A push in the right direction please??

[Vaughan]

You need to store the prices in a separate table: one price per record, so there will be separate records for the three prices. A "PriceScale" field identifies the three records from each other.

Create a relationship to the price table based on the product code, and the price scale.

[al183]

Thanx.

Considering the number of items I have, it would then be easier for me to have a separate table for each Price Scale.

Is it possible to have each PriceScale in a separate table?

If so, how to make PriceCode + PriceScale point to the appropriate table.

I note you are also in Sydney. Is there a local user group?

[al183]

Also, I note that at the top of this forum page it says:

Note: This forum is for you to post your articles, tips, tricks & techniques. Please don't use this form for requests for "how to..." or help requests.

So where do how to's and help requests go ??

[Vaughan]

"Considering the number of items I have, it would then be easier for me to have a separate table for each Price Scale."

No no no. It might be easier for you now, but it will make it very difficult for you later on to pull in the price info: instead of looking in one place you'll need to look in three (or more).

I assume that somewhere in the sales record there will be a field for the price scale: if not, there needs to be one. Select a price scale; select a product code: bingo, ONE record from the prices table will match this combination. Easy innit!

Imagine trying to do this with multiple price tables...

I did photography years ago, a serious 4 year course at TAFE. A friend of mine lived in Petersham which was then a bit dodgy*. To minimise the loss from theft, he used to keep his expensive and extensive camera equipment hidden all around his flat: a body under the bed, lenses inside socks at the bottom of drawers, tripods in cupboards. Every time he went out It took him about 45 minutes to find everything! And then there was the day he couldn't remember where he'd left his wide angle lens... ROTFLMAO.

Putting prices in multiple table would be a bit like that.

* Petersham might STILL be dodgy for all I know. :

[Vaughan]

I just moved the topic to a more appropriate forum...

[al183]

Hey .. I used to know a guy who lived in Petersham .... he moved to Queensland ... nuff said

Thanks for th guidance; I think I've worked out how I'm going to approach it now.