Advanced Query

[Lamb2087]

Three tables: tblPeople, tblApplicants and tblLegislator.

tblPeople
PeopleCodeId LastName FirstName
1000 Smith Harry
2000 Sooner Amber
3000 Fillpot John
5555 Harry Phil
6666 Stevens Melissa
7777 Williamson Barry


tblApplicant
PeopleCodeID SenateID HouseID
1000 SCHS33 SCHH40
2000 SCHS22 SCHH50
3000 SCHS11 SCHH42


tblLegislator
PeopleCodeID LegislatorID
5555 SCHS33
6666 SCHH50
7777 SCHS11

Now we want to get a list of all the applicant to each legislator:

Applicant's PeopleCodeID
Applicant's Firstname & Lastname
Applicant's SenateID
Legislator's PeopleCodeID (where the LegislatorID matches the SenateID)
Legislator's Firstname & Lastname

 

[Tim L]

I may not have understood the problem but it seems to me that there should be a relationship between tblPeople and tblLegislator and between tblApplicant and tblLegislator.

It would also appear to me that perhaps there could be fields in tblPeople for the LegislatorID and HouseID, linked (with Referential Integrity and Update enforced) to tblLegislator (and tblHouse, if there is one).

However you don't have to muck around with the database relationships table if you don't want to, you can create relationships within queries too.

For the output I'd use a report that Groups the output on the legislator.

Just point, is the LegislatorID for 6666 in tblLegislator correct? It would appear from the other two entries that it should be SCHS33

HTH

Tim

 

[Lamb2087]

I currently have a query between tblPeople and tblApplicant that works just fine. Also a query between tbl People and tbl Legislator and it works just fine. My problem is being able to llist the Legislators and all the applicants under them based on the House_ID or Senate_ID in the tble Applicant and the Legislator_ID in tbl Legislator.

I have messed around with this for three days without much luck so I need some assistance.

 

[Lamb2087]

The tble People is large enough and I do not want to add more fields to it like you have shown.

The query should be able to get done either through using tables or by combining queries.

 

[EMP]

Now we want to get a list of all the applicant to each legislator:

Applicant's PeopleCodeID
Applicant's Firstname & Lastname
Applicant's SenateID
Legislator's PeopleCodeID (where the LegislatorID matches the SenateID)
Legislator's Firstname & Lastname

Try the query "qryApplicantLegislator" in the attached database.

In your sample data, SenateID SCHS22 does not have a matching LegislatorID while LegislatorID SCHH50 does not have a matching SenateID, so when the query is run, only two matching records will be returned.

 

[Lamb2087]

EMP:
Your query worked like it should, the only problem is that the First_Name and Last_Name are located in a separate table( tblPeople). In the tblApplicant you have People_Code_ID, Senate_ID, House_ID. IN tblLegislator you have People_Code_ID, Legislator_ID, Legislator_Title. So in order to get the frist and last names of the people you need to join some how to People to get that probably with People_Code_ID since all three tables have this as a field. It is not as easy as it might seem since I have been fighting this for three days.

 

[EMP]

I may have misunderstood what you actually required.

When the query is run, it returns these two records:-
[Applicant PeopleCodeID]/[Applicant FirstName]/[Applicant LastName]/[Applicant SenateID]/[Legislator PeopleCodeID]/[Legislator FirstName]/[Legislator LastName]

1000 / Harry / Smith / SCHS33 / 5555 / Phil / Harry
3000 / John / Fillpot / SCHS11 / 7777 / Barry / Williamson

Based on your sample data, how would you like the records to show?

 

[Lamb2087]

What you sent is what I want returned. The thing is that the first name and last name are in the People table not in the legislator or applicant table.

 

[EMP]

The applicants' names and legislators' names are respectively drawn from the queries "qryApplicant" and "qryLegislator".

 

[Lamb2087]

I tell you waht!
I have almost got this to work. Here is what I have:

(Query 1) I have one query the uses tblPeople and tblApplicant to bring back the People_Code_ID, First_Name, Last_Name, Senate_ID, House_ID.

(Query 2) I have another query that uses tblPeople and tblLegislator to bring back People_Code_ID, First_Name, Last_Name, Legislator_ID, Legislative_Title.

I have created a query that uses tblApplicant and tblLegislator that brings back applicants and legislators that match on Senate_ID and House_ID.
I have joined query 1 to People_Code_ID to tblApplicant and have joined query 2 to People_Code_ID to tblLegislator.

It brings back all the info I want, but need some to look at what I have to fine tune it.

My goal is to list all the legislators and the applicants in their districts.

 

[EMP]

Brilliant! It took you only three days to come to that!

 

[Lamb2087]

Yes the name are drwn from the queires you mentied sine in those queries they are joined to the People Table that contains the names.

Di you see my last post that said I have almost got this to work?

 

[Lamb2087]

It should not have taken me this long. Do you know enough to look at what I have so far and fine tune it to make it work?