Akinia wrote:
Hi every body
I've a little problem with my query, I can't figure it out. I've three
tables:
Table A (EmployeNr, Date, Code)
Table B (EmployeNr, Date, Code) Which is an historic of Table A
Table C (BegD, EndD)
I can have all the records in table A that doesn't match in table B
but not from the other side.
Table A Table B Table C
0001 0001 20031001
20031002 20030930 20031031
PR1 PR1
0001 0001
20031003 20031001
PR1 Mal
0001
20031003
PR1
The result expected is:
0001
20031001
PR1
Due to the difficulty of determining your example I wonder if PRI should
be Mal or PRI.
0001
20031002
PR1
I need to get all the records In table A which aren't in Table B
but also all the records in table B which aren't in table A but
matching with the period in table C.
Any suggestion will be greatly appreciated.
Create an unmatched query (Query/New/FindUnmatched) for TableA recs not in
TableB. Note your field names and their positions when you save the
query.
Now create an unmatched query (Query/New/FindUnmatched) for TableB recs
not in TableA. The field names, position and count should match those in
the first query. If this query does not contain a field contained in
Query1, you can always enter something like Expr1 : ""
Now modify this second query. Add TableC to it. Create a relationship
line between the date range fields of Table2 and Table3 if it does not
exist. While in desing mode of the second query, from the menu select
View/SQL. Highlight and copy the SQL to the clipboard.
Now open Query1 in design mode, select ViewSQL, remove the semi-colon at
the end, and enter
UNION ALL
and then paste the SQL statement from Query2.
Save and run.