UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Joining 2 Tables Using Dmax, Access 2016    
 
   
Kayleen
post May 15 2019, 06:50 PM
Post#1



Posts: 203
Joined: 22-April 05
From: Qld, Australia


Hi, I am back using Access after many years absence. I wasted 2 hours yesterday unsuccessfully trying to join 2 related tables in a query using all the google help I could find. Thanks for helping....

tblAgents
AgentID
AgentName

tblAgentContacts (as in a communication made with Agent)
ContactID
AgentID
ContactDate
ContactPerson
ContactOutcome


so I need the query to return records from the tblAgents with the LATEST contact information for the Agent, eg. TravelAgentA, Phone, Email, LastContactDate, LastContactPerson, etc.
I can get a query working to bring records from tblAgents and a field DMax(ContactID, tblAgentContacts, AgentID=AgentID) successfully, but then when I try and create the data as above using the DMax field of ContactID = tblAgentsContacts.ContactID I get a data mismatch. They are both long integer, one being an autonumber.

Am I WAYYYYYYYY out of practice and off track? Sorry, thanks heaps for your help.

Kayleen
Go to the top of the page
 
cheekybuddha
post May 15 2019, 07:04 PM
Post#2


UtterAccess VIP
Posts: 11,161
Joined: 6-December 03
From: Telegraph Hill


Joining on a DMax() - I'm not sure I understand!!!

It sounds as if you want something a bit more like:
CODE
SELECT
  a.AgentName
  c.ContactDate,
  c.ContactPerson,
  c.ContactOutcome
FROM (
  (
    SELECT
      AgentID,
      MAX(ContactDate) AS ContactDate,
    FROM tblagentContacts
    GROUP BY AgentID
  ) c2
  INNER JOIN tblAgents a
          ON c.AgentID = a.AgentID
)
INNER JOIN tblagentContacts c
        ON c2.AgentID = c.AgentID
       AND c2.ContactDate = c.ContactDate
;

(Untested and will likely need tweaking!)

--------------------


Regards,

David Marten
Go to the top of the page
 
projecttoday
post May 15 2019, 07:28 PM
Post#3


UtterAccess VIP
Posts: 10,730
Joined: 10-February 04
From: South Charleston, WV


I notice in your Dmax that you are maxing on the ID (autonumber) field. This is not recommended. The ID field was not intended for sequencing but rather identifying. This is for your information.

--------------------
Robert Crouser
Go to the top of the page
 
Kayleen
post May 16 2019, 02:59 AM
Post#4



Posts: 203
Joined: 22-April 05
From: Qld, Australia



Awesome, exactly like that! Thank you so much for helping me remember stuff !
Kayleen
Go to the top of the page
 
BruceM
post May 16 2019, 07:24 AM
Post#5


UtterAccess VIP
Posts: 7,881
Joined: 24-May 10
From: Downeast Maine


To add a little more detail to David's suggestion, there is a good article about subqueries here
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    21st May 2019 - 03:42 AM