Unique refs only problem..

G

Guest

Posted this a earlier - am convinced its something simple but its driving me
crazy!!!any help really appreciated!!,

the Query seems to run fine at the moment (apart from the dupes). Please see
sample data below: info is from one table an one qry:, both come from
different external sources and i am trying to reconcile the two...

tbl_CSFB_PN_tbl:
fields: SumofCashAmount, ccy, SettlementDate,description,Ckey
Ckey is the primary key assigned to the data upon import into the database
using autonumber
100.00,USD,4th-Jan-05,itemdescription1,400001
100.00,USD,4th-Jan-05,itemdescription2,400002

qry_CSFB_PB_ImagineTotalsAmended:
ccy,Date,trdid (this is a non-unique reference),ICash,Ikey
Ikey is the primary key assigned to the data upon import into the database
using autonumber
USD,4th-Jan-05,ref10001,100.00,59840

Currently the SQL query will return records from the above where "Icash"
matches the "SumofCashAmount" (within 0.01) AND where the dates are equal.

Problem is in the above example the query will return the records with Ikey
59840 twice - matching it against both Ckey refs from the table. I need to be
able to just use each record once so i can compare back to the original
tables to see what is unmatched. it does not matter which ikey/ckeys are
matched (as long as they have same cash and date), just that they are only
used once.

any help greatly appreciated - let me know if you need any more info
thx
 
G

Guest

thanks Jerry,

the SQL i am using now is below

SELECT DISTINCT tbl_CSFB_PB_tmp.*, qry_CSFB_PB_ImagineTotalsAmended.*
FROM tbl_CSFB_PB_tmp INNER JOIN qry_CSFB_PB_ImagineTotalsAmended ON
Abs(tbl_CSFB_PB_tmp.SumofCashAmount-qry_CSFB_PB_ImagineTotalsAmended.ICash)<0.01
And (tbl_CSFB_PB_tmp.SettlementDate)=(qry_CSFB_PB_ImagineTotalsAmended.Date);

please let me know any advice!
thx
 
Top