Finding subsequent codes

[Rixxe]

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.)

[weekend00]

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)

can you explain more about what you want to get? and examples are very appreciated.

and more details of your tables/fields

[Rixxe]

In more detail (hopefully):

I need to find all the patients that have a code (drug or read) from TableA before the first code (drug) from TableB, And then a subsequent code from TableA (So a code from Table A again) two weeks after the first code from TableB.

I have split the queries myself, so completing the first part: Find all patients that have a code from TableA before the First code in TableB.

And another query: Find a code from TableA two weeks after the first code from TableB.

Each table contains the same generic fields:
Expr1 (Hex field)
Patid (Patient ID, this is not unique hence the Hex field)
Eventdate (Date when the drug was used)
Code (The drug code itself)


DateA and DateB are the eventdates for TableA and TableB, but in date format.

To link the full attempts at this:

SELECT [TableA].Expr1, [TableA].patid
FROM [TableA] INNER JOIN [TableB] ON [TableA].Expr1 = [TableB].Expr1
WHERE (((DateDiff("d",[DateA],[DateB]))>=14))
GROUP BY [TableA].Expr1, [O3TableA].patid;

And

SELECT [TableA MINDATE].Expr1, [TableA MINDATE].patid
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, [TableB MINDATE].patid;

The first query should find all codes from TableA that are before the first code for TableB.

The second query should find all the codes from TableA that are 2 weeks after the First code for TableB.

What it might look like when complete: (EDIT: It didnt actually copy the way i wanted, but hopefully you can get the idea!)


I don't need all the fields, infact i have only added Expr1 and Patid to the group by query because they are all that i need to see.

Hopefully this gives you a better idea of the scale of the problem, and the task i am facing! (Considering i have basically searched this site for most of the code i am using!)

Thanks again, and if i can give you even more information let me know.

Cheers

Rixxe

[weekend00]

In first query:
what is EXPR1? why do you join on this field? I was expecting you join on code.
you want to find all codes from TableA that are before the first code for TableB,
but you don't have [code] in you query (select field or criteria)

[Rixxe]

Expr1 is the patid field but in a unique state, as the patid isn't unique.
The codes from TableA and TableB are different, its for research purposes:
How many patients have had a drugA before drugB and so forth. As I want to find the number of patients not the codes themselves I am linking the tables via Expr1.

I should have mentioned that TableA only contains drugA and TableB only contains drugB. Sorry for that.

There are the same patients, and therefore patid and Expr1 in both tables.

Hope this helps.

[weekend00]

I would like to know how expr1 is unique, I am feeling there is no matching expr1 in tableB with tableA.

I still think you should join on patid and ignore expr1.

example:
tableA
patid drugAdate
1 1/11/10
1 2/21/10
2 3/10/10
2 2/10/10

TableB
patid drugBdate
1 1/1/10
1 5/1/10
2 5/5/10

for Query1, do you expect following result(maybe you want more fields):
patid drugAdate
2 3/10/10
2 2/10/10

for query2:
patid drugAdate
1 2/21/10

are these what you want?

[Rixxe]

Expr1 is unique, it takes into account all the 'hidden' fields from each record, even if they have the same patid and gives them a unique alphanumerical value.

I have already tried with patid, and i get the same issue. My query doesn't get the correct code from TableB.

Not sure if the MINDATE really works, or if there is a better way to get the earliest date.

[weekend00]

get mindate for tableb:
query: tableBMinDate
select patid,mid(drugbDate) as minDate from tableb group by patid

less than Bmindate:
query1:
select tablea.* from tableA left join tabeBMinDate on tableA.patid=tablebMinDate.patid where tableA.drugDate<tablebmindate.minDate or tablebmindate.minDate is null

query2:

select tablea.* from tableA inner join tabeBMinDate on tableA.patid=tablebMinDate.patid where tableA.drugDate > tablebmindate.minDate+14

[Rixxe]

With a bit of tinkering here and there, using your example i have managed to make it work!

Thank you for your help.