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
