Matching using unique records only!!

[arran1180]

I currently have a query that compares data between two tables, effectively
matching a similar numerical field in both tables to create a new one using
an INNER JOIN function.
Is there a way i can get the query to only use the data from each table
once? As in ONE entry in one table is mathced against ONE entry in the other.
Currently where table A has two similar entries but with different references
they are returned in the query matched off against the same data from table
B. My goal is to highlight any data that is unmatched, so i need tp eliminate
this problem!
I am an SQL beginner, but the code is below: (select from / inner join)

SELECT qry_CSFB_PB_trd_ref_totals.*, qry_CSFB_PB_ImagineTotalsAmended.*
FROM qry_CSFB_PB_trd_ref_totals INNER JOIN qry_CSFB_PB_ImagineTotalsAmended
ON
Abs(qry_CSFB_PB_trd_ref_totals.SumofCashAmount-qry_CSFB_PB_ImagineTotalsAmended.ICash)<0.01;

any help greatly appreciated!

 

[TC]

It's really not possible to comment sensibly on SQL, unless you list
the main fields for each table, and state the primary key field(s) for
each table. Give us that info, then we can comment.

HTH,
TC

 

[arran1180]

apologies! - the actual SQL query i provided is bases upon two other queries
(not tables) linked to two separate xls files. So i dont believe that a
primary key has been defined (in the raw data there is no one field that does
not contain dupes)?

please see info on the two base queries below - (hope its not overkill on
what may require)

1. qry_CSFB_PB_trd_ref_totals
SumofCashAmount
ccy
SDate
CSFBref - data in the query is grouped by this to provide the
"sumofCashAmount" field, so in this query all these refs are unique

2 qry_CSFB_PB_imagineTotalsAmended
ImagineID
Date
secSymbol
ICash
(each entry here is unique)

Basically i am trying to match up ICash to SumofCashAmount (within 0.01),
but only using each record once with an aim of reconciling the two queries
and highlighting entries that do not match. The current SQL query finds
matches fine in the correct range, but will count the same entry twice if
that will also provide a match

hope this is clearer!

 

[TC]

The queries are irrelevant, if the tables don't have primary keys.

You need to state the primary key of each table. If you do not know (or
have not defined) those primary keys, you have no chance of getting the
SQL to work properly. All your further activities are bound to end in
frustration.

Relational database design, is fundamentally based on the concept of
primary keys. You abslutely positively definitely /must have/ a primary
key for each table.

I suggest that you forget your forms & queries for now, and go back to
the start of the process. Show us the fields in each table, then we can
help suggest some primary keys. /Then/ you can start on the sql!

It's like having a proper foundation for a new building. You /must
have/ a proper foundation. You can';t build on a bed of sand. Your
tables /must have/ porimaryt keys. You can't write reliable SQL if they
don't.

HTH,
TC