A select query selecting a select statement

ms-access, ms-access-2007, select, sql

Solution

Not sure if Access supports it, but in most engines (including `SQL Server`) this is called a correlated subquery and works fine:

SELECT  TypesAndBread.Type, TypesAndBread.TBName,
        (
        SELECT  Count(Sandwiches.[SandwichID]) As SandwichCount
        FROM    Sandwiches
        WHERE   (Type = 'Sandwich Type' AND Sandwiches.Type = TypesAndBread.TBName)
                OR (Type = 'Bread' AND Sandwiches.Bread = TypesAndBread.TBName)
        ) As SandwichCount
FROM    TypesAndBread

This can be made more efficient by indexing `Type` and `Bread` and distributing the subqueries over the `UNION`:

SELECT  [Sandwiches Types].[Sandwich Type] As TBName, "Sandwich Type" As Type,
        (
        SELECT  COUNT(*) As SandwichCount
        FROM    Sandwiches
        WHERE   Sandwiches.Type = [Sandwiches Types].[Sandwich Type]
        )
FROM    [Sandwiches Types]
UNION ALL
SELECT  [Breads].[Bread] As TBName, "Bread" As Type,
        (
        SELECT  COUNT(*) As SandwichCount
        FROM    Sandwiches
        WHERE   Sandwiches.Bread = [Breads].[Bread]
        )
FROM    [Breads]

Problem

I don't even know if I am doing this query the right way. There is a `Sandwiches` table that has some 7 fields and 2 of them are comboboxes (`Type` and `Bread`). So I made a query that combines all of the comboboxes values into one query, like this: ``` SELECT TypesAndBreads.TBName, TypesAndBreads.Type FROM (SELECT [Sandwiches Types].[Sandwich Type] As TBName, "Sandwich Type" As Type FROM [Sandwiches Types] UNION ALL SELECT Breads.Bread As TBName, "Bread" As Type FROM Breads) AS TypesAndBreads; ``` I get the flat values of the tables now I want to count all the sandwiches under each `TypesAndBreads.TBName`. I have this, just to make sure it works with all the Sandwiches: ``` SELECT TypesAndBread.Type, TypesAndBread.TBName, (SELECT Count(Sandwiches.[SandwichID]) As SandwichCount FROM Sandwiches) As SandwichCount FROM TypesAndBread; ``` But I want to reference the current Type and TBName inside the subquery. Something like this: ``` SELECT TypesAndBread.Type, TypesAndBread.TBName, (SELECT Count(Sandwiches.[SandwichID]) As SandwichCount FROM Sandwiches WHERE Sandwiches.[TypesAndBread.Type] = Sandwiches.[TypesAndBread.TBName]) As SandwichCount FROM TypesAndBread; ``` But of course this doesn't work. I didn't think it will, just thought of giving it a try. I was thinking of maybe constructing the query with VBA when they open the Report that this query is going to be based of. So I guess my question is: Is there a way to reference the current selected fields in a subquery? Or is there a different way to approach this? Thanks for the help EDIT: My table structure is like this: `Sandwiches`'s fields ``` | SandwichID | Name | Date Added | Chef | Sandwich Type | Bread | Reviewed By | ``` where `Sandwich Type` and `Bread` are Lookup fields for these tables: `Sandwiches Types`'s fields ``` | Sandwich Type | ``` `Breads`'s fields ``` | Bread | ``` The TypesAndBreads query combined the Sandwiches Types and Breads tables, but the reason for that is so that I can get the count of all the sandwiches that have that Type or bread. A result like this: ``` +=============================================+ | Type | TBName | SandwichCount | +=============================================+ | Sandwich Type | Turkey Club | 10 | | Bread | Italian | 5 | | Bread | Garlic | 8 | +---------------------------------------------+ ``` the example result's first row basicly says there are 10 sandwiches in record with the Sandwich Type field equal to Turkey Club. I hope that explains it better.

Original source