Get Data fields from another table based on 2 dates in first table (1 Viewer)

thtadthtshldntb

Registered User.
Local time
Today, 06:52
Joined
Oct 29, 2014
Messages
20
Hello, I am new here, though I have been lurking and using your Access wisdom for a while. You guys are great.

Anyway, I would call myself intermediate level at best with Access. I never expected to have to do so much with it, but when my bosses found out I could do Access basics, they began demanding more and more.

I manage an EMR from which a datapull occurs on discharge to various access databases.

They wanted me to add a triage patient data pull to track what procedures are being done to triage patients. So I built a database with the following 2 tables.

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 tables, Patient ID refers to the same thing

The Primary Key for tblTriageVisits is
Patient ID + Admission DateTime OB DateTime

The Primary Key for tblTriageProcedures is
PatientID + Procedures FMS + TimeProcFMS

The tblTriageVisits stores all the patients triage visits. The other table stores what was done to each patient on those visits

It should also be noted that this EMR exports times in number of seconds since 12/31/1975, so TimeProcFMS is the number of seconds since that date for example.

The pulls work great and the duplicate record elimination method works great.

I have to design a couple of different reports based on this data and one in particular has pretty much flumoxxed me.

I need to make a report that lists each patient by date they were there and then shows what was done to them. I have tried several different variations of this as both queries and reports. The report breaks down when I try to get it to show just those procedures that would be for that triage visit. I got to the point where it would group by day and then sort the patients alphabetically. Then I added in the visit fields. And it would put all triage procedures for that patient from all their visits in each visit.

So, I was like great, let me filter out those that don't occur between the two dates, admission and discharge

I used the following two formulas (the first one on the procedure name field the second on the procedure date field)

=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#))

And they work, sort of. They do eliminate those procedures that occur outside of the date range I am looking for. However, the access report still leave spaces in the report where the filtered out items would be.

Did I approach this problem correctly? And if so, how do I get rid of those spaces?

Any help is appreciated.

Note, I can read and mostly understand SQL, but am not comfortable working in it yet. And the Access SQL box gives me a headache, it is so jumbled up.
 

JHB

