Graham,
Now I'm jealous!! You must be down under or someplace very far away from Arkansas. It was late Friday afternoon here when you replied. We've had a very cold winter and are anxiously awaiting spring!! The query appears to be working however, it is still taking very, very long. I'll work on this more Monday and check the indexes and see if I can figure out something to speed it up. Thanks for your help!! I'll let you know how it comes out. This has been a great learning experience for me.
Doyce
---In MS_Access_Professionals@yahoogroups.com, <graham@...> wrote:
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@...
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 (6) |
Tidak ada komentar:
Posting Komentar