Retrieving data from a table

[sharon attwood]

I put this on the tables forum but my answers have now stopped, can anyone here help me with how I get this information to appear on a form....

I have created an expense database but I now want to try to add fields to the main form which will allow the users to select their car engine capacity and the price they paid per litre to establish how much VAT can be reclaimed. A small extract from the table from customs & excise is set out as below. There are 5 engine capacity headers and numerous pence per litre rows.

Pence per litre ....Up to 1000cc.......1001 to 1500cc
75.0 ..................8.525.................9.653
75.2 ..................8.550.................9.682
75.5...................8.575.................9.710
75.7...................8.600.................9.738
75.9...................8.625.................9.767
76.1...................9.650.................9.795

So if someone had a car with the engine size between 1001 to 1500cc and had paid 75.7p per litre for their fuel we could reclaim VAT @ 9.738p per mile.

Is there anyway I can get access to look up this information for me?

...................................................................................................
Ans:

It would be nice if you could find out the formulae for working out the pence per mile bit based on the cc.

Either way, I think your table needs changing to this design

Price. . . . CC. . . . . . . . . . . . . VAT
75.0 . . . .1000. . . . . . . . . . . .8.525
75.0 . . . .1000 to 1500. . . . . . .9.653
etc
etc

Col
__________________
This ain't no technological breakdown. . .oh no, this is the road to hell.

...................................................................................................

Thanks,

Unfortunately I can't get hold of a formula. I'm still not sure how I would look up a value, even if I changed the table as you suggested. The user would need to select a cc size and then a price per litre which would then need to be cross referenced to give a value. I could set up different tables for each engine size, but then I'm not sure how I could point the answer at the correct table.
I don't even know if what I am trying to do is possible in access.

.....................................................................................................

Sharon, you could have the price per litre in one ComboBox and the engine size in another. Both these would then be the criteria for a query.

Can you post your Db and I'll have a look for you, failing that I could knock one up (in Access97)

Col
__________________
This ain't no technological breakdown. . .oh no, this is the road to hell.

....................................................................................................

I think I've attached the file, but I've never done this before so it might not be there!

The table I'm trying to create is called pence per litre but it is completely stand alone at the moment until I can work out how to get any information out of it. I have changed the table to your suggested layout but have only entered a few records, there are hundreds to be entered if it can be made to work!

Thanks

Sharon

..................................................................................................

I can't seem to get the file to attach it doesn't seem the right extention code it's an mdb

 

[Ukraine82]

Sharon,

I suggest you attach your file to winzip. Also, it appears that you might need to make a cascading combobox along with IIF and Switch Functions.

hth,
Michael

 

[sharon attwood]

Unfortuantely the zipped file is too big to attach. I've tried deleting most of the data but it's still bigger than 300kb.

I have since tried adding a button to bring up a form relating to a query which seemed to work and bring up the information of the table, but I can't use this information. I also tried a sub form but I could not get this to work at all.

Any help greatly appreciated

Thanks

Sharon

 

[ColinEssex]

Unfortuantely the zipped file is too big to attach.

have you compacted it?

Col

 

[sharon attwood]

I used maximum to compact it