List:General Discussion« Previous MessageNext Message »
From:Andrew Zahn Date:June 15 2006 3:08pm
Subject:Re: MS access query in mysql
View as plain text  
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
>>
>>
>
>

Thread
MS access query in mysqlAndrew Zahn13 Jun
  • Re: MS access query in mysqlPeter Brawley13 Jun
    • Re: MS access query in mysqlAndrew Zahn15 Jun