Graham must be away. Perhaps someone else can jump in.
I've come up with this query but It's been running for 2 hours and still says "running query" at the bottom and I've gotten no results. That won't work.
SELECT [PO Receipts].TNXDTE_55, [PO Receipts].RECNUM_55, [PO Receipts].PRTNUM_55, [PO Receipts].VENID_55, [Vendor Master].COMNAM_08
FROM [PO Receipts] INNER JOIN [Vendor Master] ON [PO Receipts].VENID_55 = [Vendor Master].VENID_08
WHERE ((([PO Receipts].PRTNUM_55)=(SELECT TOP 1 Dupe.VENID_55
FROM [PO Receipts] AS Dupe
WHERE (((Dupe.PRTNUM_55)=[PO Receipts].PrtNum_55))
ORDER BY Dupe.TNXDTE_55 DESC , Dupe.RECNUM_55)))
ORDER BY [PO Receipts].TNXDTE_55 DESC, [PO Receipts].RECNUM_55;
Perhaps someone can help.
And why are my messages not showing the whole thread? I'm replying via web post.
Doyce
---In MS_Access_Professionals@yahoogroups.com, <winberry.doyce@...> wrote:
Graham,
Thanks for the reply. First of all, the tables come from my ERP software. I do believe they are normalized. For their naming convention, every table has its own unique suffix but the first part is the same, for example PRTNUM_01 is the part number in the Part Master table. It is the same as PRTNUM_55 except that the _55 suffix refers to the PO Receipts table. That being said, I'm learning something new here. I've never used a subquery before. I've got the first query to run but I'm having trouble with the second query. The PK for the PO Receipts table is RECNUM_55. First question, Does "Parts" mean my [Part Master] table? What does PRTNAME refer to? The suffixes for the Part Master table is _01 so should the query be:
(SELECT p.PRTNUM_01, p.PRTNAME_01
(SELECT TOP 1 v.VENID_55
FROM [PO Receipts] AS v
WHERE v.PRTNUM_55=p.PRTNUM_01
ORDER BY v.TNXDTE_55 DESC, v.[RECNUM_55])
AS LastVendor
FROM [PART MASTER] AS p
ORDER BY p.PRTNUM_01)
Doyce
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (4) |
Tidak ada komentar:
Posting Komentar