Query to return stopped medications

  • Thread starter Thread starter Deb
  • Start date Start date
D

Deb

Newby using Access 2000. Have a table for drugs which
have a start and stop date. Many records are stopped but
then started again. I need to run a query which only
returns those records truly stopped.

All help much appreciated!
 
What logic would you use to determine this? For example, what if a medicine
was started in November 2003, Stopped in December 2003, and restarted in
February 2004, but not stopeed again? What would your entry on the table
look like? Would the Stop Date be blank, or would it still contain December
2003?

I would think that you should store these start and stop dates in a separate
tabel tied to the medicine name or number. You have a classic one-to-many
relationship here, which requires two tables.

If you don't want to do that, then you need to blank out the "Stop Date"
whenever you enter a new "Start Date". By doing so, you could simply use a
query to locate all records with an entry in the stop date. If you ever
enter anticipated stop dates (future dates), then your query would need to
look for all entries with a stop date that is before today.

If for some reason you can't blank out the previous stop dates when a
medicine is restarted, then you would need to find all medicines where the
stop date is in the past *AND* the start date is not greater than the stop
date.

Let us know which of these items apply to your situation and we can help you
with the exact query syntax if you need help.

Thanks,

Rick B





Newby using Access 2000. Have a table for drugs which
have a start and stop date. Many records are stopped but
then started again. I need to run a query which only
returns those records truly stopped.

All help much appreciated!
 
Here is a sample of the table layout
Drug Start Stop Dose
Aspirin 1/1/04 5/1/04 10 mg
Aspirin 5/1/04 5 mg
Prilosec 2/1/04 2/15/04 5 mg
Prilosec 2/15/04 10 mg
Zyprexa 1/1/04 3/1/04 20 mg
and so on

My query should only return Zyprexa as it is the only one
not restarted.

Hope this helps. Thanks!!!
 
I'm not sure how to get what you are looking for in this case. Testing for
a stop date would get you the first instance of each drug in your example.

I have played with this quite a bit and can't seem to find a way to do it.

I will watch this thread to see if one of our MVPs can come up with a
solution!

Rick B




Here is a sample of the table layout
Drug Start Stop Dose
Aspirin 1/1/04 5/1/04 10 mg
Aspirin 5/1/04 5 mg
Prilosec 2/1/04 2/15/04 5 mg
Prilosec 2/15/04 10 mg
Zyprexa 1/1/04 3/1/04 20 mg
and so on

My query should only return Zyprexa as it is the only one
not restarted.

Hope this helps. Thanks!!!
 
If you mean that a drug is active as long as it has an open stop date and you
want all drugs that are stopped. You could do this in two queries.

QueryOne - Get all drugs that don't have a stop date:
SELECT Drug
FROM Table
WHERE Stop Is Null

Query two uses queryone

SELECT DISTINCT Table.*
FROM Table LEFT JOIN QueryOne
ON Table.Drug = QueryOne.Drug
WHERE QueryOne.Drug is Null

There are other methods that should work. Look into the Exists clause.
 
Back
Top