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

[Giles Roadnight]

I have a drivers table, a meetings table and a meetings points table.   Some series take the overall score from the best 5 races out of 6, some take the best 21 races out of 29 (just examples, it could be any number out of any number).   So in my view where I select the best x results for each driver I need to be able to say how many top results I want. I won’t be able to pass this to the view so I think I’ll have to do it with qoq   Giles Roadnight http://giles.roadnight.name   -----Original Message----- From: Ellwood [mailto:[EMAIL PROTECTED] Sent: 24 March 2004 11:23 To: [EMAIL PROTECTED] Subject: RE: [ cf-dev ] Compilcated SQL query on access   TOP works fine in Access. I am getting a bit confused now so you may have to run your requirements by me again at some point as I have about 101 things going on in my head. (all those split personailities that wont stop talking).   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 11:18 To: [EMAIL PROTECTED] Subject: RE: [ cf-dev ] Compilcated SQL query on access   But I have no way of knowing how many rounds need to be taken into consideration. Can I pull the top [number] number from a field in access? If not then it’ll have to be done in CF.   I do have MX 6.1   Giles Roadnight http://giles.roadnight.name   -----Original Message----- From: Ellwood [mailto:[EMAIL PROTECTED] Sent: 24 March 2004 11:08 To: [EMAIL PROTECTED] Subject: RE: [ cf-dev ] Compilcated SQL query on access   Correct. But do the query of query inside access. Not CF unless you are using the latest version but even then it is best to get the DB to do what it does best.   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:55 To: [EMAIL PROTECTED] Subject: RE: [ cf-dev ] Compilcated SQL query on access   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