Finding a particular record in a query

  • Thread starter Thread starter John Johndon
  • Start date Start date
J

John Johndon

I have a table that is made up of a list of people with various attributes
(fields) for each person. The people have a field that is labeled "team",
since the people came from either team A or team B...I am writing a query to
extract certain people from the list. One of the things I want to do is to
find the nearest person on the list from the other team e.g. if I want the
person who was 16th on the list, how do I find the nearest person (above or
below) on the list from the other team?

TIA

JJ
 
One of the things I want to do is to
find the nearest person on the list from the other team e.g. if I want the
person who was 16th on the list, how do I find the nearest person
Click to expand...

How is "nearest" defined? If you are assuming that the order of names
in the table is usable, I'm sorry to say that it is not; a Table is an
unordered "bucket" of records, and there is no record number or any
controllable way to define two records as being "near".

If you have some field within the table that provides such a sequence,
you can probably use DMin() on the difference between the two people's
sequence numbers, but I'm not sure quite how you're defining "near"!
 
Sorry I wasn't clear about that John....

One of the fields is "team number" from 1 to 100 lets say, so when I say
nearest, I meant the person from the other team, who has the overall number
(field "team number") nearest to the person we are interested in....

Can a report have fields from two different queries?


TIA

JJ
 
Back
Top