RE: [ cf-dev ] Compilcated SQL query on access

[Giles Roadnight]

So I save the query in access and access it like any other table?   Sounds good but the top 5 of 6 thing may change i.e 21 out of 29 is another option.   I suppose I could do a query to get the top 21 then do a query of a query or something like that.     Giles Roadnight http://giles.roadnight.name   -----Original Message----- From: Ellwood [mailto:[EMAIL PROTECTED] Sent: 24 March 2004 10:20 To: [EMAIL PROTECTED] Subject: RE: [ cf-dev ] Compilcated SQL query on access   Best to do this in views in access. i.e. create a query for the sum   select top 5 points from driverpoints where driverid = d.id   then join this with the driver table and run the query from that. It is all possible, just takes a bit of planning and is guaranteed to change once the client sees what is possible.   Ellis C Wood BSc Ellwood Web Solutions   T: 01623 459973 E: [EMAIL PROTECTED] W: http://www.ellwoodwebsolutions.co.uk -----Original Message----- From: Giles Roadnight [mailto:[EMAIL PROTECTED] Sent: 24 March 2004 10:12 To: [EMAIL PROTECTED] Subject: [ cf-dev ] Compilcated SQL query on access   I have a meetings table, a drivers table and a drivers points table. Each driver has points for each meeting. For a series the total points are the best 5 out of 6 scores that a driver has got.   Is it possible to write a query to do this in access?   Something like:   Select sum(select top 5 points from driverpoints where driverid = d.id) as total, driver.drivername From driver d Order by total   I’d test this but I don’t actually have the tables or data yet. I am certain the above won’t work though as you can’t selects with a select statement in access (can you?).   Is there another way of doing this?   Thanks   Giles Roadnight http://giles.roadnight.name