Syntax Error in From Clause

[jsccorps]

Following SQL code works fine. However, if I copy this SQL code and paste it
to a new query, when I try to run or save it I get the message: "Syntax error
in From clause".

SELECT T1.VendorID, T1.MagazineID, T2.MinRemit, T1.Remit
FROM MagazineData_appended_Vendors AS T1 INNER JOIN [SELECT
[MagazineData_appended_Vendors].MagazineID,
Min([MagazineData_appended_Vendors].Remit) AS MinRemit FROM
MagazineData_appended_Vendors GROUP BY
[MagazineData_appended_Vendors].MagazineID]. AS T2 ON (T1.MagazineID =
T2.MagazineID) AND (T1.Remit = T2.MinRemit)
GROUP BY T1.VendorID, T1.MagazineID, T2.MinRemit, T1.Remit
ORDER BY T1.VendorID, T1.MagazineID;

 

[Allen Browne]

Take a look at the GROUP BY clause.

I can't make sense of:
[MagazineData_appended_Vendors].MagazineID].

 

[Michel Walsh]

Try:


SELECT T1.VendorID, T1.MagazineID, T2.MinRemit, T1.Remit
FROM MagazineData_appended_Vendors AS T1
INNER JOIN (SELECT MagazineID, Min(Remit) AS MinRemit
FROM MagazineData_appended_Vendors
GROUP BY MagazineID) AS T2
ON (T1.MagazineID = T2.MagazineID) AND (T1.Remit = T2.MinRemit)
GROUP BY T1.VendorID, T1.MagazineID, T2.MinRemit, T1.Remit
ORDER BY T1.VendorID, T1.MagazineID;





OR

Make a (saved) query with

SELECT MagazineID, Min(Remit) AS MinRemit
FROM MagazineData_appended_Vendors
GROUP BY MagazineID

say it is saved under the name of q1, then use:

SELECT T1.VendorID, T1.MagazineID, T2.MinRemit, T1.Remit
FROM MagazineData_appended_Vendors AS T1 INNER JOIN q1 AS T2
ON (T1.MagazineID =
T2.MagazineID) AND (T1.Remit = T2.MinRemit)
GROUP BY T1.VendorID, T1.MagazineID, T2.MinRemit, T1.Remit
ORDER BY T1.VendorID, T1.MagazineID;



Vanderghast, Access MVP