Another Relationship question

[Dave Sandburg]

This may seem like a simple question, but to me it's mystifiing: I'm creating a DB solution that will display a list of counties and their appropriate sales tax rate. On one layout the user will enter all the counties he makes sales into along with each county's tax rate. I want these counties to be added to a value list so the user can select the appropriate county when making a sale on another layout. When the county is selected I'd like to have the appropriate tax rate for that county automatically compute the tax.

I've added a second table to hold the counties and tax rates but that seems awkward because the user has to create a new record for each county. Is there a better way to do this? If not, how do I get the counties into a Value List on the original table?

Thanks for your suggestions.

[Inky Phil]

Hello Dave

I think that you should keep the second table because that is where you will store the tax rate.

When you create or edit your value list you can opt to use values from a field so in your case your value list should use values from the county field in the second table.

You should format a field in your first table to use this value list.

You should also then establish a relationship between the first table and the second table based on county name=county name.

Then once you have chosen a county in the first table you will be able to calculate the tax due at the appropriate rate because the two tables will be linked by county name.

The only other thing you have to do is a routine to add a county to the second table if it isn't available in the value list

HTH

Phil

[comment]

I believe I would lookup the rate into a field in sales, before calculating the tax. Tax rates can change, and past sales should be protected from such changes.

[Dave Sandburg]

Thanks Phil, but I have another question: In the County table I have two fields, one for county and one for the tax rate. So it appears that I have to create a new record for each county, correct? Or is there another way to do it? Next, you said that the only other thing I'd have to do is a "routine to add a County to the second table if it isn't available in the value list". I've lost you on that one. Can you clarify this?

[Inky Phil]

Dave

1/ Yes you will have to have one record for each county.

2/ With regard to the routine to add a county I had assumed that each user would build up their own file of counties. If you distribute your solution with all the counties and their appropriate tax rates already entered then of course no entry routine is necessary.

HTH

Phil