First Post, so i appologies if i don't provide all the information needed.
The query itself looks fairly simple, but i have had huge problems trying to get it to work.
I need to:
Find all patients/records with a code from TableA at any time before the FIRST code from TableB, and a subsequent code from TableA >2 weeks after code TableB and before 20100604. (04/06/2010)
To solve this:
I created one table that includes all codes from TableA = TableA, and i made another table that includes all codes from TableB = TableB.
I then created the query below; <0 to show codes before TableB and then >14 to show codes 2 weeks after TableB.
SELECT [TableA].Expr1, [TableA].patid INTO [Table2 <0]
FROM [TableA MINDATE] INNER JOIN [TableB MINDATE] ON [TableA MINDATE].Expr1 = [TableB MINDATE].Expr1
WHERE (((DateDiff("d",[DateA],[DateB]))<0))
GROUP BY [TableA MINDATE].Expr1, [TableA MINDATE].patid;
The dates in these tables are usually just text based e.g. 20100604, but for this exercise i have changed them into date format.
Currently, it is not picking up the first code from TableB, it just uses any code. I planned to add in the last date criteria at the end of the query once it works, so thats not a major need.
Hopefully this makes some sense, if you need more information let me know.
Thanks
Rixxe
EDIT: Just a thought, if the first code from TableB was connected to a Null date, it would move on to the next record? Or would it simply just leave that record out all together?
(There are multiple records, some with the same ID hence the use of a Hex field.)