I have written a query in access that works and looks like this.....

SELECT DISTINCT(Royal.Postcode) AS Postcode, B.[Business Amount], P.[Private Amount]
FROM Royal,
(SELECT Postcode, Count(VMFCode) AS [Business Amount]
FROM Royal
WHERE VMFCode = "RMB"
GROUP BY Postcode, VMFCode) B,
(SELECT Postcode, Count(VMFCode) AS [Private Amount]
FROM Royal
WHERE VMFCode = "RMP"
GROUP BY Postcode, VMFCode) P
WHERE (((B.Postcode)=[Royal].[Postcode]) AND ((P.Postcode)=[Royal].[Postcode]) AND ((Royal.Status)="Active"));

This query works fine, but when i save and close it and then run it, then open it again, it looks like this ...

SELECT DISTINCT (Royal.Postcode) AS Postcode, B.[Business Amount], P.[Private Amount]
FROM Royal, [SELECT Postcode, Count(VMFCode) AS [Business Amount]
FROM Royal
WHERE VMFCode = "RMB"
GROUP BY Postcode, VMFCode]. AS B, [SELECT Postcode, Count(VMFCode) AS [Private Amount]
FROM Royal
WHERE VMFCode = "RMP"
GROUP BY Postcode, VMFCode]. AS P
WHERE (((B.Postcode)=[Royal].[Postcode]) AND ((P.Postcode)=[Royal].[Postcode]) AND ((Royal.Status)="Active"));

The problem now is when i try to amend the query and save it again i get the error :

"Invalid bracketing of name 'SELECT Postcode, Count(VMFCode) AS [Business Amount'.

I get this error also when the data in the database changed. I've tried reverting everything back, but the same happens

Can anyone help????

Access uses a different format than the standard t-sql queries. Your best choice is to save the query to a text file so that you can append to it and then repaste it.

Chi

They mostly come at night...mostly

Thanks chi, but the error comes when the data is changed also, which is obviously no good. Is there some kind of different bracketing convention or something with Access?