Linking Two Fields And Bring Current To The Top

[TBC]

Hello All,
I have a query that does a great job at fining duplicate master records

I'm hoping someone can help me add to this query so that it fining duplicate master records like the query show and add one more record that will link up as shown in the example.
If you need any more information or anything I can help with please don’t hesitate to reach out.



[sql]SELECT subqry2.maxdatereferral
,[PSB Accout Linkingqry].master
,[PSB Accout Linkingqry].RecordNumber
,[PSB Accout Linkingqry].FirstName
,[PSB Accout Linkingqry].LastName
,[PSB Accout Linkingqry].StAddress1
,[PSB Accout Linkingqry].City
,[PSB Accout Linkingqry].Remark
,[PSB Accout Linkingqry].Phone
,[PSB Accout Linkingqry].Employment
,[PSB Accout Linkingqry].ClientNumber
,[PSB Accout Linkingqry].CollectorNumber
,[PSB Accout Linkingqry].TEXT1
,[PSB Accout Linkingqry].dateofreferral
,[PSB Accout Linkingqry].StatusCode
,[PSB Accout Linkingqry].PrincipalBalance
FROM [PSB Accout Linkingqry]
INNER JOIN (
SELECT [PSB Accout Linkingqry].master
,max([PSB Accout Linkingqry].dateofreferral) AS maxdatereferral
FROM [PSB Accout Linkingqry]
INNER JOIN (
SELECT master
,dateofreferral
FROM [PSB Accout Linkingqry]
WHERE master IN (
SELECT master
FROM [PSB Accout Linkingqry]
GROUP BY master
HAVING count(*) > 1
)
ORDER BY dateofreferral DESC
) AS subqry1 ON [PSB Accout Linkingqry].master = subqry1.master
GROUP BY [PSB Accout Linkingqry].master
) AS subqry2 ON [PSB Accout Linkingqry].master = subqry2.master
ORDER BY subqry2.maxdatereferral DESC
,[PSB Accout Linkingqry].master
,[PSB Accout Linkingqry].dateofreferral DESC; [/sql]

The below query links the duplicate master records and the TEXT1. Now I just need the top one to and bottom one to work together

[sql]SELECT [New Accout Linkingqry].Master
,[New Accout Linkingqry].TEXT1
,[New Accout Linkingqry].RecordNumber
,[New Accout Linkingqry].FirstName
,[New Accout Linkingqry].LastName
,[New Accout Linkingqry].StAddress1
,[New Accout Linkingqry].City
,[New Accout Linkingqry].Remark
,[New Accout Linkingqry].Phone
,[New Accout Linkingqry].Employment
,[New Accout Linkingqry].ClientNumber
,[New Accout Linkingqry].CollectorNumber
,[New Accout Linkingqry].DateOfReferral
,[New Accout Linkingqry].StatusCode
,[New Accout Linkingqry].PrincipalBalance
FROM [New Accout Linkingqry]
WHERE (
(
([New Accout Linkingqry].Master) IN (
SELECT [Master]
FROM [New Accout Linkingqry] AS Tmp
GROUP BY [Master]
,[TEXT1]
HAVING Count(*) > 1
AND [TEXT1] = [New Accout Linkingqry].[TEXT1]
)
)
)
ORDER BY [New Accout Linkingqry].Master
,[New Accout Linkingqry].TEXT1; [/sql]

 

[vbaInet]

Can you please re-format the SQL and use code tags:

http://www.access-programmers.co.uk/forums/showthread.php?t=240420

And expand on what you're trying to achieve.

 

[jdraw]

If you need any more information or anything I can help with please don’t hesitate to reach out.

What are you trying to do ? Showing code/sql doesn't explain WHAT, it shows us HOW you have done something. But we need to understand that something before offering focused suggestions.

Why would you have duplicate master records?