Get Value From Table

[Steve1001]

Hi

I have a table of carrier tarrifs

Wt. A B C
0.5 £12.50 £15.00 £14.00
1.0 £15.00 £18.50 £16.50
1.5 £16.00 £19.85 £17.90
2.0 £17.00 £21.20 £19.30
2.5 £18.00 £22.55 £20.70
3.0 £19.00 £23.90 £21.90
3.5 £20.00 £25.25 £23.10
4.0 £21.00 £26.60 £24.30 etc

wt being the weight, and A B C etc the delivery area

A Customer data tbl holds the relivant taffif area. When I print an invoice, I would like the correct carriage figure to be taken from the tbl, if there was only one area this would be easy, but I can't find away with multiple areas. Has any one any ideas ??

Thanks

Steve

 

[DrSimon]

Steve
I know your table looks nice but you'd do much better if you changed the design to:
Wt Tariff_Area Tariff
0.5 A £12.50
0.5 B £15
etc.

Now you just have to select WHERE Wt='?' AND Tariff_Area='?'

The main trouble with tools like Access is that is makes things so easy that you don't always need to know any theory behind it. If you have more problems like this, get a basic book on Database design and look up 'normalisation'.

Simon Rouse

 

[Steve1001]

Simon

Thanks, I know that would work, but the tarrif table if many column and rows and would take a lot of work to re format...or is there a way Access can do this???

Stephen

 

[Craig0201]

thread181-192373

 

[DrSimon]

I'm not sure how adept you are with Access. The easiest (slowest) way would be to do it all by hand. If you are happy writing queries I'd do it in multiple stages.

Create a new table using a Make Table query for Tariff 'A' with fields:
Wt; Tariff_Area: "A"; Tariff:A

Then use append queries for the rest, one at a time e.g.:
Wt; Tariff_Area: "B"; Tariff:B

There may be better ways, but this is simple and it should work. The obvious comment is the old joke - 'if I was going there I wouldn't have started from here'

Simon