Why is Access requesting a parameter value when running SQL

user_15989804 0
Viewed 59
Select count([Customer Number])
From
(
    SELECT distinct [Customer Number] & [Membership Number], [Customer Number]
    FROM Data
    Where [Customer Number] = 2000004
)
group by [Customer Number] & [Membership Number];

Looking for a parameter value of Membership Number. Why is this?

ms-access sql
1 Answers

The only fields available to the outer SELECT are those which are listed in the subquery SELECT. That subquery field list does not include [Membership Number] as a separate field. It's only there as part of an expression which concatenates 2 fields. Access will assign an alias (like "Expr" followed by a number) to that calculated column. And it does not return a column named [Membership Number].

Here is an Immediate window example which illustrates the issue ...

strSubQ = "SELECT DISTINCT [Customer Number] & [Membership Number], [Customer Number] FROM Data"
Set rs = CurrentDb.OpenRecordset(strSubQ)

' what are the names of the fields available from the subquery?
For i = 0 To (rs.Fields.Count - 1): ? rs.Fields(i).Name : Next
Expr1000
Customer Number

So when Access sees [Membership Number] in the outer query's GROUP BY clause, it can't find that name in the available fields, and therefore assumes it must be a parameter.

user_773350
Related