ACCESS FEED: Re: [MS_AccessPros] RE: Adding Index to SQL View

Jumat, 07 Maret 2014

Re: [MS_AccessPros] RE: Adding Index to SQL View

Doyce-

SELECT DISTINCT is what I was going to recommend after I saw your first message. That makes the query not updatable, but maybe that doesn't matter.

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

http://www.viescas.com/

(Paris, France)

On Mar 7, 2014, at 8:24 PM, <winberry.doyce@roadsysinc.com> <winberry.doyce@roadsysinc.com> wrote:

John,
I just found out that part number 2300163 was received 12 times on the latest date so I guess thats why your query returned it twelve times. If I change it to this:
SELECT DISTINCT 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(TNXDTE_55)
FROM [PO Receipts] As v2
WHERE v2.PRTNUM_55 = p.PRTNUM_01)));
It only returns one record for 2300163. It it a problem to add DISTINCT behind SELECT?
Doyce

---In MS_Access_Professionals@yahoogroups.com, <winberry.doyce@...> wrote :

John,
Your query runs almost instantly, however it's returning some records that are duplicates. For example I got part number 2300163 with vendor 56255 twelve times. How would I add an index to the SQL table?
Doyce

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
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
http://www.viescas.com/
(Paris, France)

On Mar 7, 2014, at 7:11 PM, <winberry.doyce@...> <winberry.doyce@...> 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