Andrew Zahn wrote:
I am using MS Access to read from a MySQL database. The query
generated through access listed below returns incorrect data when
executed in Access and doesn't work at all in MySQL. I believe it has
to Last() and with the # symbols around the date. Any insight into
this problem would be greatly appreciated.
SELECT ReturnTbl.ComponentID, ReturnTbl.PartDescription,
Sum(ReturnTbl.Quantity) AS SumOfQuantity, Last(CompVendorListTbl.Cost)
AS LastOfCost
FROM ReturnTbl LEFT JOIN CompVendorListTbl ON ReturnTbl.ComponentID =
CompVendorListTbl.ComponentID
WHERE (((ReturnTbl.Date)<#2/1/2006#))
GROUP BY ReturnTbl.ComponentID, ReturnTbl.PartDescription,
ReturnTbl.USL, ReturnTbl.RtnMfgr
HAVING (((Sum(ReturnTbl.Quantity))<>0) AND ((ReturnTbl.USL)=0) AND
((ReturnTbl.RtnMfgr)=0))
ORDER BY ReturnTbl.PartDescription;
MySQL has no Last() function. You need a subquery (eg SELECT MAX(cost)
FROM CompVendorListTbl WHERE componentid=returntbl.componentid), or one
of the tricks described at
http://dev.mysql.com/doc/refman/5.1/en/example-maximum-column-group-row.html,
or one of the tricks described under 'Within-Group Aggregates' at
http://www.artfulsoftware.com/queries.php.
Surround date literals with '', not ##. MySQL will not expect a US date
format unless you apply explicit formatting.
PB
-----
Thanks,
Andrew Zahn
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.8.4/363 - Release Date: 6/13/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
