Thank you very much for the tips and for the links. I think I have it
working now with a subquery.
cheers,
Andrew Zahn
Peter Brawley wrote:
> 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
>>
>>
>
>
