Unmatched query

I need help with a query that sorts out unmatched fields from two
tables. I've tried with an union query but it matches my duplicates as
well... I want my duplicates to be unmatched. Any suggestions?

Table 1
Date Account Amount
2007-02-13 123 300
2007-02-13 456 500
2007-02-13 111 100
2007-02-13 111 100 ( I want this duplicate to be unmatched)
2007-02-14 222 200
2007-02-14 555 650

Table 2
Date Account Amount
2007-02-13 123 300
2007-02-13 456 450
2007-02-13 111 100
2007-02-13 333 700
2007-02-14 222 200
2007-02-14 444 250

SELECT T1.Account, T1.Amount, T2.Account, T1.Amount
FROM T1 LEFT JOIN T2 ON (T1.Account=T2.Account) AND
(T1.Amount=T2.Amount)
UNION SELECT T1.Account, T1.Amount, T2.Account, T1.Amount
FROM T2 LEFT JOIN T1 ON (T1.Account=T2.Account) AND
(T1.Amount=T2.Amount);

There may be a confusion about the terminology...

An "unmatched" query is one which finds values in one table that don't exist
(i.e., are "unmatched") in another table.

Are you saying you wish not to see duplicate values?

Regards

Jeff Boyce
Microsoft Office/Access MVP

wrote in message
ups.com...
I need help with a query that sorts out unmatched fields from two
tables. I've tried with an union query but it matches my duplicates as
well... I want my duplicates to be unmatched. Any suggestions?

Table 1
Date Account Amount
2007-02-13 123 300
2007-02-13 456 500
2007-02-13 111 100
2007-02-13 111 100 ( I want this duplicate to be unmatched)
2007-02-14 222 200
2007-02-14 555 650

Table 2
Date Account Amount
2007-02-13 123 300
2007-02-13 456 450
2007-02-13 111 100
2007-02-13 333 700
2007-02-14 222 200
2007-02-14 444 250

SELECT T1.Account, T1.Amount, T2.Account, T1.Amount
FROM T1 LEFT JOIN T2 ON (T1.Account=T2.Account) AND
(T1.Amount=T2.Amount)
UNION SELECT T1.Account, T1.Amount, T2.Account, T1.Amount
FROM T2 LEFT JOIN T1 ON (T1.Account=T2.Account) AND
(T1.Amount=T2.Amount);

On 15 Feb, 00:42, "Jeff Boyce" wrote:
There may be a confusion about the terminology...

An "unmatched" query is one which finds values in one table that don't exist
(i.e., are "unmatched") in another table.

Are you saying you wish not to see duplicate values?

Regards

Jeff Boyce
Microsoft Office/Access MVP

wrote in message

ups.com...



I need help with a query that sorts out unmatched fields from two
tables. I've tried with an union query but it matches my duplicates as
well... I want my duplicates to be unmatched. Any suggestions?

Table 1
Date Account Amount
2007-02-13 123 300
2007-02-13 456 500
2007-02-13 111 100
2007-02-13 111 100 ( I want this duplicate to be unmatched)
2007-02-14 222 200
2007-02-14 555 650

Table 2
Date Account Amount
2007-02-13 123 300
2007-02-13 456 450
2007-02-13 111 100
2007-02-13 333 700
2007-02-14 222 200
2007-02-14 444 250

SELECT T1.Account, T1.Amount, T2.Account, T1.Amount
FROM T1 LEFT JOIN T2 ON (T1.Account=T2.Account) AND
(T1.Amount=T2.Amount)
UNION SELECT T1.Account, T1.Amount, T2.Account, T1.Amount
FROM T2 LEFT JOIN T1 ON (T1.Account=T2.Account) AND
(T1.Amount=T2.Amount);- Dölj citerad text -

- Visa citerad text -

