comparing tables unique records only!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am comparing two tables, the only silmar criteria are fileds for cash
amount and date. Both tables have primary keys thier records.
I am trying to design a qeury that will dislay only records that match in
both tables.
Currently my query works pretty well, matching records that have silimar
amounts and dates.
My problem is that i only want to use each record once - i cannot allow dupes!
for example
table 1 has 2 records of $100 on 1st Jan (but different primkeys)
table 2 has 1 record of $100 on 1st Jan (single unique primkey)

Currently my qeury uses the info from table 2 to match the table 1 data
twice, i want my query to only match one of these records - ie no dupes of
primary keys used! this example can happen either way round - sometimes
table 2 will have more similar records...
Is this possible to do in Access? is there something i can do to the query
to eliminate any dupes?
my current SQL is below - any help really appreciated!

SELECT 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);

thx!
 
I've tried using the DISTINCT keyword in my code but i am still getting
dupes/the same result ... SQL is pretty new to me - have i entered the
statment correctly or is there a way that i need to specify fields from the
tw tables?

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);
 
Back
Top