Access database help!

[harper1]

I have an Access database which is for a car hiring company. The main table 'Rentals' has cust_no, vehicle_no, startdate, enddate

I want to rank the vehicles by popularity - ie. the car which is in the most records should be ranked top. I have tried using Count in a query but not sure how to do it.

Anybody got ideas?

 

[biojo]

count should add up all records in that filter , e.g vehicle_number

 

[18tooo]

havent used access in aaaaaages, but know me sql and i think if youre in query design view, you can right click in the top part and choose 'SQL View'

then this *should* work:

SELECT Rentals.vehicle_no, COUNT(Rentals.vehicle_no)
FROM Rentals
GROUP BY Rentals.vehicle_no;

and if you want the most popular at the top of the list and so on then add this after the GROUP BY line:

ORDER BY COUNT(rentals.vehNo) DESC

obviously if you want to get the actual name of the vehical rather than just the number and the name is in another table then you need a join in there too

 

[harper1]

This works but as soon as I add in some other fields, ie to narrow the query down by date it doesnt. I just get one count for each different date. So count is always 1.

 

[18tooo]

hmm, should work fine, after adding say a date range to it, you should have something looking like this:

SELECT Rentals.vehicle_no, COUNT(Rentals.vehicle_no)
FROM Rentals
WHERE startdate BETWEEN #1/1/2008# AND #1/31/2008#
GROUP BY Rentals.vehicle_no;
ORDER BY COUNT(Rentals.vehicle_no) DESC;

or at least i'm pretty sure thats how access does dates if me memory serves me right, i also believe they also have to be in month/date/year format.

 

[harper1]

Each car has a unique number i.e. 00001 00002 00003 (vehicle_no)
Each car has a name i.e. Renault Clio (vehicle_name)

I want to find out the most popular model. For instance I have ten Renault Clio cars for hire, each having a unique vehicle_no. I want to find out popularity based on vehicle_name. However this field is not included in the Rentals table, only vehicle_no is, so I get a different count for each unique vehicle. I now need to group all of the Clio's together, all of the Corsa's together etc.

I hope this is making sense!

 

[18tooo]

yep, that makes sense...

supposing you have a unique ID for each rental (in this case rental_no) then this should do the job:

SELECT Vehicles.vehicle_name AS 'Vehicle Name', COUNT(Rentals.rental_no) AS 'Times Rented'
FROM Vehicles INNER JOIN Rentals ON Vehicles.vehicle_no=Rentals.vehicle_no
WHERE startdate BETWEEN #1/1/2008# AND #1/28/2008#
GROUP BY Vehicles.vehicle_name
ORDER BY COUNT(Rentals.rental_no) DESC;

have also added the 'AS' bits to make the results table look nicer

 

[harper1]

Thanks that has worked
Any chance you can explain what youve done there to me?