Query To Eliminate Dups? 3

[kjv1611]

Well, I first tried using a LEFT JOIN with "WHERE ... IS NULL" routine, but Jet doesn't seem to like that sort of setup - it would give errors, or just return no results ... 'course it could have been b/c of the joins/where parts I was using.

Then I thought, hey, I'll just use a UNION query to remove the dups. After all, it works really well whenever you have "true" dups.

The problem I have, however, is that THESE dups aren't 100% dups. Let me explain.

I'm taking data on a recurring basis from 2 different sources. One sources has pieces of info that the other source does not have, and vice versa. But they can be for the same people in both lists - at least a small number of them - a couple hundred out of a few thousand records.

Well, here's one thought I just came up with... maybe someone can say, no that won't work, or give suggestions? Any thoughts/examples/references/suggestions appreciated..

My new idea... will see if will work:
First create a table that ONLY includes the fields that BOTH original sources have - name, address, that sort of thing - unfortunately no unique ID is on the informatino at this records at this point.

Next, I'll create a UNION query of THAT table...

Then somehow join the UNION query to the table that has all the data, and then filter the full data table?

But I'm not sure about the last part... I'm afraid I could end up recreating the "duplicates"..

Any thoughts from anyone on this one? I appologize if it sounds spotty. I think my brain is a little spotty this morning.. I need an I.V. of coffee.

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

For now, I think I'm just going to leave off all the additional data, for the purpose of the task at hand, and then look at adding the other data in later on.... unless we determine we just don't need it for our purposes..

--

"If to err is human, then I must be some kind of human!" -Me

 

[BigRed1212]

I'm not real good (actually I'm very bad) at what I think should be an easy task, namely "tell me what is in this list that isn't in that list" (or some variation of that).

I saved this query that PHV wrote and filed it under "extremely helpful". See if this helps you think about it or fix your situation. It works fine in Access so it should be adaptable to whatever you are using.

SELECT Table1.yourField AS Item, 'Table1 only' AS Location
FROM Table1 LEFT JOIN Table2 ON Table1.yourField=Table2.yourField
WHERE Table2.yourField Is Null
UNION SELECT Table2.yourField, 'Table2 only'
FROM Table1 RIGHT JOIN Table2 ON Table1.yourField=Table2.yourField
WHERE Table1.yourField Is Null

 

[kjv1611]

Yes, that IS very interesting indeed! I SHOULD have thought of that, actually. It's using the UNION and LEFT JOIN/NULL tools together in one full set! Man, that seems like an awesome idea! Thanks for sharing! And as always, thanks to the whiz PHV for originally putting it together. [WINK]

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

And actually, double-thanks! That helped me realize something that I was doing wrong. I was being a knuckle head, trying to make the situation more difficult than it was. Rather than comparing each of the data sources with themselves, I was comparing each table to the destination "combined" table, which was duh - why I kept coming up empty!

;p

--

"If to err is human, then I must be some kind of human!" -Me

 

[PHV]

FYI:
thread701-1525538

 

[kjv1611]

Thanks for the reference, PHV.

--

"If to err is human, then I must be some kind of human!" -Me

 

[TheAceMan1]

... and faq701-5721

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[kjv1611]

Thanks for the FAQ link as well... by the way, is there a set in stone way to see who wrote each FAQ. I've seen names attached to some, and of course I can follow links from user history, but how do you tell from within a FAQ every time? Any way?

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Well, after looking at the FAQ you mentioned, AceMan1, I'm not fully seeing where that would work anyway... perhaps I'm just dense. I'll try looking at it again later.. could end up being tomorrow or next week..

--

"If to err is human, then I must be some kind of human!" -Me