From: Peter Brawley
Date: June 13 2006 9:57pm
Subject: Re: MS access query in mysql
List-Archive: http://lists.mysql.com/mysql/198886
Message-Id: <448F34BA.8090301@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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