Have been here a while
Local time
Today, 11:52
Joined
Jun 17, 2012
Messages
7,732
After reading it several times, it is still not clear how you have set up the report, (if the report is based on a query, tables or what ever)!
What isn't clear either, you are showing the field names in the tables, but then you are using some which isn't mention like:
[Admission DateTime OB DateTime] and [LD Discharge Date/Time DateTime]
where are they coming from?
Why are you using Switch?
.. However, the access report still leave spaces in the report where the filtered out items would be. ..
What do you mean by that, (show a printscreen)?
Could you show some sample data in an Excel sheet or MS-Access database + how you want the result, (zip it because you haven't post 10 post yet).
If a report involved more as one table, then you need a query as recordsource!
 

thtadthtshldntb

Registered User.
Local time
Today, 06:52
Joined
Oct 29, 2014
Messages
20
After reading it several times, it is still not clear how you have set up the report, (if the report is based on a query, tables or what ever)!
What isn't clear either, you are showing the field names in the tables, but then you are using some which isn't mention like:
[Admission DateTime OB DateTime] and [LD Discharge Date/Time DateTime]
where are they coming from?
Why are you using Switch?
What do you mean by that, (show a printscreen)?
Could you show some sample data in an Excel sheet or MS-Access database + how you want the result, (zip it because you haven't post 10 post yet).
If a report involved more as one table, then you need a query as recordsource!

When I get to work I will make a blank version of the database and link to it, via my googledrive.

The report is based on the internal query inside the record when you set up the whole reports data source. I usually try to work out the base query as just a query then create the report and copy the query into the reports record source.

Sorry for the ambiguity with those two fields, I had intended to use briefer names in typing stuff out to avoid typos,

In tblTriageVisits

Admission Date and Time is really called Admission DateTime OB DateTime
Discharge Date and TIme is really called LD Discharge Date/Time DateTime

I am stuck with those longer names because the EMR exports them as those fields and its easier when setting up the duplicate record prevention to just use the awkward names it calls them all the way through.

I am using switch because as I understand it, to use multiple conditions with an iif statement, you have to nest them and in my experience that can lead to more errors.
 

JHB

Have been here a while
Local time
Today, 11:52
Joined
Jun 17, 2012
Messages
7,732
When I get to work I will make a blank version of the database and link to it, via my googledrive. ...
Then I'll wait for it, but is better if you zip it and add it to this thread.
 

thtadthtshldntb

Registered User.
Local time
Today, 06:52
Joined
Oct 29, 2014
Messages
20
Then I'll wait for it, but is better if you zip it and add it to this thread.

Note that there is usually a startup macro that runs the text file imports to each sequence of tables and eliminates duplicates.

once all that is done, the two primary tables that I use are

tblTriageVisits
tblTriageProcedures

There are three reports

Triage patients by Date
Triage Patients by Date with Procedures
Triage Patients by Date with Procedures and Switch

The latter two are where I am stuck.

I tried to use the switch statements on the procedure date to filter out the ones that did not go with a particular visit, but what look at what wound up happening.

As I said, If I only had to do this with 1 patient, I know what to do (I think).

Any advice is appreciated.
 

Attachments

  • Triage.zip
    280.4 KB · Views: 76

JHB

Have been here a while
Local time
Today, 11:52
Joined
Jun 17, 2012
Messages
7,732
Then I only need to know how you want the result for the two reports, (show a printscreen). Remember I don't know your business so ...
 

thtadthtshldntb

Registered User.
Local time
Today, 06:52
Joined
Oct 29, 2014
Messages
20
open the triage database.

run the report called

Triage Patients by Date with Procedures. I would like it to look like that, however with only the procedures that go to those particular visits.
 

JHB

Have been here a while
Local time
Today, 11:52
Joined
Jun 17, 2012
Messages
7,732
Do you mean like in the below picture?
 

Attachments

  • Triage.jpg
    Triage.jpg
    85 KB · Views: 222

thtadthtshldntb

Registered User.
Local time
Today, 06:52
Joined
Oct 29, 2014
Messages
20
yeah, but with only the procedures that occurred for that patient in that particular visit showing for that visit
 

JHB

Have been here a while
Local time
Today, 11:52
Joined
Jun 17, 2012
Messages
7,732
yeah, but with only the procedures that occurred for that patient in that particular visit showing for that visit
And that is (when you look at the data in the picture)?? And how do you determine that?
 
Last edited:

thtadthtshldntb

Registered User.
Local time
Today, 06:52
Joined
Oct 29, 2014
Messages
20
Each visit, in tblTriageVisits has a Admission date and a discharge date.

My idea was to use those switch statements in the text boxes to filter out those procedures that don't go with that particular patients visit. I think it sort of worked, but the one issue that happened was that even though it filtered out procedures outside a range, it left the blank spaces for those it filter out.

fundamentally what I want is a query that lets me

pick a patient visit
find all the records in tblTriageProcedures that go to that visit
pick the next patien visit
find all the records in tblTriageProcedures that go to this visit
repeat
repeat

IF I only had to do this for one patient, I think I can do that.

I don't know how to do this for more than 1 patient.
 

JHB

Have been here a while
Local time
Today, 11:52
Joined
Jun 17, 2012
Messages
7,732
..
My idea was to use those switch statements in the text boxes to filter out those procedures that don't go with that particular patients visit...
You can't do that in this way, you need to sort the data out which you don't want in the report, using the where condition in a query or said the other way round, select only the records you want to see in the report.
A report show so many records as the Recordsource for the report has.
Take a look at the picture in post #8, tell me exactly which shouldn't be in there and why they shouldn't!
 

thtadthtshldntb

Registered User.
Local time
Today, 06:52
Joined
Oct 29, 2014
Messages
20
yeah, that was my original question, the actual query itself. When there was not much response I began trying to see if i could do it in the report itself.

If you go by that screen shot, none of the records for any of those procedures should be there, because they fall outside of the date for the two days lists.

Ideally, in the query itself, I would call up

patient id
patient name
admission date
discharge date

then it would look in the other table, tblTriageProcedures

and find all the triage procedures that go between the particular admission and discharge date for that patient.
 

JHB

Have been here a while
Local time
Today, 11:52
Joined
Jun 17, 2012
Messages
7,732
..
If you go by that screen shot, none of the records for any of those procedures should be there, because they fall outside of the date for the two days lists.
..
..and find all the triage procedures that go between the particular admission and discharge date for that patient.
Does the below not do that?
 

Attachments

  • a.jpg
    a.jpg
    96.9 KB · Views: 285

thtadthtshldntb

Registered User.
Local time
Today, 06:52
Joined
Oct 29, 2014
Messages
20
Maybe we are having a communication difficulty, which I accept responsibility for as I am still learning my way around access.

Based on some suggestions in here I have made the following query

SELECT tblTriageVisit.[Patient ID], tblTriageVisit.[PT-Name], tblTriageVisit.[pt-billnum], tblTriageVisit.[Admission DateTime OB DateTime], tblTriageVisit.[LD Discharge Date/Time DateTime], DateAdd("s",[TimeProcFMS],#12/31/1975#) AS ValidProcTime, tblTriageProcedures.[Procedures FMS]
FROM tblTriageVisit INNER JOIN tblTriageProcedures ON tblTriageVisit.[Patient ID] = tblTriageProcedures.[Patient ID]
WHERE (((DateAdd("s",[TimeProcFMS],#12/31/1975#))>=[Admission DateTime OB DateTime] And (DateAdd("s",[TimeProcFMS],#12/31/1975#))<=[LD Discharge Date/Time DateTime]));

I think that does part of what I want.

However, when I try to change the join so that it will include all visits even those with no corresponding procedures, it gives me an invalid data type.

Which I think is because of the null fields, when there are no corresponding triage procesdures for a visit.
 

JHB

Have been here a while
Local time
Today, 11:52
Joined
Jun 17, 2012
Messages
7,732
Maybe we are having a communication difficulty ...
Yeah I think so, because if I run the query you show below and for which you write
I think that does part of what I want.
It gives exactly the same result as what I've showed you, so yes it must be that.
Below is the result from your query:

And for the above you wrote in post #13:
If you go by that screen shot, none of the records for any of those procedures should be there, because they fall outside of the date for the two days lists.
So I'll leave here and wish you luck with your project.
 

Attachments

  • Result.jpg
    Result.jpg
    66.6 KB · Views: 130

Users who are viewing this thread

Top Bottom