access help- query between 2 tables

[thtadthtshldntbe]

So I created this new database to import and record records from our triage patients in access.

It has 2 tables, based on how the data is stored in the EMR.

tblTriageVisits and tblTriageProcedures

tblTriageVisits has the following fields
-Patient ID
-Export Time
-Display DateTime
-PT Name
-PT Billnum
-Admission Date and Time
-Discharge Date and time
-PT Attenddoc
-Chief Complain
-PT AdmitDX
-UniqueVisitRecord

tblTriageProcedures has the following fields
-Patient ID
-ExportTime
-Display DateTime
-Procedures FMS
-UniqueProcedureRecord
-TimeProcFMS

In both table, Patient ID refers to the same thing, ie the patients ID
Export time is the time that the EMR spit the text file out
Display DateTime is the time that the field was created in the datebase inside the database inside the EMR.

I am going to use this for several reports, most of which are fairly straight forward. The complicated report that I am stuck on involves making a list of triage visits and what happened to each patient.

Given the way that I have to export the data, there is no direct way to associate a PT-Billnum or a Admit or Discharge date with each procedure.

However, I figured, ok, simple logic, if the procedure time falls between a admit and admit date I can get those two dates (and once I do some more work on exporting the time the ptbillnum was created in the EMR) by looking for the closest Admit and discharge dates to the procedures.

So each procedure would wind up with its own associated admission and discharge date.

I can do this for example if I did not have to associate each procedure with a patient, if I just wanted to find all the procedures between 2 dates.

I can do this if I am only looking for the dates for the first patient in thw query.

