ive got two tables that i need to merge.

I need the join to work so a date within table b links to a range in table a of 2 columns start and end- is it possible?

Ie
Table A
patient number
admitted date
discharged date
etc
etc

Table B
Patient number
operation date (will lie in admit dis date)

NB a patient number may exist in table A and not B, or table A twice at differnt times and B once.

thanks, ren

are you trying to put the operation date into tblA?

put a new field into tblA called OperationDate.

UPDATE tblB INNER JOIN tblA ON tblB.PatientID = tblA.PatientID SET tblA.OperationDate = [tblB].[OperationDate]
WHERE (((tblB.OperationDate) Between [tblA].[AdmitDate] And [tblA].[DischargeDate]));
try that first with *copies* of your tables (although you could always delete the new field). hope that's what you're looking for.