Sorry for the confusion.... I want an unmatched query that sorts out
the "unmatchad values" in my tables. The problem is that, for example,
T1 contains dupicate values that in my query matches only one value in
T2. In this example I want only one of the duplicates in T1 to match
the value in T2. I don't want to eliminate the duplicates.

Table 1
Date Account Amount
2007-02-13 111 100
2007-02-13 111 100 ( I want this duplicate to be unmatched)

Table 2
Date Account Amount
2007-02-13 111 100

Result with my "unmatched query"
(Values from T1) (Values from T2)
Date Account Amount Date Account Amount
2007-02-13 111 100 2007-02-13 111 100
2007-02-13 111 100 2007-02-13 111 100 ( my query
matches the duplicates from T1 with my value in T2 x2 )

I want this result...

(Values from T1) (Values from T2)
Date Account Amount Date Account Amount
2007-02-13 111 100 2007-02-13 111 100
2007-02-13 111
100 (-the second value in
T1 as an unmatched value)


Regards

Henrik J

Right off the top of my head, I can't imagine how to tell Access to match
the first record it finds that matches, but not to match any others that may
be duplicated in T1. After all, if they are duplicated in T1, how do you
know which one to use in attempting to match to T2?

You've described a "how", as in how you are trying to do something. If you
describe a bit more about the "what" and "why" (i.e., the underlying
business need that you are attempting to solve), the newsgroup readers may
be able to offer alternative ways to get the job done...

Regards

Jeff Boyce
Microsoft Office/Access MVP

wrote in message
ups.com...
On 15 Feb, 00:42, "Jeff Boyce" wrote:
There may be a confusion about the terminology...

An "unmatched" query is one which finds values in one table that don't
exist
(i.e., are "unmatched") in another table.

Are you saying you wish not to see duplicate values?

Regards

Jeff Boyce
Microsoft Office/Access MVP

wrote in message

ups.com...



I need help with a query that sorts out unmatched fields from two
tables. I've tried with an union query but it matches my duplicates as
well... I want my duplicates to be unmatched. Any suggestions?

Table 1
Date Account Amount
2007-02-13 123 300
2007-02-13 456 500
2007-02-13 111 100
2007-02-13 111 100 ( I want this duplicate to be unmatched)
2007-02-14 222 200
2007-02-14 555 650

Table 2
Date Account Amount
2007-02-13 123 300
2007-02-13 456 450
2007-02-13 111 100
2007-02-13 333 700
2007-02-14 222 200
2007-02-14 444 250

SELECT T1.Account, T1.Amount, T2.Account, T1.Amount
FROM T1 LEFT JOIN T2 ON (T1.Account=T2.Account) AND
(T1.Amount=T2.Amount)
UNION SELECT T1.Account, T1.Amount, T2.Account, T1.Amount
FROM T2 LEFT JOIN T1 ON (T1.Account=T2.Account) AND
(T1.Amount=T2.Amount);- Dölj citerad text -

- Visa citerad text -

Sorry for the confusion.... I want an unmatched query that sorts out
the "unmatchad values" in my tables. The problem is that, for example,
T1 contains dupicate values that in my query matches only one value in
T2. In this example I want only one of the duplicates in T1 to match
the value in T2. I don't want to eliminate the duplicates.

Table 1
Date Account Amount
2007-02-13 111 100
2007-02-13 111 100 ( I want this duplicate to be unmatched)

Table 2
Date Account Amount
2007-02-13 111 100

Result with my "unmatched query"
(Values from T1) (Values from T2)
Date Account Amount Date Account Amount
2007-02-13 111 100 2007-02-13 111 100
2007-02-13 111 100 2007-02-13 111 100 ( my query
matches the duplicates from T1 with my value in T2 x2 )

I want this result...

(Values from T1) (Values from T2)
Date Account Amount Date Account Amount
2007-02-13 111 100 2007-02-13 111 100
2007-02-13 111
100 (-the second value in
T1 as an unmatched value)


Regards

Henrik J