More Complex queries

[stuey]

Looking to Create a complex query which I have solved in the first instance by using a report but I would rather do this in my query.

My query contains 4 pieces of info

name, time1, time2, time3, overall time

I want add a rank on all the times by smallest time first then sort by overall time.

does anyone have ANY clues I can't even create a rank in the field I sort by

stuart

 

[Jack Cowley]

If your times are in separate fields in a single record in your table then you need to normalize your data. Once you do that sorting/ranking your data will be much easier.

hth,
Jack

 

[stuey]

I thought the whole point of normalisation was to cut out duplicate data

Secondly the fields I want to ranking on are actually calculated fields does this help or degrade matters

stuart

 

[Jack Cowley]

You are correct about duplicate data, but having time in a single field is what you want. If, for example, you were entering time for when an employee worked you would not want a field for every day of the week, but a single field linked to a table with an ID and TimeWorked field. Excel works across rows but Access doesn't (at least not easily) so you want your data in a 'column'. You should be able to do your calculations and get your ranking, but I admit to speculating a bit about that as I don't know how you are doing it, but a query or two should get you the answers you need.

Jack

 

[stuey]

Well basicaly for every athlete

I have a start time, point 1, point 2, finish time and I work out the bit in between.

I want to rank the bits in between

 

[Jack Cowley]

Take a look here.

Jack

 

[stuey]

thanks