Parameter query for no records in date range

[Guest]

i am working on a parameter query that checks a date range (using
Between/And) but that would return a patient name if no records were found in
that date range.
Any help is much appreciated...sorry, I posted a similiar question but can't
find the thread. The query uses data from two tables: Patient Information and
Medication Record. I am basically checking to see if a patient missed an
injection (medication) duirng a specified date range....usually 1 month. Many
thanks again.

 

[Jeff Boyce]

Kathy

One approach to doing this would be to:
* query to find all patients
* query to find all patients that DID get injected in the selected date
range
* using the two previous queries, create a new (unmatched) query that
finds all in the first that are not in the second.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

For that you can use your query as a sub query , something like


Select T1.* From TableName As T1 Where T1.PatientId Not In (Select
T2.PatientId From TableName Where T2.DateFieldName Between
Nz([Forms]![frmParameter]![txtStartDate],#1/1/1900#) And
Nz([Forms]![frmParameter]![txtEndDate],Date()))

If you want further help I'll need the SQL you are using

 

[Guest]

Sorry, forgot to set the second table as T2

Select T1.* From TableName As T1 Where T1.PatientId Not In (Select
T2.PatientId From TableName As T2 Where T2.DateFieldName Between
Nz([Forms]![frmParameter]![txtStartDate],#1/1/1900#) And
Nz([Forms]![frmParameter]![txtEndDate],Date()))

--
Good Luck
BS"D

For that you can use your query as a sub query , something like


Select T1.* From TableName As T1 Where T1.PatientId Not In (Select
T2.PatientId From TableName Where T2.DateFieldName Between
Nz([Forms]![frmParameter]![txtStartDate],#1/1/1900#) And
Nz([Forms]![frmParameter]![txtEndDate],Date()))

If you want further help I'll need the SQL you are using
--
Good Luck
BS"D

i am working on a parameter query that checks a date range (using
Between/And) but that would return a patient name if no records were found in
that date range.
Any help is much appreciated...sorry, I posted a similiar question but can't
find the thread. The query uses data from two tables: Patient Information and
Medication Record. I am basically checking to see if a patient missed an
injection (medication) duirng a specified date range....usually 1 month. Many
thanks again.

 

[Guest]

Kathy,

I asked for more information about your data structure yesterday, but didn't
get it. It would be helpful if we knew what the fields in your table(s)
looked like, but without that I'll take a stab at it.

Since you are new to Access, I would recommend doing this as two queries.

Query1: This query identifies those patients that received an injection
during the month of Jan 2001. I would assume that you probably also have a
Medication field or something else, so you may need to modify the where
clause of this query.

SELECT DISTINCT PatientID
FROM [Medication Record]
WHERE InjectionDate BETWEEN #2007/01/01# AND #2007/01/31#

Query 2: This query selects all of the patients from your Patient
Information table that do not have a matching record in the results generated
by Query1.

SELECT PatientID, PatientLastName, PatientFirstName
FROM [Patient Information]
LEFT JOIN Query1 ON [Patient Information].PatientID = Query1.PatientID
WHERE Query1.PatientID IS NULL

I would expect a medication tracking database to be much more complex than
this. How do you know which patients are supposed to recieve a particular
medication on a given day? You have not taken this into consideration in
this query, so I assume you are just using this to ID those that have not
gotten an injection, then you are going to check their charts or something to
determine whether they were supposed to get an injection.

HTH
Dale

 

[Guest]

Sorry, got that date format wrong , should be #mm/dd/yyyy# format.

Dale
--
Email address is not valid.
Please reply to newsgroup only.

Kathy,

I asked for more information about your data structure yesterday, but didn't
get it. It would be helpful if we knew what the fields in your table(s)
looked like, but without that I'll take a stab at it.

Since you are new to Access, I would recommend doing this as two queries.

Query1: This query identifies those patients that received an injection
during the month of Jan 2001. I would assume that you probably also have a
Medication field or something else, so you may need to modify the where
clause of this query.

SELECT DISTINCT PatientID
FROM [Medication Record]
WHERE InjectionDate BETWEEN #2007/01/01# AND #2007/01/31#

Query 2: This query selects all of the patients from your Patient
Information table that do not have a matching record in the results generated
by Query1.

SELECT PatientID, PatientLastName, PatientFirstName
FROM [Patient Information]
LEFT JOIN Query1 ON [Patient Information].PatientID = Query1.PatientID
WHERE Query1.PatientID IS NULL

I would expect a medication tracking database to be much more complex than
this. How do you know which patients are supposed to recieve a particular
medication on a given day? You have not taken this into consideration in
this query, so I assume you are just using this to ID those that have not
gotten an injection, then you are going to check their charts or something to
determine whether they were supposed to get an injection.

HTH
Dale

--
Email address is not valid.
Please reply to newsgroup only.

i am working on a parameter query that checks a date range (using
Between/And) but that would return a patient name if no records were found in
that date range.
Any help is much appreciated...sorry, I posted a similiar question but can't
find the thread. The query uses data from two tables: Patient Information and
Medication Record. I am basically checking to see if a patient missed an
injection (medication) duirng a specified date range....usually 1 month. Many
thanks again.

 

[Guest]

First of all, thank you for answering me again as you answered yesterday and
I thought I had figured it out, but I was wrong, then I lost the thread
etc....anyway, I followed your suggestion and it looks like it is
working...although I also said that yesterday...I am not sure how to use a
subQuery, so I just built another query and went into SQL view and adjusted
your code to my tables and the returned data now looks good. I guess you can
tell I am a novice...but many thanks for your help!
--
Kbelo

Sorry, forgot to set the second table as T2

Select T1.* From TableName As T1 Where T1.PatientId Not In (Select
T2.PatientId From TableName As T2 Where T2.DateFieldName Between
Nz([Forms]![frmParameter]![txtStartDate],#1/1/1900#) And
Nz([Forms]![frmParameter]![txtEndDate],Date()))

--
Good Luck
BS"D

For that you can use your query as a sub query , something like


Select T1.* From TableName As T1 Where T1.PatientId Not In (Select
T2.PatientId From TableName Where T2.DateFieldName Between
Nz([Forms]![frmParameter]![txtStartDate],#1/1/1900#) And
Nz([Forms]![frmParameter]![txtEndDate],Date()))

If you want further help I'll need the SQL you are using
--
Good Luck
BS"D

i am working on a parameter query that checks a date range (using
Between/And) but that would return a patient name if no records were found in
that date range.
Any help is much appreciated...sorry, I posted a similiar question but can't
find the thread. The query uses data from two tables: Patient Information and
Medication Record. I am basically checking to see if a patient missed an
injection (medication) duirng a specified date range....usually 1 month. Many
thanks again.

 

[Guest]

Thank you Dale for answering me...(again)...I lost the thread and nothing
seemed to be working out for me yesterday, but today is better and I think I
have fixed the problem. But just wanted to say I appreciated your replys.
--
Kbelo

Sorry, got that date format wrong , should be #mm/dd/yyyy# format.

Dale
--
Email address is not valid.
Please reply to newsgroup only.

Kathy,

I asked for more information about your data structure yesterday, but didn't
get it. It would be helpful if we knew what the fields in your table(s)
looked like, but without that I'll take a stab at it.

Since you are new to Access, I would recommend doing this as two queries.

Query1: This query identifies those patients that received an injection
during the month of Jan 2001. I would assume that you probably also have a
Medication field or something else, so you may need to modify the where
clause of this query.

SELECT DISTINCT PatientID
FROM [Medication Record]
WHERE InjectionDate BETWEEN #2007/01/01# AND #2007/01/31#

Query 2: This query selects all of the patients from your Patient
Information table that do not have a matching record in the results generated
by Query1.

SELECT PatientID, PatientLastName, PatientFirstName
FROM [Patient Information]
LEFT JOIN Query1 ON [Patient Information].PatientID = Query1.PatientID
WHERE Query1.PatientID IS NULL

I would expect a medication tracking database to be much more complex than
this. How do you know which patients are supposed to recieve a particular
medication on a given day? You have not taken this into consideration in
this query, so I assume you are just using this to ID those that have not
gotten an injection, then you are going to check their charts or something to
determine whether they were supposed to get an injection.

HTH
Dale

--
Email address is not valid.
Please reply to newsgroup only.

i am working on a parameter query that checks a date range (using
Between/And) but that would return a patient name if no records were found in
that date range.
Any help is much appreciated...sorry, I posted a similiar question but can't
find the thread. The query uses data from two tables: Patient Information and
Medication Record. I am basically checking to see if a patient missed an
injection (medication) duirng a specified date range....usually 1 month. Many
thanks again.