trying to get the query right

[needinghelp]

This is being added to a dabase I am working on kind of as an after though. I live in the US and am working for a political lobbyist. The database contains a table that has lots of personal information on our clients, such as name address and phone number. What we would like to accomplish is to be able to enter the persons county and of residence and find out their State Senator and State Legislature. Because the Senate and Legislative districts that devide the state don't follow county lines but are instead based on population it is possible for one county to have multiple senate or legislature districts or for one senate or legislative distirct to inclue multiple counties. Note that the district lines are also not the same for the senate and legislature. For this purpose I have created five table with following fields:

Counties
--------
County

Senate Districts
----------------
County
District

Senate
-------
District
Senator ID

Legislative Districts
---------------------
County
District

Legislature
-----------
District
Legilatiure ID

The county field in the Counties table relates to the County fields in the Senate District and Legistative District Tables. The District Fields also relate to each other within the appropriate branch. The County field in the Counties table also relates to the County Field in my Personal Data table mentioned earlier. The Senator ID and Legislature ID fields relate back to the Personal ID field in the Personal Data Table

Now that I've provided the background information here comes the question part.

a) have I set this up in a way that makes sense or is there a better way to do it.

b) how can I query this setup so that I can query a counry and it will list the Senate District number(s) and appropriat Senator(s) and the Legistalure District number(s) as well as appropriate Legistature(s) without having information repeated. The way I have my query currently set up I get:

Senate Senator Legistlature Legislature
District District
---------------------------------------------
1 Smith 1 Jones
1 Smith 2 Fredrick
2 Adams 1 Jones
2 Adams 1 Fredrick

and so on.

Thanks for any help you can provide.

 

[Abby N]

This is a more difficult problem than it appears at first glance. I played around with a few different ideas and here's my best advice. (Apart from, get out of politics; it’ll suck the life out of you. *wink*) First, you can get rid of the county table. As you already have the counties in the district tables it's redundant. Next, create a query that includes the Senate District table, Senate table and Personal table. Join the fields in the obvious manor. Include the following fields in your query:

[Senate District].[County]
[Senate].[District]
[Personal].[Representative Name]
and a fourth field – Branch: “Senate”

You may also want to add [Enter County] as the criteria for the County field. Save the query. Now, create a new query for the Legislature with a structure like the one above. Go into the Legislature query’s SQL view and copy the entire statement. Go back to the Senate query and go into its SQL view. After the last line type the word UNION and paste the legislative SQL statement. Your final statement will look something like this:

SELECT tblSenateDistricts.County, tblSenateDistricts.District, tblPersonalID.Name, "Senate" AS Branch
FROM (tblSenateDistricts INNER JOIN tblSenate ON tblSenateDistricts.District = tblSenate.District) INNER JOIN tblPersonalID ON tblSenate.SenatorID = tblPersonalID.PersonalID
WHERE (((tblSenateDistricts.County)=[Enter County]));
UNION SELECT tblLegislativeDistricts.County, tblLegislature.District, tblPersonalID.Name, "Legislative" AS Branch
FROM (tblLegislativeDistricts INNER JOIN tblLegislature ON tblLegislativeDistricts.District = tblLegislature.District) INNER JOIN tblPersonalID ON tblLegislature.LegilatiureID = tblPersonalID.PersonalID
WHERE (((tblLegislativeDistricts.County)=[Enter County]));

That should do it. I hope this helps. If you have any problems you know where to find us.

~Abby


[This message has been edited by Abby N (edited 08-23-2001).]