Hi Doyce
Not away – just enjoying a leisurely Saturday morning swim at the beach with the dog, followed by a late breakfast with the cryptic crossword puzzle :-)
From your table and field descriptions, your substitutions should be as follows:
[PO Receipts PK] == RECNUM_55
Parts == [PART MASTER]
PRTNUM == PRTNUM_01
Don't worry about PRTNAME – I was just demonstrating that you can include other fields from [PART MASTER] (e.g. the part name or price) in the query.
Given these substitutions, your query should look like this:
SELECT
p.PRTNUM_01,
(SELECT TOP 1 v.VENID_55
FROM [PO Receipts] AS v
WHERE v.PRTNUM_55=p.PRTNUM
ORDER BY v.TNXDTE_55 DESC, v.RECNUM_55)
AS LastVendor
FROM [PART MASTER] AS p
ORDER BY p.PRTNUM_01;
I assume that you have a one-to-many relationship between PRTNUM_01 and PRTNUM_55, and that both fields are indexed. You should also have indexes on VENID_55 and TNXDTE_55, otherwise performance will suffer badly.
Best wishes,
Graham
PS: To include the preceding messages in your reply in a web post, I think you click on "Show message history"
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of winberry.doyce@roadsysinc.com
Sent: Saturday, 1 March 2014 10:52
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Last Receipt with Vendor
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 (5) |
Tidak ada komentar:
Posting Komentar