Advertisement
Help Keep Boards Alive. Support us by going ad free today. See here: https://subscriptions.boards.ie/.
If we do not hit our goal we will be forced to close the site.

Current status: https://keepboardsalive.com/

Annual subs are best for most impact. If you are still undecided on going Ad Free - you can also donate using the Paypal Donate option. All contribution helps. Thank you.
https://www.boards.ie/group/1878-subscribers-forum

Private Group for paid up members of Boards.ie. Join the club.

Access query

  • 20-02-2004 01:30PM
    #1
    sound_wave
    Closed Accounts Posts: 1,152 ✭✭✭


    hey all! im having a problem with an access query, i have to crete a query which lists the top ten rental vehicles in a frim.

    i have tried querying using a count and sorting this in decending order but this is outputting reoccuring values. i suppose my main question is how do i stop a car number form being displayed more than once!

    thanks


Comments

  • #2
    Karoma
    Registered Users, Registered Users 2 Posts: 19,396 ✭✭✭✭Karoma


    using DISTINCT


  • #3
    Spunj
    Registered Users, Registered Users 2 Posts: 437 ✭✭Spunj


    No clue what your tables look like but

    try something like this:
    : 
    Select  RegNo, Sum(RentalCount)  as TotalRentals
From [YourTable]
Group by RegNo
Order by Sum(RentalCount) desc

    Replace RegNo with whatever the Vehicle Table key is.


  • #4
    Seth Brundle
    Moderators, Politics Moderators, Paid Member Posts: 44,046 Mod ✭✭✭✭Seth Brundle


    fields:
    Table 1:- id, car
    Table2:- B_id, id, car

    SELECT TOP 10 Table1.car, Count(Table2.id) AS Table2_id, Table2.date
    FROM Table1 INNER JOIN Table2 ON Table1.id = Table2.id
    GROUP BY Table1.car, Table2.date
    ORDER BY Count(Table2.id) DESC;

    The TOP 10 part is the code you are looking for to get the latest 10.


Advertisement