Referencing values within queries

[sf827]

This is probably an easy one, but for the life of me I can't understand why this isn't working.



Whenever I go run the following SQL expression I get a message asking for a value for "T1.N".

SELECT T1.State, Count(T1.Outlet), T1.N
FROM (SELECT State, Outlet, Count(Outlet) AS N FROM ExtractEvents AS T1 WHERE EventCode = "IN" OR "RETURNED" GROUP BY State, Outlet)
GROUP BY T1.State

Why is the reference not working?

Thanks

[aytee111]

Access probably doesn't like your column name. Put it in square brackets - T1.[N]

[aytee111]

It is a valid column, right? I see you are also using the letter N as an alias for your count. I would change that so that you can see where there are different values. "N" isn't a good name for a column or for an alias, it isn't very descriptive.

[sf827]

Hi aytee,

I have run the subquery and it works fine, showing correct values under N It's just when I go to reference N that it won't work for me... Is there a way of referencing the "N" expression without using an alias?

Thx

[aytee111]

I don't think I can help you further. That's all I would try, and if neither the square brackets or changing the name works, then I don't know what the problem is. Sorry.

[sf827]

Thanks anyway

[aytee111]

Finally figured it out - I wonder if you have too!

The last part needs to be GROUP BY State, N

It doesn't know what to do with the N, that is why it keeps asking for a value. Here's how I did it - I made two actual queries, instead of doing it all in one, then it became obvious!

[sf827]

Unfortunately not! Would you please post an example of the codes you used?

[aytee111]

Add ", N" to the end of your string

[sf827]

All good I've figured it out. Thanks