What I cannot figure out how to do is for all patients in a given range (which would be by date, but the date part I don't know how to do).

Any suggestions?

 

[Cerb]

Are UniqueVisitRecord and UniqueProcedureRecord the only keys?

 

[thtadthtshldntbe]

Actually, originally the primary key for each table was

tbltriagevisits = patient id + admission date time + unique visit record

tblTriageProcedures = patientid + display datetime + uniquevisitrecord

but then I realized that broke the mechanism by which I built the database to eliminate duplicates so I droped the uniquewhatever from them.

 

[thtadthtshldntbe]

I was thinking about this some more...

What I seem to want to do is

Call up a patients visit from tblTriageVisit

then look in tblTriageProcedures and find all the procedures done whose date falls between the Admission and Discharge dates for that given patient.

What I can do already is fine all visits out of all of them that fall between those two dates. I think. What I am not sure how do yet, is to limit it to just records that have the same patient id.

Is my thinking correct so far?

 

[Cerb]

So, then, if you dropped the column requirements, what are the key sets? If UniqueVisitRecord is not actually unique, what set of data is?

For example, if a patient gets a visit with an admit date, can they be admitted again before the discharge date (IE, can those ever overlap)?

 

[cabri]

You have to start with a known reference point.
That is the time frame and patient ID. you need both or just sort on Patient ID within the time frame.
The later will give you all patients (sorted by ID)

you will need a query join between the two tables.

you can easily use the Query wizard to build such.

 

[thtadthtshldntbe]

So, then, if you dropped the column requirements, what are the key sets? If UniqueVisitRecord is not actually unique, what set of data is?

For example, if a patient gets a visit with an admit date, can they be admitted again before the discharge date (IE, can those ever overlap)?

Procedurally no, meaning from the point of view of patient process, within the maternal childhealth (my division) a patient cannot have another admit date before the discharge date (yes in the whole medical organization a patient can have multiple accounts open, but that is not a case here).

(Mind you I am a still learning some of this stuff) but the unique data set for each table is what I set as the primary key. Those combinations make the records distinct from other records.

You have to start with a known reference point.
That is the time frame and patient ID. you need both or just sort on Patient ID within the time frame.
The later will give you all patients (sorted by ID)

you will need a query join between the two tables.

you can easily use the Query wizard to build such.

That is my sort of idea.

What I wound up trying was to build an access report that first groups by date then sorts the patients alphabetically.

Then I dropped the two fields , Procedures FMS and TimeProcFMS into the body.

The report started listing all the triage procedures for each patient even those from prior visits, but for every visit.

Then I altered the control source for those two items with the following conditions (I should point out that the EMR that I pull this data from exports a bunch of its times as # of seconds since 12/31/1975

=Switch(DateAdd("s",[TimeProcFMS],#12/31/1975#)>=[Admission DateTime OB DateTime] And DateAdd("s",[TimeProcFMS],#12/31/1975#)<=[LD Discharge Date/Time DateTime],[Procedures FMS])

=Switch(DateAdd("s",[TimeProcFMS],#12/31/1975#)>=[Admission DateTime OB DateTime] And DateAdd("s",[TimeProcFMS],#12/31/1975#)<=[LD Discharge Date/Time DateTime],DateAdd("s",[TimeProcFMS],#12/31/1975#))

I thought this was having the effect I had intended, which was to filter out those triage procedures which did not happen for that visit, but I am not 100% sure, that is kind of where I stopped before looking for some help.

But also what happened is that access, even though it did not print those triage procedures it still left spaces for them.

 

[Cerb]

the unique data set for each table is what I set as the primary key. Those combinations make the records distinct from other records.

Yes, but what columns are candidates, if there may be more than one set? You can have more than one possible set of key columns, and I'm thinking that may be the case, here. But, your post with the prior choice of keys was kind of confusing.

IE, are (Patient ID, Admission Date and Time) and (Patient ID, Discharge Date and Time) each unique in all the rows in the table?

Also, is TimeProcFMS the time the procedure is considered to have taken place?

 

[thtadthtshldntbe]

Yes, but what columns are candidates, if there may be more than one set? You can have more than one possible set of key columns, and I'm thinking that may be the case, here. But, your post with the prior choice of keys was kind of confusing.

IE, are (Patient ID, Admission Date and Time) and (Patient ID, Discharge Date and Time) each unique in all the rows in the table?

Also, is TimeProcFMS the time the procedure is considered to have taken place?

sorry, I am fairly certain that you know more about this than I do.

None of those fields are unique in and of themselves.

Take tblTriageVisit

a patient may come in for 3 or 4 triage visits in one day (sometimes triage visits are less than 30 minutes). So her patient id number will show up several times in a particular day's data set. So I made a combined primary key of patient id + admission date and time.

check that, yes the answer to your question is yes. Those primary keys as structured are unique.

primary key for tblTriageVisit = (Patient Id + admission date and time)

Primary key for tblTriageProcedures = (patient id + patientid + display datetime + uniquevisitrecord)

(honestly looking at it now, I can probably eliminate displaydatetime from the second primary key).

TimeProcFMS is the number of seconds since 12/31/1975 that the procedure took place. I use the dateadd to convert it to an actual date and time.

 

[cabri]

<snip>
I thought this was having the effect I had intended, which was to filter out those triage procedures which did not happen for that visit, but I am not 100% sure, that is kind of where I stopped before looking for some help.

Did you manually compare the records to what the query provided?

But also what happened is that access, even though it did not print those triage procedures it still left spaces for them.

???

you can run queries directly without using a report type screen - this will allow you to see that actual data returned.

 

[thtadthtshldntbe]

you can run queries directly without using a report type screen - this will allow you to see that actual data returned.

I know that, doing a data comparison was my next step. I just did a quick update before work to this thread.

What threw me for a loop is the access report leaving blank spaces for those records that my conditionals excluded

 

[cabri]

I know that, doing a data comparison was my next step. I just did a quick update before work to this thread.

What threw me for a loop is the access report leaving blank spaces for those records that my conditionals excluded

no advice on the report issue; never utilized them within access.

 

[Tweak155]

What is the statement you use to get it for the first patient? You can probably set up a procedure to do it for all patients, possibly not a single query depending on how it is structured (although even then I'm sure you could, but I like to avoid making messy views / queries).

 

[thtadthtshldntbe]

well even underlying access reports is a data source query.

I usually try to figure out the underlying query by itself first. I am just kind of stumped on this one.

here is a scrubbed copy of the database
https://drive.google.com/file/d/0B7T2xcfjh34kN0QxVjI5YUE2eUk/view?usp=sharing

I have put in a test patients data so you can see how the report i am trying to run works

 

[Cerb]

I don't know how the switch works*, but I wouldn't be surprised if it acts like a left join, and/or need null checking.

If I am understanding correctly, I think you're after something like this:

SELECT *
FROM tblTriageVisits as ttv
  INNER JOIN tblTriageProcedures AS ttp
    ON ttv.[Patient ID] = ttp.[Patient ID]
      AND (1976+ttp.TimeProcFMS) BETWEEN ttv.[Admission Date and Time] AND ttv.[Discharge Date and Time]

Obviously, converting TimeProcFMS to a normal date, and then changing the selected columns to fit. If the BETWEEN clause fails in the join, it should work in a WHERE clause, and still do the same thing.

That should work based on assuming that (Patient ID, Admission Date and Time) and (Patient ID, Discharge Date and Time) are each unique (candidate keys), TimeProcFMS being between them for each patient, and with them being non-overlapping (no procedure may match more than one date range per patient).

* with SQL's pseudo-3VL, and not ending the condition list with a guaranteed true value, I'm not sure what it should do, and the online docs aren't helpful on that case.

P.S. I have no way to open an accdb, that I know of.

 

[thtadthtshldntbe]

well its an access database, i would not be surprised if Gdocs database program were to open it.

switch works like Select/Case from say VBA does.

Switch (condition,outcome if condition true, condition, outcome if condition ture,etc,etc)

the advantage of switch, as opposed to say iif, is that you can have compound conditions, so like

switch (Var1 > 5 and Var1 < 7,"Var 1 is between 5 and 7, var1 >7 and var1 < 9,"var1 is between 7 and 9").

You can also only nest up to like 7 iif statements in access, whereas you can do to like 10 cases in access. the best thing is that switch is sql so that translating these access reports into my new Crystal reports + SQL database set up that they have just approved for next year.

It looks like that SQL might work. I will probably not be able to test it until saturday. I have to work on a project coming due over the next couple of weeks (ugh...I have to create and test about 3000 new datafields in the EMR itself that I pull this data from, due to the new NJ electronic birthregistry program...sigh only 2 weeks to do it...I won't pull my hair our...)

 

[Cerb]

Right, but I mean, if Var1 is 12, then what? By making it to the point of evaluating the switch statement, the row may be guaranteed to exist in the result set (that's where the docs are unclear), even though some dates for some patients will not match a given visit.