KB WRONG, for TOP N values per group query.

[Phil]

Criteria: In (Select Top 3 [UnitsInStock] From Products Where
[CategoryID]=[Categories].[CategoryID] Order By [UnitsInStock] Desc)

That is the whole key to the simple solution proposed in KB 210039.

I opened up Northwind, and when I actually enter that line, I get

The expression you entered contains invalid syntax.

What am I missing?

 

[Michel Walsh]

Hi,


Works fine here. Can you cut and paste the SQL statement I got and tried it?


--------------
SELECT Categories.CategoryName, Products.ProductName, Products.UnitsInStock
FROM Categories INNER JOIN Products ON Categories.CategoryID =
Products.CategoryID
WHERE (((Products.UnitsInStock) In (SELECT TOP 3 [UnitsInStock] FROM
Products WHERE [CategoryID]=[Categories].[CategoryID] ORDER BY
[UnitsInStock] Desc )))
ORDER BY Categories.CategoryName, Products.UnitsInStock DESC;

 

[Phil]

Same answer. You realize that I am attempting to run this SQL as an
entry in the Grid of Designview, right? I don't think the problem is
with the SQL, (I am not getting an SQL syntax error when trying to RUN
it,) but the syntax of using SQL with the IN() function. As soon as I
try to leave the cell, I get that error.

Trying your SQL in the same place gave me the same results.

Hi,


Works fine here. Can you cut and paste the SQL statement I got and tried it?


--------------
SELECT Categories.CategoryName, Products.ProductName, Products.UnitsInStock
FROM Categories INNER JOIN Products ON Categories.CategoryID =
Products.CategoryID
WHERE (((Products.UnitsInStock) In (SELECT TOP 3 [UnitsInStock] FROM
Products WHERE [CategoryID]=[Categories].[CategoryID] ORDER BY
[UnitsInStock] Desc )))
ORDER BY Categories.CategoryName, Products.UnitsInStock DESC;
--------------


Hoping it may help,
Vanderghast, Access MVP

Criteria: In (Select Top 3 [UnitsInStock] From Products Where
[CategoryID]=[Categories].[CategoryID] Order By [UnitsInStock] Desc)

That is the whole key to the simple solution proposed in KB 210039.

I opened up Northwind, and when I actually enter that line, I get

The expression you entered contains invalid syntax.

What am I missing?

 

[Michel Walsh]

Hi,


Just to be sure,

1) you didn't type

"Criteria: IN(...) "


but, in the line Criteria, you typed "IN( ... ) ", under the UnitsInStock
column, without the quotes.

2 ) You are using Jet, not MS SQL Server as database (mdb, not adp).


3) You typed

[Categories].[CategoryID]

and not

[Categories.CategoryID]



Vanderghast, Access MVP

Same answer. You realize that I am attempting to run this SQL as an entry
in the Grid of Designview, right? I don't think the problem is with the
SQL, (I am not getting an SQL syntax error when trying to RUN it,) but the
syntax of using SQL with the IN() function. As soon as I try to leave the
cell, I get that error.

Trying your SQL in the same place gave me the same results.

Hi,


Works fine here. Can you cut and paste the SQL statement I got and tried
it?


--------------
SELECT Categories.CategoryName, Products.ProductName,
Products.UnitsInStock
FROM Categories INNER JOIN Products ON Categories.CategoryID =
Products.CategoryID
WHERE (((Products.UnitsInStock) In (SELECT TOP 3 [UnitsInStock] FROM
Products WHERE [CategoryID]=[Categories].[CategoryID] ORDER BY
[UnitsInStock] Desc )))
ORDER BY Categories.CategoryName, Products.UnitsInStock DESC;
--------------


Hoping it may help,
Vanderghast, Access MVP

Criteria: In (Select Top 3 [UnitsInStock] From Products Where
[CategoryID]=[Categories].[CategoryID] Order By [UnitsInStock] Desc)

That is the whole key to the simple solution proposed in KB 210039.

I opened up Northwind, and when I actually enter that line, I get

The expression you entered contains invalid syntax.

What am I missing?

 

[Phil]

Nope. I was putting it in as a seperate field called Criteria. I was
not reading it right I guess, as I could not figure out what field to
put it under. Typical Brainlock, I guess. tried it your way, the RIGHT
way, and it worked perfectly.

Thank you very much.

Hi,


Just to be sure,

1) you didn't type

"Criteria: IN(...) "


but, in the line Criteria, you typed "IN( ... ) ", under the UnitsInStock
column, without the quotes.

2 ) You are using Jet, not MS SQL Server as database (mdb, not adp).


3) You typed

[Categories].[CategoryID]

and not

[Categories.CategoryID]



Vanderghast, Access MVP

Same answer. You realize that I am attempting to run this SQL as an entry
in the Grid of Designview, right? I don't think the problem is with the
SQL, (I am not getting an SQL syntax error when trying to RUN it,) but the
syntax of using SQL with the IN() function. As soon as I try to leave the
cell, I get that error.

Trying your SQL in the same place gave me the same results.

Hi,


Works fine here. Can you cut and paste the SQL statement I got and tried
it?


--------------
SELECT Categories.CategoryName, Products.ProductName,
Products.UnitsInStock
FROM Categories INNER JOIN Products ON Categories.CategoryID =
Products.CategoryID
WHERE (((Products.UnitsInStock) In (SELECT TOP 3 [UnitsInStock] FROM
Products WHERE [CategoryID]=[Categories].[CategoryID] ORDER BY
[UnitsInStock] Desc )))
ORDER BY Categories.CategoryName, Products.UnitsInStock DESC;
--------------


Hoping it may help,
Vanderghast, Access MVP



Criteria: In (Select Top 3 [UnitsInStock] From Products Where
[CategoryID]=[Categories].[CategoryID] Order By [UnitsInStock] Desc)

That is the whole key to the simple solution proposed in KB 210039.

I opened up Northwind, and when I actually enter that line, I get

The expression you entered contains invalid syntax.

What am I missing?

 

[kimmi2ue]

I have a similar question, but I'm apparently not savvy enough in Access. I'm trying to create a query using 1 table. I want the top 10 meds, per department. I have a column with departments (department), a column with medication names (Medication), and a column with a count of each medication name (CountofMedication).

Can anyone help me?

Thanks
Kim