Relationships

[ledgerr.rob]

Hello,

I've been working on a project that incorporates Subcontractors with a State, a region (that i define by county) and then by City. My goal is to enter information about a subcontractor, select a state and a city. Then i want to be able to query subcontractors by state/region/city. I'm close on this but I can't seem to figure out a relationship issue.

Currently i have it in my mind that a state will have many subcontractors. That state will have many regions, regions will have many counties, and counties will have many cities.

Now i have the State - Region - County - City figured out, no problem. But when i add the subcontractor into the equation i get many more records than i should ( i think a cross join is happening but i don't know).

I've attached a copy of the DB for what i have so far. To get you where i am looking, open qry1. Running it provides 1118 records. (there should be 2737). Removing tblSubcontractor provides the 2737 records (the total number of cities in the project at this time).

So i tried troubleshooting this.
1- add tblSubcontractor to qry 2 = 3 results. this is correct
2- add tblState to qry 2 = 3 results. This is still correct
3 - add tblRegion to qry 2 = 13 results. Something is wrong here
4- add tblCounty_Imports to qry 2 = 139 results. Problem is growing
5- add tblCity to qry 2 = 1118 results. Same number of results as qry 1 originally provided.

This must be a relationship error with the tblState and tblRegion but i don't know the direction i should go.

Any ideas or direction?

Thank you,
Rob

 

[plog]

INNER JOINS act as criteria. You have 3 contactors, assigned to ID, MT and WA. That's the choke point. Downstream from that you will only get data for those 3 states. If you want to report on every city you have you need to start with that table and LEFT JOIN all other tables to it. However, when you add another contractor that shares a state with an existing one you will then create "dupes".

With all that said, your tables need to be structured better. You said this is the location hierarchy:

City->County->Region->State

First, you need a county table. Second, each location type should only link to the one to its right. City should have only a foreign key to County, County should only have a foreign key to Region, Region should just list which state it belongs to. No need for a state table, you only have 1 piece of real data in there (autonumbers aren't real data) so instead of using a foreign key just use that state postal code.

Also, files like [StateCountyCity] should not exist. No need to store calculated data. You can append all that data in a query and just have it like that without storing it.

Lastly, tblSubcontractor does not need to have a foreign key to every location table. It only needs the foreign key of the lowest level (most likely city) to which they are assigned.

Fix your tables first, then revisit this.

 

[ledgerr.rob]

Thanks plog. I'm cleaning up the tables now. some of that was just some residual messiness from me trying different things (but definitely not all of the garbage).

I think I understand your recommendations. A couple quick questions.

I don't understand your suggestion regarding how to incorporate the zip code in lou of a foreign key.

Also, if i understand this, on my data entry form I could have a combo box for City. Then because of how the relationships will be, the county, region, and state will automatically be 'selected'? Is that correct? If so, the city combo box would have a large amount of records to display.

I'll keep cleaning up my tables.

Regards,
rob

 

[plog]

Not zip code, postal code (MT, OR, WA, etc.). There's no point in having just one real field of data in your state table. So instead of having 5 in the State field of tblRegion, simply store 'WA' there.

I have no idea about your entry form. You need to shore up your tables first, work on reports next, then worry about input forms.