Results 1 to 14 of 14
  1. #1
    Lounger
    Join Date
    Jul 2002
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query (last 40 recent dates) (Accesss 2000)

    Hi,

    I have a field in a query that is just dates for the last 2 years.

    Is there a way for me to specify the criteria to pull up the last 40 most recent dates?

    Thanks,Bill

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Query (last 40 recent dates) (Accesss 2000)

    Try SELECT TOP 40 .......
    HTH
    Pat

  3. #3
    Lounger
    Join Date
    Jul 2002
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query (last 40 recent dates) (Accesss 2000)

    Hi Pat,

    I don't know what you mean I entered (SELECT TOP 40 .......
    HTH) and it says the syntax is incorrect.

    The field name is "Date"

    Could you give me a little more info.

    Thanks for your help.

    Bill

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Query (last 40 recent dates) (Accesss 2000)

    I presume that your query is:
    SELECT Date FROM tblYourName , or something like this.

    You would need to change it to:
    SELECT Top 40 Date FROM tblYourName

    Pat

  5. #5
    Lounger
    Join Date
    Jul 2002
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query (last 40 recent dates) (Accesss 2000)

    Hi Pat,

    Forgive me for being so slow at this but here is what I have.

    Name of the table that the query is on. "A_OUTINGS_FINAL"

    Name of the query is. "A_OUTINGS_FINAL Query"

    Name of the the Fields in the query "Name" and "Date"

    I then entered "SELECT Top 40 Date FROM tblA_OUTINGS_FINAL" in the criteria box for the date field but this doesn't work.
    I guess I'm going about it the wrong way, any suggestions?

    Thanks,
    Bill

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Query (last 40 recent dates) (Accesss 2000)

    Sorry for not being more explicit, but the SQL of the query is:

    SELECT Top 40 Date FROM tblA_OUTINGS_FINAL

    There is no criteria in this query.

    Pat

  7. #7
    Lounger
    Join Date
    Jul 2002
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query (last 40 recent dates) (Accesss 2000)

    Hi Pat,

    OK in the query in a Field box in its own colum I have entered "Expr1: (SELECT Top 40 Date FROM tblA_OUTINGS_FINAL)

    I then get the message " The Microsoft Jet database engine cannot find the input table or query 'tblA_OUTINGS_FINAL'. Make sure it exist and that the name is spelled correctly"

    Thanks,
    Bill

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Query (last 40 recent dates) (Accesss 2000)

    <P ID="edit" class=small>(Edited by patt on 26-Mar-03 15:55. Click on properties)</P>No, go into query design mode (the grid view of the query), then select the SQL mode of the query and paste what I previously said into this area.
    The other way to do this is to start a new query, select your table, drag the date down to the grid. Next right click in the top grey area, click onto properties and enter 40 into the top parameter.
    HTH
    Pat

  9. #9
    Lounger
    Join Date
    Jul 2002
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query (last 40 recent dates) (Accesss 2000)

    Hi Pat,

    >>>The other way to do this is to start a new query, select your table, drag the date down to the grid. Next right click in the top grey area, click onto properties and enter 40 into the top parameter.<<<

    When I right click in the grey area a box called "query properties" opens up. The top parameter is "Description" is this where I should enter 40?

    I also have a third field called "Time" (I left it out to make it easier to explain what I was trying to do). I"m not sure but if I do enter 40 how will I know if it is going to pull up the top 40 dates or the top 40 times?

    When I put 40 in description I still get more than 40 records but if I put it in Top Values I then get 40 records but is it time or date?

    Thanks,
    Bill

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Query (last 40 recent dates) (Accesss 2000)

    >>When I put 40 in description I still get more than 40 records but if I put it in Top Values I then get 40 records but is it time or date?<<

    You are getting somewhere now (40 into Top Values), if you specified Date as the field you will get the top 40 dates, else if you specified time then you will get the top 40 times.

    Let me know how you go, it seems like you are on the right track now though.

    Pat

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Query (last 40 recent dates) (Accesss 2000)

    To get more of an appreciation look in the help for Top Predicate, some of which follows:

    TOP n [PERCENT]
    Returns a certain number of records that fall at the top or the bottom of a range specified by an ORDER BY clause. Suppose you want the names of the top 25 students from the class of 1994:

    SELECT TOP 25 FirstName, LastName
    FROM Students
    WHERE GraduationYear = 1994
    ORDER BY GradePointAverage DESC;

    If you don't include the ORDER BY clause, the query will return an arbitrary set of 25 records from the Students table that satisfy the WHERE clause.

    Pat

  12. #12
    Lounger
    Join Date
    Jul 2002
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query (last 40 recent dates) (Accesss 2000)

    Hi Pat,

    Thanks for all of your help.

    Here is how I got the results I was looking for. In the query design grid I right clicked on the top grey area as you suggested and selected properties. I then entered 40 in the top values box and closed the properties window.
    Next I set the field DATE to sort in descending order, the query will find the top 40 of the leftmost colum that has sort selected.

    Bill

  13. #13
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Query (last 40 recent dates) (Accesss 2000)

    Well done. Do you go into SQL much for your query definition or do you stay in the design grid?
    Pat

  14. #14
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query (last 40 recent dates) (Accesss 2000)

    Just a quick FYI...on an interesting thread. When you go into the design view of a query, the 'Top Values' should actually be visible to you. Look for the word 'All' in your menus. It will be in a drop down box. That's the 'menu' option that sets the 'Top' statement for you. ('All sets nothing, but you can select from the list in the drop down, or just type the value into that box).

    I'm embarassed to say how long I was developing stuff in Access, and never realized what that 'All' was doing up there, until a while back I learned about the Top Statement. Go figure!

Posting Permissions

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