Relating records in a query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Lucy - 31 May 2005 07:31 GMT

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.

Reply to this Message

Michel Walsh - 31 May 2005 13:47 GMT

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

>I have a question about queries in an Access Database, I'm using MS office
> 2000.
[quoted text clipped - 26 lines]
>
> Thanks for any feedback.

Reply to this Message