query SQL question? NEWBIE

[arran1180]

I am using a SQL query with an inner join to compare/display 2 tables where
the field "cash amount" are similar (ie - two tables, diplaying only those
records where the cash fields are within 0.01 of each other). Each record in
each table has a primary key (different in each table).

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;

which works fine for what i need it, but i am trying to add in a clause
where the query will only join/return the data if they also occur on the same
date.
EG
table 1 has $100 on 1st Jan and $100 on 2nd Jan
table 2 has $100 on 1st Jan

the current query uses matches the table 2 ref to both table one refs - i
really need it to only match to the one on the same date!

This is my first foray into SQL, and have tried a few things but nothing
seems to work!

thanks in advance

 

[Brendan Reynolds]

You can use AND and OR in your JOIN clause, just as you would use them in,
for example, a WHERE clause ...

ON
Abs(tbl_CSFB_PB_tmp.SumofCashAmount-qry_CSFB_PB_ImagineTotalsAmended.ICash)<0.01
AND tbl_CSFB_PB_tmp.DateFieldName =
qry_CSFB_PB_IMagineTotalsAmended.DateFieldName

 

[arran1180]

brilliant thanks a lot!

You can use AND and OR in your JOIN clause, just as you would use them in,
for example, a WHERE clause ...

ON
Abs(tbl_CSFB_PB_tmp.SumofCashAmount-qry_CSFB_PB_ImagineTotalsAmended.ICash)<0.01
AND tbl_CSFB_PB_tmp.DateFieldName =
qry_CSFB_PB_IMagineTotalsAmended.DateFieldName