HELP! That article about derived tables for SQL Server doesn't work for MsAccess

Results 1 to 5 of 5

Thread: HELP! That article about derived tables for SQL Server doesn't work for MsAccess

  1. #1
    Lodewijk Wiggers Guest

    Default HELP! That article about derived tables for SQL Server doesn't work for MsAccess

    Who can help me out here ? <BR>I have tried the method of having derived tables in the FROM clause but MsAccess doesn&#039t accept a SELECT statement after a FROM clause.... Is there a way to solve this or am I asking to much of Microsoft ? <BR>P.S. CREATE VIEW is also not implemented...grr..

  2. #2
    Join Date
    Dec 1969
    Posts
    1,315

    Default RE: HELP! That article about derived tables for SQL Server doesn't work for MsAccess

    Yes, there is.<BR>You need to create a saved querydef first in the FROM clause.<BR>Then create another query, or just an SQL statement based on<BR>this saved querydef.<BR>Can you give us an example how you want to create the query?<BR><BR>John Weeflaar

  3. #3
    Lodewijk Wiggers Guest

    Default RE: HELP! That article about derived tables for SQL Server doesn't work for MsAccess

    Yes of course I can:<BR>I have the following table in MsAcces:<BR>BOOKS with 22 columns, one of them is the NR_SOLD, which I want to use to find the top N bestsold books.<BR>So, I want to ORDER BY NR_SOLD DESC. This is NOT a problem, the problem is how I return only the top N records of this query...<BR>In other words, another query should select only the top N records...Maybe you know how ??<BR>Thanks for responding to my question!!

  4. #4
    Join Date
    Dec 1969
    Posts
    1,315

    Default RE: HELP! That article about derived tables for SQL Server doesn't work for MsAccess

    Yes, it is possible to do that.<BR>You don&#039t even need to create the first query (...DESC).<BR>It&#039ll select the first top N bestsold books by itself.<BR>Here is the solution: <BR>To select the top 15 bestsold books:<BR><BR>SELECT * <BR>FROM BOOKS a<BR>WHERE 15 >=<BR>(SELECT Count(*)<BR>FROM BOOKS b<BR>WHERE b.NR_SOLD >= a.NR_SOLD)<BR>ORDER BY NR_SOLD DESC<BR><BR>You can use a variable N to replace the 15, where this variable<BR>can be an input from a text field, for example.<BR><BR>Happy Programming!<BR><BR>John Weeflaar


  5. #5
    Lodewijk Guest

    Default Thanks John! It works!

    Used your example and it works perfectly! :))<BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •