Weird join problem

[Julia Boswell]

I've got a very strange problem that I can't explain or problem solve.

My tables are set up as follows:

tblPart (the parts table): has fields PartSN (primary key) and Status
tblDespatches (the despatch history table): has fields DespatchNo (primary
key), PartSN and ReceiptDate

Needless to say there's a one to many relationship from PartSN in tblPart to
tblDespatches.

I want to run a query that shows all Parts with a specific Status, with
their latest despatch history details. Unfortunately multiple despatches can
be made on one day, which means I need to create a base Max query
(qryStatusBaseReceipt) of the despatch history table before creating my main
query. Here's what I've done.

SELECT tblDespatches.PartSN, Max(tblDespatches.ReceiptDate) AS
MaxOfReceiptDate, Max(tblDespatches.DespatchNo) AS MaxOfDespatchNo
FROM tblDespatches
GROUP BY tblDespatches.SAASMSN;

This works fine and shows the last entered despatch history on the latest
date.

I've then created a query that uses this base query:

SELECT tblPart.PartSN, tblDespatches.ReceiptDate, tblPart.Status
FROM tblPart INNER JOIN (tblDespatches INNER JOIN qryStatusBaseReceipt ON
(qryStatusBaseReceipt.MaxOfDespatchNo = tblDespatches.DespatchNo) AND
(tblDespatches.ReceiptDate = qryStatusBaseReceipt.MaxOfReceiptDate)) ON
(tblPart.PartSN = tblDespatches.PartSN) AND (tblPart.PartSN =
qryStatusBaseReceipt.PartSN)
WHERE (((tblPart.Status)="In Store"))
ORDER BY tblPart.PartSN, tblDespatches.ReceiptDate;

When I first create this query it works absolutely fine. Then without
warning after it working fine for a while, I'll get an error message that
Access can't represent the join MaxofDespatchNo in design, which it
consequently deletes, screwing up the query and showing multiple despatch
history information for one part number.

This is a real pain, because I can't predict when it's going to fail, and
it's critical for users to be able to guarantee that the query counts the
correct number of parts.

Anyone got any ideas why this should be unpredictable and any solutions?

Thanks in advance.

Julia

 

[Julia Boswell]

Just realised I made a mistake on the text below which is now correct .....