ACCESS FEED: Re: [MS_AccessPros] Need help with query syntax

Selasa, 19 Agustus 2014

Re: [MS_AccessPros] Need help with query syntax

Thanks John. I've received no error messages but the addition of the time component has made the query run so slow it is useless. I think I found the solution however. The primary key for the PO Receipts table is an autonumber field called RECNUM_55. I substituted it for the date/time component and the query runs very fast and gives the results I'm looking for. Thanks for you help!!! I have learned so much from you and others here.

Doyce ---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Ooops. Just reread it. This should work:

SELECT DISTINCT p.PRTNUM_01, v.VENID_55, [Vendor Master].COMNAM_08 AS Comnam, v.TNXDTE_55, v.TNXTIM_55

FROM ([PART MASTER] AS p

INNER JOIN [PO Receipts] AS v

ON p.PRTNUM_01 = v.PRTNUM_55)

INNER JOIN [Vendor Master]

ON v.VENID_55 = [Vendor Master].VENID_08
WHERE (((v.TNXDTE_55 + v.TNXTIM_55)=

(SELECT Max(TNXDTE_55 + TNXTIM_55)

FROM [PO Receipts] As v2
WHERE v2.PRTNUM_55 = p.PRTNUM_01)))
ORDER BY p.PRTNUM_01;

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 Aug 19, 2014, at 4:05 PM, John Viescas JohnV@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Doyce-

Sorry, I didn't notice a space missing between your last field name and the FROM keyword. Try this:

SELECT DISTINCT p.PRTNUM_01, v.VENID_55, [Vendor Master].COMNAM_08 AS Comnam, v.TNXDTE_55, v.TNXTIM_55
FROM ([PART MASTER] AS p
INNER JOIN [PO Receipts] AS v
ON p.PRTNUM_01 = v.PRTNUM_55)
INNER JOIN [Vendor Master]
ON v.VENID_55 = [Vendor Master].VENID_08
WHERE (((v.TNXDTE_55 + v.TNXTIM_55FROM)=
(SELECT Max(TNXDTE_55 + TNXTIM_55FROM)
FROM [PO Receipts] As v2
WHERE v2.PRTNUM_55 = p.PRTNUM_01)))
ORDER BY p.PRTNUM_01;

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 Aug 19, 2014, at 3:27 PM, winberry.doyce@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,
Something is missing. I'm getthing this error:
Syntax error (missing operator) in query expression v.TNXTIM_55FROM ([PART MASTER] AS p INNER JOIN [PO Receipts] AS v ON p.PRTNUM_01 = v.PRTNUM_55) INNER JOIN [Vendor Master] ON v.VENID_55 = [Vendor Master].VENID_08WHERE (((v.TNXDTE_55 + v.TNXTIM_55FROM)=(SELECT Max(TNXDTE_55 + TNXTIM_55FROM)
FROM [PO Receipts] As v2
WHERE v2.PRTNUM_55 = p.PRTNUM_01)))
ORDER BY p.PRTNUM_01;
Doyce

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

Doyce-

You can add dates and times, like this:

SELECT DISTINCT p.PRTNUM_01, v.VENID_55, [Vendor Master].COMNAM_08 AS Comnam, v.TNXDTE_55, v.TNXTIM_55FROM ([PART MASTER] AS p INNER JOIN [PO Receipts] AS v ON p.PRTNUM_01 = v.PRTNUM_55) INNER JOIN [Vendor Master] ON v.VENID_55 = [Vendor Master].VENID_08
WHERE (((v.TNXDTE_55 + v.TNXTIM_55FROM)=(SELECT Max(TNXDTE_55 + TNXTIM_55FROM)
FROM [PO Receipts] As v2
WHERE v2.PRTNUM_55 = p.PRTNUM_01)))
ORDER BY p.PRTNUM_01;

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 Aug 19, 2014, at 2:08 PM, winberry.doyce@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Hello friends,
The query below is supposed to select the last time a part was received. However I have discovered if a part is received twice on the same day, it shows up twice and I only want the latest receipt. I have added the TXNTIM_55 field but don't know how to add the max value to the query.
SELECT DISTINCT p.PRTNUM_01, v.VENID_55, [Vendor Master].COMNAM_08 AS Comnam, v.TNXDTE_55, v.TNXTIM_55FROM ([PART MASTER] AS p INNER JOIN [PO Receipts] AS v ON p.PRTNUM_01 = v.PRTNUM_55) INNER JOIN [Vendor Master] ON v.VENID_55 = [Vendor Master].VENID_08
WHERE (((v.TNXDTE_55)=(SELECT Max(TNXDTE_55)
FROM [PO Receipts] As v2
WHERE v2.PRTNUM_55 = p.PRTNUM_01)))
ORDER BY p.PRTNUM_01;
Doyce