Re post as no responses - join problem with query

  • Thread starter Thread starter Julia Boswell
  • Start date Start date
J

Julia Boswell

Folks, I'm re-posting as I had no responses and I'm still stuck, any idea on
the following:
 
Gidday Julia,
What I'd do with this one is create my own alias for MaxOfDespatchNo,
don't let Access create it for you. Your own alias needs to be only
slightly different to the one Access creates - like MaxDespatchNo for
instance.

I have seen this happen before and it seems to fix it - no promises
though.

Your SQL would then read:
SELECT tblDespatches.PartSN, Max(tblDespatches.ReceiptDate) AS
MaxOfReceiptDate, Max(tblDespatches.DespatchNo) AS MaxDespatchNo
FROM tblDespatches
GROUP BY tblDespatches.PartSN;

Then use that new field to join your other query.

Cheers
David Fenton
Access Developer
Brisbane
Australia
 
Julia Boswell said:
Folks, I'm re-posting as I had no responses and I'm still stuck, any idea on
the following:
Click to expand...
Hi Julia,

Did you "type in" this query?

This part just does not look right to me
(I would expect an On clause to refer to
only 2 tables/queries):

ON
(tblPart.PartSN = tblDespatches.PartSN)
AND
(tblPart.PartSN = qryStatusBaseReceipt.PartSN)

Without data in front of me (so maybe I don't see
something important), I wonder what would
be wrong with:

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


Is it possible that maxdespatchno does not occur
in same record of tbldespatches for maxreceiptdate?

Just one idea....

Gary Walter
 
or?

SELECT
tblPart.PartSN,
tblPart.Status,
tblDespatch.ReceiptDate
FROM
(tblPart
INNER JOIN tblDespatch
ON
tblPart.PartSN = tblDespatch.PartSN)
INNER JOIN qryStatusBaseReceipt
ON
(tblDespatch.ReceiptDate = qryStatusBaseReceipt.MaxOfReceiptDate)
AND
(tblDespatch.PartSN = qryStatusBaseReceipt.PartSN)
AND
(tblDespatch.DespatchNo = qryStatusBaseReceipt.MaxOfDespatchNo)
WHERE (((tblPart.Status)="In Store"))
ORDER BY tblPart.PartSN, tblDespatch.ReceiptDate;
 
Thanks David, you're right Access created this one. I'll try this and see
how it works.

Julia
 
Thanks Gary, no I didn't write the SQL myself. I did it through Access
designer. I've got a couple of suggestions/solutions to try. I'll have a go
and see if any of them work. Thanks for your suggestions.

Julia
 
Back
Top