Unmatch return query

[Carlos]

Hello, i newbie in queries and try the unmactch query but only one field is
compare
I have 2 tables (T1,T2) with identical fields

Emp NUMBER
StartDate DATE
EndDate DATE

Ex.

T1

| Emp | StartDate | EndDate |
| 100 | 2006-01-02 | 2006-01-10 |
| 100 | 2006-05-02 | 2006-05-10 |(1)
| 100 | 2006-07-02 | 2006-07-10 |(2)

T2

| Emp | StartDate | EndDate |
| 100 | 2006-01-02 | 2006-01-10 |
| 100 | 2006-07-02 | 2006-07-09 |(2)

(1) not exist T2
(2) not same EndDate

Return

| Emp | StartDate | EndDate |
| 100 | 2006-05-02 | 2006-05-10 |
| 100 | 2006-07-02 | 2006-07-10 |

need to compare StartDate an EndDate with same Emp number, it's possible

Thank's

 

[Allen Browne]

Carlos, the logic is that you have an overlap if:
- A starts before B ends, AND
- B starts before A ends, AND
- the Emp number is the same.

You could build a query like that which does give you the matches. Then use
it as an input "table" for your unmatched query.

 

[Carlos]

Thank's Allen for your replay

but i not follow your , i new to access querys and i not see your workaround
to solve my problem,
if you give an exemple i been thankful

thank's

 

[Allen Browne]

The idea is to create a query using both the T1 and T2 tables.
Join them on the Emp field.

In the Criteria row under T1.StartDate, enter:
< T2.EndDate

In the Criteria row under T2.StartDate, enter:
< T1.EndDate

This gives you the overlapping records.

 

[John Spencer]

SELECT T1.*
FROM T1 LEFT JOIN T2
ON T1.Emp = T2.Emp and
T1.StartDate = T2.StartDate and
T1.EndDate = T2.EndDate
WHERE T2.Emp is Null

In the unmatched query that you already have, you can double click on the
join line between the emp fields and observe the join choice.
Create a join between startdate fields (drag from one to the other) and them
double click that line and make the same choice
Repeat for endDate.

The simplest method is to just enter the SQL into the SQL window (menu bar
View: SQL)

 

[Carlos]

Thank's John it works in the perfection

SELECT T1.*
FROM T1 LEFT JOIN T2
ON T1.Emp = T2.Emp and
T1.StartDate = T2.StartDate and
T1.EndDate = T2.EndDate
WHERE T2.Emp is Null

In the unmatched query that you already have, you can double click on the
join line between the emp fields and observe the join choice.
Create a join between startdate fields (drag from one to the other) and
them double click that line and make the same choice
Repeat for endDate.

The simplest method is to just enter the SQL into the SQL window (menu bar
View: SQL)