Doyce-
Maybe:
SELECT p.PRTNUM_01, v.VENID_55
FROM [PART MASTER] As p
INNER JOIN [PO Receipts] As v
ON p.PRTNUM_01 = v.PRTNUM_55
WHERE v.TNXDTE_55 =
(SELECT Max(TNXTDTE_55)
FROM [PO Receipts] As v2
WHERE v2.PRTNUM_55 = p.PRTNUM_01);
In my version, it's the date that needs the index, but it might run faster anyway.
Adding an index shouldn't invalidate their install, but you might have to put the index back after an update from them. And if they send in an update, you can always (temporarily) remove your additional index, do the install, then put it back.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
On Mar 7, 2014, at 7:11 PM, <winberry.doyce@roadsysinc.com> <winberry.doyce@roadsysinc.com> wrote:
John,
Here is the SQL:
SELECT p.PRTNUM_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;
PRTNUM_55 and TNXDTE_55 have indexes. VENID_55 does not. PRTNUM_01 is the PK of the PART MASTER table. I am not locked out of the SQL DB but I fear that if I change anything that when my software vendor updates their software package, the updates won't install correctly and they won't give me any support because I changed something on the table. What I'm trying to do is get the VENID from the latest receipt of a part number.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