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!
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!