Query returning too many records

[123Olaf]

Hello, I need Help with my query. I built 2 tables: Table 1: sub account number, account name, amount paid (Record one: abc, Tree, 100; Record two: abc, Tree, 200) and Table 2: account number, sub account number, account name (Record one: 1122, abc, Tree; Record Two: 3344, acb, Tree). I joined them by sub account number and account name. I was looking to get the following answer: Query2 Account Number Sub Account Number Account Name Amount 1122 abc Tree 100 3344 abc Tree 200
but got additional records that are incorrect.
Query2 Account Number Sub Account Number Account Name Amount 3344 abc Tree 100 1122 abc Tree 100 3344 abc Tree 200 1122 abc Tree 200
How can I get read of theses additional records?
Thank you for your responses.

 

[GinaWhipp]

What JOIN are you using, I suspect that is the cause of the issue.

 

[The_Doc_Man]

Concur with Gina's question/comment. If you have a

SELECT {many fields} FROM tblA, tblB

you have specified a Cartesian JOIN, which is like a matrix product derived from multiplying vectors. The correct syntax would be

.... FROM tblA INNER JOIN tblB ON tblA.SubAccountNumber = tblB.SubAccountNumber ....

or something like that.

 

[123Olaf]

The Doc_Man and GinaWhipp,
Thank you for your responses. I tried the syntax you wrote and my results did not change. I still have wrong values.
My SQL is as follows:

SELECT Table2.[Account Number], Table1.[Sub Account Number], Table1.[Account Name], Table1.Amount
FROM Table1 LEFT JOIN Table2 ON (Table1.[Sub Account Number] = Table2.[Sub account number]) AND (Table1.[Account Name] = Table2.[Account Name]);

This is a mock up query. But it is an accurate reproduction of my issue. The tables are imported from 2 different sources and missing any/all unique values or anything that I could potentially use as a unique value. I have to come up with a work around.
Thanks again, your help is really appreciated.

 

[GinaWhipp]

Your issue is there is no PK to FK relationship in the query so you are getting all records from Table1 that have matching records in Table2. If this is your table set up then you need to modify so you can relate one-to-many (PK-to-FK) and get the results you want.

 

[123Olaf]

Gina Whipp,
Thank you very much for your response. I really appreciate it.
I agree with you regarding the issue that I am missing a unique identifier in my Table #2. Since I can't re-build this table. I was just thinking... What if I will build a new query prior to the query that I am having an issue with. That query could look at/analyse a sub account number and account name combination pulled from Table #2 and if there is a match the query will assign a unique number that will be placed into a separate field. Then I will bring this number into my original query and will preform the matching here. Would this resolve the problem? What is your opinion on this?
The issue is that I do not know how to complete the coding for this query that could look at/analyse a sub account number and account name combination and if there is a match the query will assign a unique number that will be placed into a separate field. :-(. Could you possibly help me with this?

Thank you again for all your help,
Olaf.

 

[jdraw]

I agree with Doc man and Gina.
However, this may not be a show stopper

Since I can't re-build this table

We need to see some sample data, say 10-12 records from each of table1 and table2, and your best guess at what the 'expected result' should be.

You may be able to build some temporary tables, or queries to get the structure you need to get your result.

Good luck.

 

[GinaWhipp]

Yep, I can help but as JDraw says we need some sample data and then what you expect to see.

That said, why can't you rebuild the table? If you are linking to it then you should be able to get the PK.

 

[arnelgp]

linking on [sub account number] will suffice your query:

SELECT Table2.[Account Number], Table2.[Sub Account Number], Table2.[Account Name], Table1.Amount
FROM Table2 LEFT JOIN Table1 ON Table2.[Sub Account Number] = Table1.[Sub account number];

whether you have pk or not as long as you are joining on same datatype.

use Aggregate query (Total) to group them.