Relationship question

[bnickelsen]

With the following tables

tblStateList
IDStateList (AutoNumber Key)
StateName

tblCountyList
IDCountyList (AutoNumber Key)
CountyName

tblCityList
IDCityList (AutoNumber Key)
CityName

tblZipCodes
tblZipCodes (AutoNumber Key)
Zipcode

What would be the best way to set up relationships?

My First thought...

tblStateList
IDStateList (AutoNumber Key)
StateName

tblCountyList
IDCountyList (AutoNumber Key)
CountyName
fkIDStateList

tblCityList
IDCityList (AutoNumber Key)
CityName
fkIDCountyList

tblZipCodes
tblZipCodes (AutoNumber Key)
Zipcode
fkIDCityList

But I am wondering if I should also tie city and Zipcodes back to the State list as well..

tblStateList
IDStateList (AutoNumber Key)
StateName

tblCountyList
IDCountyList (AutoNumber Key)
CountyName
fkIDStateList

tblCityList
IDCityList (AutoNumber Key)
CityName
fkIDCountyList
fkIDStateList

tblZipCodes
tblZipCodes (AutoNumber Key)
Zipcode
fkIDCityList
fkIDStateList

 

[KenHigg]

Unless you have some un-stated, other wise warranted reasons:

a. These are not all inter-related
b. Zip codes and cities/counties have no inherent relationship other than to overlap one another from time to time.

???

 

[bnickelsen]

Clarification:

I will be building a form that will ask for the State and City.

County Should auto fill in

And a combo box should offer the zip codes in that area.

I will then need to Autofill in other items:
Tax Rates , Distance, Drive Time And Hotel Rates

Tax Rates Depend on State, County and City
Distance and drive time depend on zip code
Hotel rates depend on City (And will have a Seasonal dependence as well, I have not even planed this one yet)

Does that make sense?

 

[KenHigg]

Where are you going to get all this 'Rates' data? I would think a examination of how these are set up would dictate what 'auto fills' you could do...

 

[bnickelsen]

To be honest I have not got to that point yet.

I am sitting on dozens of excel sheets and need to convert them into one big database.

I am in the planning and learning stage at this point.

The data base will be used for the following

1. Sales persons requests for estimates
2 Estimator builds an estimate
3. Sales person build a proposal to give the customer
4. Project manager’s use as a scope of work and to issue change orders and addendums
5. Billing the job.

I need to be able to track what the sales people are requesting and what they sale.

I also need to track what estimators have for a work load, and how much time they spend on different type of estimates.

What I am looking at is a very big task.

So back to this question, I have an excel workbook with a chart that shows State, City, County, tax rates, hotel rates, Travel time and distance.

Each will have its own table.
(Hotel rates are a little more complicated because I need to list the rate based on the month. For the estimating part the highest rate would be used, but for invoicing the actual rate for that month would be used.)

To calculate tax I need to pull the state rate, county rate and the city rate. (This is sales tax) This rate will then need to be adjusted by the type of Project. (Other tables still loaming out there.)

For travel I have a list that shows how many miles from each of our offices and the average time to drive that distance. ( I will also need a table that has our locations listed)

Ok so for estimates I would need all four items State, County, City and Zip.

But for some thing I might only need to show the City and Zip or just the county.

It has also been suggested that we include details that might be specific to a given State, county or City. (That would mean more tables). This might include construction code, ship to cost ect.

I have a ton of work ahead of me; I am just trying to break some things down into smaller tasks.

 

[KenHigg]

So for a final rate, do you simply add state rate, county rate and city rate?

 

[bnickelsen]

For the most part.