R.E

[Guest]

Hi
I am hoping someone would be kind enough to look at following query. I have
spent many hours going through the help section of Access but I cannot find
solution. I am trying to analyse a theory for a book I wish to write

I am using Microsoft Access 2002 and I have built a small data base for
horse racing.
The best way to explain my query is to give you a real example

Field 1 DATE Field 2 HORSE NAME Field 3 DIST OF RACE Field4
WEIGHT HORSE CARRIED Field 5 POSITION HORSE RAN

1/03/04 Mr X 1200
55kg
3rd
20/03/04 Mr T 1300
54kg
10th
28/03/04 Mr X 1100
52kg
1st
10/ 04/04 Mr X 1400
58kg
4th

What I was needing assistance for,is that I cannot work out the query
formula to use in the Criteria section of Design View Query, to
Obtain a list of say Mr Xâ€s everytime it runs 1st and showing its distance
and weight increase or decrease from its immediate previous run only.
So in example above Mr X Ran 1st on the 28/03/04, But I would like to see if
horse went up or down in distance and up or down in weight from its run on
the 1/03/04.

In other words is there a formula I can use to compare against the previous
or even the next record of a selected record eg the selected record being
where a horse runs 1st

Hopefully there is a formula which I can use in Access to do this .

I hope above makes sense

Thanks
Boko

 

[[MVP] S.Clark]

Finding a horse that got first place for an event is easy.
Select Horsename, Weight from Races where Date = #...# and Position = "1st"
and RaceNo=1

Once you find that Horsename, you than have to go find all of the other
races in which that horse was first. Again, very easy.

Select Horsename from Races where Date <> #...# and Position = "1st" and
RaceNo<>1

The problem with doing two queries is that they aren't combined. So,
instead of a SELECT query, use a couple of APPEND queries instead to write
the data to another table.

Once the data is there, you can start to apply the magic. Such as comparing
the weight deltas, etc. I would probably do this in VBA code, by stepping
through the data and writing the values in the records.