Getting Duplicates when running a select querie

[MarkNie]

Hi

I hope someone can give me a hand with this one:

I have to tables tblJPM & tblBNY I am trying to match the records in the tables with a querie. The problem is that when I run the querie which just call all the records from tblJPM and just the ones from tblBNY that match tblJPM it duplicates the records.

There are more than 1 record with the same information so it looks like when there is more than one record with the same information it duplicates the records:

tblBNY
Record 1 Acc code = 130271 Acc name = BNY
Record 2 Acc code = 130271 Acc name = BNY

tblJPM

Record 1 Acc code = 130271 Acc name = BNY
Record 2 Acc code = 130271 Acc name = BNY

When I run the querie it will match them up but duplicate the ammount so the querie will show:

Querie:

Record 1 Acc code = 130271 Acc name = BNY
Record 2 Acc code = 130271 Acc name = BNY
Record 1 Acc code = 130271 Acc name = BNY
Record 2 Acc code = 130271 Acc name = BNY

Do you know why it does this and how to get around it.
Your help will be greatly appreciated.

Thanks
Regards
Mark

 

[lynchg]

Select Distinct.... (continue with the rest of your query)

Add the Distinct keyword and it will eliminate te duplicate records in your output.

 

[MarkNie]

Hi lynchg

Here is the querie where do I put the Select Distinct in?

SELECT tblBNY.[Tracking ID], tblBNY.[ACCT NO], tblBNY.[ACCOUNT TITLE], tblJPM.[Tracking ID], tblJPM.ActCd, tblJPM.ActNm
FROM tblBNY INNER JOIN tblJPM ON tblBNY.[ACCT NO] = tblJPM.ActCd;

Thanks for the quick response

Regards
Mark

 

[MarkNie]

Hi lynchg

I got that right but now it is only showing 1 record and there are suppost to be 2 with the same number.

Do you know of anyway to do that.

Thanks
Mark

 

[lespaul]

I think you want something like this:

SELECT DISTINCT tblBNY.[Tracking ID], tblBNY.[ACCT NO], tblBNY.[ACCOUNT TITLE] FROM tblBNY
UNION
SELECT DISTINCT tblJPM.[Tracking ID], tblJPM.ActCd, tblJPM.ActNm
FROM tblJPM;

you can even add to it so you know which table they come from:

SELECT DISTINCT tblBNY.[Tracking ID], tblBNY.[ACCT NO], tblBNY.[ACCOUNT TITLE], "BNY" As Location FROM tblBNY
UNION
SELECT DISTINCT tblJPM.[Tracking ID], tblJPM.ActCd, tblJPM.ActNm, "JPM"
FROM tblJPM;

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[MarkNie]

Hi lespaul

That works fine it is not showing duplicates anymore which is great but now I want to match the 2 tables with each other and that querie is not doing that.

Lets see if I can explain what I want the querie/s to do.

2 tables which have duplicate records in, such as account number. I want to match the 2 tables without there being duplicates as follows:

tblBNY
Record 1 ACCT NO = 230287
Record 2 ACCT NO = 230287

tblBNY

Record 1 ActCd = 230287
Record 2 ActCd = 230287
Record 3 ActCd = 230287

When I Run the querie it should show:

tblBNY tblJPM
ACCT NO ActCd
230287 230287
230287 230287

The 3rd one should not show as there is not a value for it in tblBNY.

The second part is that I want to show all the values in tblBNY that is not in tblJPM and the other way around.

So basically I want to end up with 3 files 1 which is combined values of both tables and the other 2 are the values in each table that is not in the other one.

If you could help with this tricky one it would be great as I have been trying everything I know (which is not that much: lol)

Thanks
Mark