Access-muddled SQL runs but won't save.

[Guest]

I need to change a query, but Access won't let me. The query runs OK but
raises an error when I try to save it:

SELECT [Sub Kit Comps].KitItem
FROM [SELECT COUNT (1) AS CountParts, KitItem FROM
(SELECT * FROM [Sub Kit Comps] INNER JOIN [Find Items In Kits]
ON [Find Items In Kits].[Part Number] = [Sub Kit Comps].Item
WHERE [Sub Kit Comps].Item
IN (SELECT [Part Number] FROM [Find Items In Kits]))
GROUP BY KitItem]. AS Alias1
WHERE (((Alias1.CountParts)=(SELECT COUNT (1) FROM [Find Items In Kits])));

How do I fix the syntax so Access will accept it?

 

['69 Camaro]

Hi, Allen.

How do I fix the syntax so Access will accept it?

Try the following:

SELECT [Sub Kit Comps].KitItem
FROM (SELECT COUNT (1) AS CountParts, KitItem FROM
(SELECT * FROM [Sub Kit Comps] INNER JOIN [Find Items In Kits]
ON [Find Items In Kits].[Part Number] = [Sub Kit Comps].Item
WHERE [Sub Kit Comps].Item
IN (SELECT [Part Number] FROM [Find Items In Kits]))
GROUP BY KitItem) AS Alias1
WHERE (((Alias1.CountParts)=(SELECT COUNT (1) FROM [Find Items In Kits])));

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

I need to change a query, but Access won't let me. The query runs OK but
raises an error when I try to save it:

SELECT [Sub Kit Comps].KitItem
FROM [SELECT COUNT (1) AS CountParts, KitItem FROM
(SELECT * FROM [Sub Kit Comps] INNER JOIN [Find Items In Kits]
ON [Find Items In Kits].[Part Number] = [Sub Kit Comps].Item
WHERE [Sub Kit Comps].Item
IN (SELECT [Part Number] FROM [Find Items In Kits]))
GROUP BY KitItem]. AS Alias1
WHERE (((Alias1.CountParts)=(SELECT COUNT (1) FROM [Find Items In
Kits])));

How do I fix the syntax so Access will accept it?

 

[Guest]

Warning, next time you edit the SQL, you may have to make
the same change again: Access may change it back to the broken
version.

This kind of query is used where you have code that creates
the query sql, and then use code to create a recordset.

If you want to use the Access GUI to write and edit the SQL,
it would probably be better to create a nested set of querydefs:

A:
SELECT * FROM B
WHERE (((Alias1_CountParts)=(SELECT COUNT (1) FROM [Find Items In Kits])));

B:
SELECT COUNT (1) AS Alias1_CountParts, KitItem FROM C GROUP BY KitItem


C:
SELECT * FROM [Sub Kit Comps] INNER JOIN [Find Items In Kits]
ON [Find Items In Kits].[Part Number] = [Sub Kit Comps].Item
WHERE [Sub Kit Comps].Item
IN (SELECT [Part Number] FROM [Find Items In Kits])

(david)