Relating records in a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a question about queries in an Access Database, I'm using MS office
2000.

I have this following data and I want to find a query that will show me any
records that overlap or don't match:
HoleID From To
GCD0600 0 1
GCD0600 1 2
GCD0600 1.9 3
GCD0600 3 3.5
GCD0600 4 5
GCD0601 0 1
GCD0601 1 2
GCD0601 2 3
So with the above information the query's result should show two records the
one with 1.9 in the "from" field (because it overlaps with the "to" field of
the previous record) and the one with 3.5 in the "to" field (because it
doesn't match the "from" field of the next record).

There are no records in the table that have the same HoleID+From (these two
fields combined are unique).

How do I do make this query? Also, I'm new to Access so please explain in
English as I don't speak visual basic!!! LOL :-)

Thanks for any feedback.
 
Hi,


FROM is a reserved word.



SELECT a.holeID, a.start, a.end, b.holeID, b.start, b.end
FROM myTable As a INNER JOIN myTable As b
INNER JOIN a.start <= b.end AND a.end >=b.start AND a.holeID < b.holeID



There is NO overlap if a.start > b.end OR if a.end < b.start

(since a starts after the ending of the other or ends before the start of
the other).

To have overlap, negate the sentence, using De Morgan law, to get

a.start <= b.end AND a.end >= b.start


the additional condition, a.holeID < b.holeID, is to break symmetry: If
A overlaps B, then B overlaps A. We only want one mention, not two, so we
decide to only keep the mention where A overlaps B, not the mention that B
overlaps A (in case there is overlapping).




Hoping it may help,
Vanderghast, Access MVP
 
Back
Top