CODEWITHSUNDEEP
databasems-access
prodigerati
prodigerati

Reputation: 607

Access SubQuery: SHOW TOP (count form select query) Table

Is it possible to use a Count() or number from another Select query to SELECT TOP a number of rows in a different query?

Below is a sample of the update query I'm trying to use but would like to take the count from another query to replace "10".

...
WHERE Frames.Package IN (
   SELECT TOP 10 Frames
   FROM Frames.Package WHERE Package = "100" 
   ORDER BY Frames.ReferenceNumber
)

So for example, i've tried to do 

SELECT TOP SelectQuery.RecordCount Frames

Sample SelectQuery.RecordCount

SELECT COUNT(Frames.Package) AS RecordCount
FROM Frames
HAVING Frames.Package = "100";

Any assistance would be appreciated...

Upvotes: 0

Views: 157

Answers (1)

Jabberbyter
Jabberbyter

Reputation: 81

Access does not support using a parameter for SELECT TOP. You must write a literal value into the text of the SQL statement.

From another answer: Select TOP N not working in MS Access with parameter

On that note, your two queries appear to be just interchanging HAVING and WHERE clauses to get the record count. It doesn't seem to be doing anything more, thus why bother with the TOP clause and simply SELECT * FROM Frames WHERE [..]?

Am I missing something?

Upvotes: 0

Related Questions