Combine 3 tables in query with 1 field updateable? 3

[djugene188]

Sorry to bother, but any help is truly appreciated. I have 3 tables:
tblClient, tblRequests, tblEmail

Relationship of tblClient to tblRequest is 1 to Many
Relationship of tblClient to tblEmail is 1 to 1

tblClient
--------

PersonID (PK) FName
1 Eric
2 Mike
3 Dave
4 Jan

tblRequest
-----------
RequestID(PK) PersonID(FK) RequestType RequestDate
1 1 Catering 04/15/09
2 1 Catering 04/17/09
3 2 Travel 03/09/09
4 2 Hotel 03/09/09
5 2 Travel 04/01/09
6 3 Catering 02/01/09
7 3 Catering 04/08/09

tblEmail
--------
EmailID(PK) PersonID(FK) Email fldsend
1 1 eric@test.com
2 2 mike@test.com
3 3 dave@test.com
4 4 jan@test.com

What I would like to get is the information combined into one form to show only one request type per person and also have the fldsend updateable for each row as needed. I've
tried select distinct to get the RequestType to show only once, but of course the fields aren't updateable.
I've also tried UNION queries and have gotten the same result of the fields not be able to update.

Resulting table should look something like this.(some of the preceeding fields were removed for simplicity sake--all PK & FK would need to be in the result for the fields to be updateable in a continuous form):

PersonID FName RequestType Email fldsend
1 Eric Catering eric@test.com
2 Mike Travel mike@test.com
2 Mike Hotel mike@test.com
3 Dave Catering dave@test.com

In this case, 'Jan' would not be in the resulting query due to the fact that there were no RequestType associated with her record.

It is at this point the user should be able to add notes to the fldsend.
I really appreciate any info that could be provided. Please let me know if I
did not supply enough info.

Djugene

 

[PHV]

You may try something like this:

SELECT *
FROM (tblClient C
INNER JOIN tblRequest R ON C.PersonID = R.PersonID)
INNER JOIN tblEmail E ON C.PersonID = E.PersonID
WHERE R.RequestDate = (SELECT Max(RequestDate) FROM tblRequest WHERE PersonID=R.PersonID AND RequestType=R.RequestType)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[djugene188]

Thank you, PHV.
I will give that a try.
Djugene

 

[djugene188]

PHV,

This almost worked. It returned the records properly, but I was not able to add any info to the fldsend.
It looks like it locks the table once the 2nd join takes place. I tried the code with only the first join and I was able to make changes to the data available.

If there is anything else I can try, please let me know.

Djugene

 

[dhookom]

This might work

SELECT *
FROM (tblClient C
INNER JOIN tblRequest R ON C.PersonID = R.PersonID)
INNER JOIN tblEmail E ON C.PersonID = E.PersonID
WHERE R.RequestDate = DMax("RequestDate", "tblRequest","PersonID=" & R.PersonID & " AND RequestType= """ & R.RequestType & """")

Duane
Hook'D on Access
MS Access MVP

 

[djugene188]

Duane,

Thank you so much for your suggestion. It also pulled the right information, but I am still not able to update the results.

Question ---
When two inner joins are performed, is that supposed to lock the table? Once I took out the 2nd Inner Join on tblEmail, I am able to update the information. Which is exactly the same but with 2 columns removed.

Any thoughts?

Thanks.
Djugene

 

[dhookom]

See what happens after you make PersonID a unique index in tblEmail.

Duane
Hook'D on Access
MS Access MVP

 

[djugene188]

Duane,

Wow --- that did the trick! Wow, I will remember this for future reference for sure. Something so simple...

PHV,

Thank you once again for your help. I just wanted to let you know that I've learned a lot from many of your posts.

Have a great day guys! I can't thank you enough for this...

Djugene

 

[dhookom]

I've also learned a lot from PH ;-)

Duane
Hook'D on Access
MS Access MVP

 

[lespaul]

We've ALL learned alot from PH!!

Leslie

Come join me at New Mexico Linux Fest!