All Records from Table A and Only Most Recent from Table B 1

[djs45uk]

I have two tables which are related in the following way:

http://www.danielsmith.me.uk/structure.jpg

I am trying to create a query that returns all results from the Hardware table and the single most recent related records from the Loan table.

Here is what I've tried:

SELECT Hardware.hardwareOwner, Hardware.hardwareSerialNo, Hardware.hardwareID, Loan.loanTimeDate, Loan.loanUserID
FROM Hardware LEFT JOIN Loan ON Hardware.hardwareID = Loan.loanHardwareID
WHERE (((Hardware.hardwareOwner) Like 'TSPARE*' Or (Hardware.hardwareOwner) Like 'RSPARE*'))
AND (SELECT Max(Loan.loanTimeDate) AS MaxOfloanTimeDate FROM Loan;)
ORDER BY Hardware.hardwareOwner;

Now the above code returns all records from the Hardware table and all records from the Loans table. But again I need all records from Hardware and only the most recent related records from Loans (which is why I'm using Max date).

I really hope this makes sense - I'll be truely very grateful for anyone can help.

Many thanks

daniel

 

[lespaul]

Maybe something like:

SELECT H.Hardwareowner, H.hardwareSerialNo, H.hardwareID, L.loanTimeDate, L.loanUserID FROM Hardware
INNER JOIN (SELECT loanUserID, loanHardwareID, Max(loanTimeDate) FROM Loan GROUP BY loanUserID, loanHardwareID) As L on H.HardwareID = L.HardwareID
WHERE H.HardwareOwner like 'TSPARE*' or H.HardwareOwner Like 'RSPARE*'
Order by H.HardwareOwner



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[djs45uk]

Thank you for your help.

Unfortunately the above returns a syntax error on JOIN message.

SELECT H.Hardwareowner, H.hardwareSerialNo, H.hardwareID, L.loanTimeDate, L.loanUserID FROM Hardware 
INNER JOIN (SELECT loanUserID, loanHardwareID, Max(loanTimeDate) FROM Loan GROUP BY loanUserID, loanHardwareID) As L on H.HardwareID = L.loanHardwareID
WHERE H.HardwareOwner like 'TSPARE*' or H.HardwareOwner Like 'RSPARE*'
Order by H.HardwareOwner;

I have tried not using an INNER join to see if that makes any difference but it doesn't. I also tried removing the GROUP BY clause to see if that made a difference but no.

Thank you for your help all the same.

 

[lespaul]

what version of access? if before 2000 you'll have to use two queries like this:

qry1:
SELECT loanUserID, loanHardwareID, Max(loanTimeDate) FROM Loan GROUP BY loanUserID, loanHardwareID

qry2:
SELECT H.Hardwareowner, H.hardwareSerialNo, H.hardwareID, L.loanTimeDate, L.loanUserID FROM Hardware
INNER JOIN qry1 As L on H.HardwareID = L.loanHardwareID
WHERE H.HardwareOwner like 'TSPARE*' or H.HardwareOwner Like 'RSPARE*'
Order by H.HardwareOwner;

 

[PHV]

Create a query named, say, qryRecentLoan:
SELECT loanHardwareID, Max(loanTimeDate) AS mostRecent
FROM Loan
GROUP BY loanHardwareID

And now your query:
SELECT H.hardwareOwner, H.hardwareSerialNo, H.hardwareID, L.loanTimeDate, L.loanUserID
FROM (Hardware AS H
INNER JOIN Loan AS L ON H.hardwareID = L.loanHardwareID)
INNER JOIN qryRecentLoan AS R ON L.loanHardwareID = R.loanHardwareID AND L.loanTimeDate = R.mostRecent
WHERE H.hardwareOwner Like '[RT]SPARE*'
ORDER BY 1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[djs45uk]

Thank you again lespaul!

I'm using Access 2003 but tried doing it in two separate queries just in case. I get the same error message (syntax error in JOIN).

I do appreciate your help though - I'll fiddle with it some more and see if I can't stumble across a solution.

Many thanks

Daniel

 

[djs45uk]

Thank you PHV -- your solution works brilliantly. I am very very grateful.

Many thanks again

Daniel

 

[PHV]

I'm using Access 2003
So, a single query is possible:
SELECT H.hardwareOwner, H.hardwareSerialNo, H.hardwareID, L.loanTimeDate, L.loanUserID
FROM (Hardware AS H
INNER JOIN Loan AS L ON H.hardwareID = L.loanHardwareID)
INNER JOIN (
SELECT loanHardwareID, Max(loanTimeDate) AS mostRecent FROM Loan GROUP BY loanHardwareID
) AS R ON L.loanHardwareID = R.loanHardwareID AND L.loanTimeDate = R.mostRecent
WHERE H.hardwareOwner Like '[RT]SPARE*'
ORDER BY 1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886