Problem innerjoin query in many-to-many relationship

[joho00]

I have the following query:

SELECT tableBackup.BackupDate, tableBackup.Type, tableTape.Serial
FROM tableTape INNER JOIN (tableBackup INNER JOIN tableJoinBackupTape
ON tableBackup.ID = tableJoinBackupTape.BackupID) ON tableTape.ID =
tableJoinBackupTape.TapeID
ORDER BY tableBackup.BackupDate DESC;

This works but what I really want to do is ouput only the first
occurence of tableTape.Serial. That is, I want a list of all my backups
sorted by Date but only show the most recent occurence for each tape
(tableTape.Serial) and filter out the rest.

I have three tables (tableTape, tableBackup, tableJoinBackupTape) in a
many-to-many relationship which is causing all the confusion.

Any help is greatly appreciated.

 

[Roger Carlson]

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "MaxQueryProblem.mdb" which should shed some light on this for
you.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[joho00]

Roger,

Based on your examples, I was able to get this working. Thank you very
much!

Oddly enough, I wasn't able to get the second example you provided
(subquery) to work. I think the double innerjoins were the root of the
problem but I'm not positive. I've included the resulting query below
for your reference.

SELECT tableBackup.BackupDate AS [Backup Date], tableBackup.Type,
tableTape.Serial, tableBackup.Status
FROM tableTape INNER JOIN (tableBackup INNER JOIN tableJoinBackupTape
ON tableBackup.ID = tableJoinBackupTape.BackupID) ON tableTape.ID =
tableJoinBackupTape.TapeID
WHERE (((tableBackup.BackupDate) In (SELECT Max(tableBackup.BackupDate)
AS BackupDate FROM tableTape INNER JOIN (tableBackup INNER JOIN
tableJoinBackupTape ON tableBackup.ID = tableJoinBackupTape.BackupID)
ON tableTape.ID = tableJoinBackupTape.TapeID GROUP BY
tableTape.Serial)));

It is returning 26 records when there should be only 22 (I only have 22
tapes). The duplicate records showed nothing unique that I could draw a
correlation from.

The first example you provided (2 seperate queries) worked as expected
though. I've included them below for reference as well.

First query (queryMaxOfBackupDate):
SELECT Max(tableBackup.BackupDate) AS BackupDate, tableTape.Serial
FROM tableTape INNER JOIN (tableBackup INNER JOIN tableJoinBackupTape
ON tableBackup.ID = tableJoinBackupTape.BackupID) ON tableTape.ID =
tableJoinBackupTape.TapeID
GROUP BY tableTape.Serial;

Second query:
SELECT queryMaxOfBackupDate.Serial, queryMaxOfBackupDate.BackupDate AS
[Backup Date], tableBackup.Type, tableBackup.Status
FROM queryMaxOfBackupDate INNER JOIN tableBackup ON
queryMaxOfBackupDate.BackupDate = tableBackup.BackupDate
ORDER BY queryMaxOfBackupDate.BackupDate DESC;

Thanks again for your help.

John