Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2008
    Posts
    26

    Unanswered: Return 3 most recent records in MS Query

    I wasn't sure if this was the correct forum or not but my question deals with MS Query in an Excel sheet pulling data from an Access query.

    Here is my current SQL statement:

    SELECT `Page_Count Query`.`Job ID`, `Page_Count Query`.`Job Number`, `Page_Count Query`.`Customer Code`, `Page_Count Query`.`Page Count`, `Page_Count Query`.`Close Date`, `Page_Count Query`.PGCT

    FROM `JC_Data`.`Page_Count Query` `Page_Count Query`

    WHERE (`Page_Count Query`.`Customer Code`=?) AND (`Page_Count Query`.PGCT=?)


    What I'm hoping to return is not only the 'Customer Code' and 'PGCT' that match the parameters set in the sheet, but also the three most recent transactions by 'Close Date'. The few statements I've tried, I've had no luck w/. Anyone have suggestions?

  2. #2
    Join Date
    Sep 2008
    Posts
    5
    SELECT TOP 3 `Page_Count Query`.`Job ID`, `Page_Count Query`.`Job Number`, `Page_Count Query`.`Customer Code`, `Page_Count Query`.`Page Count`, `Page_Count Query`.`Close Date`, `Page_Count Query`.PGCT

    FROM `JC_Data`.`Page_Count Query` `Page_Count Query`

    WHERE (`Page_Count Query`.`Customer Code`=?) AND (`Page_Count Query`.PGCT=?)
    ORDER BY `Page_Count Query`.`Close Date`

  3. #3
    Join Date
    Nov 2008
    Posts
    26
    At first glance that seems like the correct solution. Unfortunately when I enter the statement I get the error:

    Parameters are not allowed in queries that can't be displayed graphically.

    I had no previous trouble before trying to return the top 3 entries.

  4. #4
    Join Date
    Sep 2008
    Posts
    5
    Without testing it, I would guess the ORDER BY clause is the problem. Depending on the ANSI compatibility standard that your system uses, you may need to DISPLAY/SELECT the field used in the ORDER BY,

    So you might try
    ORDER BY 'Close Date'

    or
    Some variation of the actual names used (Literal or aliased)

    You might also try this
    SELECT TOP 3
    P.[Job ID],
    P.[Job Number],
    P.[Customer Code],
    P.[Page Count],
    P.[Close Date],
    P.[PGCT]
    FROM [JC_Data].[Page_Count Query] P
    WHERE (P.[Customer Code]=?) AND (P.PGCT=?)
    ORDER BY P.[Close Date]

  5. #5
    Join Date
    Nov 2008
    Posts
    26
    Yeah I'm still getting the same error. I've tried removing some fields to isolate the problem and it seems to come back to the Close Date. I'm pursuing another avenue through some Excel formulas but if anyone has another suggestion for the Query, that is my preferred means.

    Thanks for the help.

Posting Permissions

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