Correlated subquery using group by prompts for parameter?!?

  • Thread starter Thread starter ericp
  • Start date Start date
E

ericp

Enter Parameter Value Query1.[Customer Id] - I hit the OK
button and everything runs fine...

I'm trying to do a straight forward "deduping" of sorts
based upon Customer Id...It doesn't matter which Order Id
I take because they've all "qualified" I just chose MAX()
grouping function in this case...but I don't want to take
just any return amount/shipped amount based upon group
functions too I want the legit stuff... it seems to run
fine I just have to hit hte OK button and that is
annoying me...

SELECT [L1].[Order Id], [L1].[Customer Id], [L1].[Email
Address], [L1].ShippedProductRevenue, [L1].ReturnAmount,
[L1].NetShippedMinusReturns
FROM [Anolon Final Qualified Order ID List] AS L1
WHERE (

[L1].[Order Id] = (

SELECT MAX(L2.[Order Id]) FROM [Anolon Final Qualified
Order ID List] AS L2 where
L2.[Customer Id] = [L1].[Customer Id]
GROUP BY L2.[Customer Id]

)

);

I've checked the spelling of [Customer Id] and done a
search/replace against it too...that's not a
problem...any recommendations?

Thanks,

Eric
 
Dear Eric:

It appears you have the idea that it just isn't recognizing one of
your [Customer Id] column references somewhere in the query. This is
probably exactly the case.

The next step, it seems to me, is to find out which one.

As an experiment, edit each [Customer Id] reference in the query,
changing it to be [Customer IdX], which will certainly cause an error.
After you have tested one of them and it turns out NOT to be the
problem one, change it back and repeat with another one. When you
edit this in exactly the one place where it is already having a
problem, everything should behave the same as it does now, except that
the Enter Parameter Value will change to say [Customer IdX]. If and
when you get it to do that, then you have found the one that needs
fixed.

Next, I'd suggest you look at the query or table which is referenced
by that [Customer Id] and confirm that there is such a column in it,
and that it is spelled exactly as you have entered it.

Anyway, that's the way I've dealt with things like this in the past,
and generally successfully.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
I don't think the GROUP BY Clause is necessary in the SubQuery.

Try without the GROUP BY Clause.

Otherwise and if [Anolon Final Qualified Order ID List] is a Query, please
post relevant Table Structure and the SQL String of this Query also.
 
Back
Top