Sounds like query

T

tminihan

I have a table which has a list of medications for a patient.

Table: PatientMedications
Field: PatID
Field: Medicaiton
Field: Dosage
Field: Frequency

The information is updated to the table using a form with a text box as
the medication input box.

Unfortunately there are a lot of user that input the medications (their
way) into the text field.
Example: One user types: Fish Oil 2 grams per day
Another User : Fish Oil 2 g

The design of the form was created before I came into the picture. As
you can see the users are typing in the dosage and frequency within the
same field.

What I need:
A list of patients using certain medications by physician. Each
physician has his/her own database tables which are linked when you
select the physician.

I created a simple query and based a form off of the query with combo
boxes to select the medication name.

SELECT PatientMedication.Medication, Patient.IDNumber, Patient.Name,
Patient.Address, Patient.City, Patient.State, Patient.ZipCode,
Patient.Birthdate, Patient.DateofLastVisit, ReferringPhysician.[Proper
Name], Eval("[Forms]![PatientVisitDate]![Attending].column(0)") AS
Physician
FROM (Patient INNER JOIN PatientMedication ON Patient.IDNumber =
PatientMedication.IDNumber) INNER JOIN ReferringPhysician ON
Patient.ReferringPhysician = ReferringPhysician.IDNumber
WHERE
(((PatientMedication.Medication)=[Forms]![frmMedications]![cmbMed1] Or
(PatientMedication.Medication)=[Forms]![frmMedications]![cmbMed2] Or
(PatientMedication.Medication)=[Forms]![frmMedications]![cmbMed3] Or
(PatientMedication.Medication)=[Forms]![frmMedications]![cmbMed14] Or
(PatientMedication.Medication)=[Forms]![frmMedications]![cmbMed5]));


PROBLEM:
There are about a dozen (if not more) versions of the same medication.

Does anyone know how to return all of the versions of the same
medication if the user types part of the medication?

Example: User types Fish
Query returns all of the medications with the word
fish in it.

Thanks
 
J

Jeff Boyce

Yes, you can do that, no, you probably don't want to...

Never underestimate the ... "creativity" of you users.

I can imagine a well-intentioned user entering "Give a man a fish, ... Teach
a man to fish ...". Did you really want every "medication" with the word
"fish" somewhere in it?

Another approach would be to restrict what can be entered in that field by:
1. you, using your brain, creating a list (i.e., a table) of "valid"
medications (one per medication only)
2. you modifying the form to have a combo box, not a text box
3. you basing the combo box on the list (and setting the LimitToList
property to Yes)

At this point, the users can ONLY select from the list of valid medications.

Or, if you absolutely, positively HAVE to find every row with the word
"fish" in that field, use the following as a selection criterion:

Like *fish*

under that field in the query design grid.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/


I have a table which has a list of medications for a patient.

Table: PatientMedications
Field: PatID
Field: Medicaiton
Field: Dosage
Field: Frequency

The information is updated to the table using a form with a text box as
the medication input box.

Unfortunately there are a lot of user that input the medications (their
way) into the text field.
Example: One user types: Fish Oil 2 grams per day
Another User : Fish Oil 2 g

The design of the form was created before I came into the picture. As
you can see the users are typing in the dosage and frequency within the
same field.

What I need:
A list of patients using certain medications by physician. Each
physician has his/her own database tables which are linked when you
select the physician.

I created a simple query and based a form off of the query with combo
boxes to select the medication name.

SELECT PatientMedication.Medication, Patient.IDNumber, Patient.Name,
Patient.Address, Patient.City, Patient.State, Patient.ZipCode,
Patient.Birthdate, Patient.DateofLastVisit, ReferringPhysician.[Proper
Name], Eval("[Forms]![PatientVisitDate]![Attending].column(0)") AS
Physician
FROM (Patient INNER JOIN PatientMedication ON Patient.IDNumber =
PatientMedication.IDNumber) INNER JOIN ReferringPhysician ON
Patient.ReferringPhysician = ReferringPhysician.IDNumber
WHERE
(((PatientMedication.Medication)=[Forms]![frmMedications]![cmbMed1] Or
(PatientMedication.Medication)=[Forms]![frmMedications]![cmbMed2] Or
(PatientMedication.Medication)=[Forms]![frmMedications]![cmbMed3] Or
(PatientMedication.Medication)=[Forms]![frmMedications]![cmbMed14] Or
(PatientMedication.Medication)=[Forms]![frmMedications]![cmbMed5]));


PROBLEM:
There are about a dozen (if not more) versions of the same medication.

Does anyone know how to return all of the versions of the same
medication if the user types part of the medication?

Example: User types Fish
Query returns all of the medications with the word
fish in it.

Thanks
Click to expand...
 
T

tminihan

Thank you for your input Jeff.

I know I have to fix the original form for the medications. This is
not my issue.
You are right about not returning all for "fish". Say for instance I
have a medication "fish oil 2 grams" and "fish oil 2 g" and "fish oil
1". I only want the "fish oil 2 grams" and the "fish oil 2 g". When I
use the Like, it returns all of the "fish oil".
Here's my criteria
Like [Forms]![frmMedications]![cmbMed1] & "*" OR
Like [Forms]![frmMedications]![cmbMed2] & "*" OR
Like [Forms]![frmMedications]![cmbMed3] & "*" OR
Like [Forms]![frmMedications]![cmbMed4] & "*" OR
Like [Forms]![frmMedications]![cmbMed5] & "*"

You already know this returns all of the records in the table.

If I use one criteria, it works great. The problem is I will have at
least three criteria.

Thanks


Jeff said:
Yes, you can do that, no, you probably don't want to...

Never underestimate the ... "creativity" of you users.

I can imagine a well-intentioned user entering "Give a man a fish, ... Teach
a man to fish ...". Did you really want every "medication" with the word
"fish" somewhere in it?

Another approach would be to restrict what can be entered in that field by:
1. you, using your brain, creating a list (i.e., a table) of "valid"
medications (one per medication only)
2. you modifying the form to have a combo box, not a text box
3. you basing the combo box on the list (and setting the LimitToList
property to Yes)

At this point, the users can ONLY select from the list of valid medications.

Or, if you absolutely, positively HAVE to find every row with the word
"fish" in that field, use the following as a selection criterion:

Like *fish*

under that field in the query design grid.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/


I have a table which has a list of medications for a patient.

Table: PatientMedications
Field: PatID
Field: Medicaiton
Field: Dosage
Field: Frequency

The information is updated to the table using a form with a text box as
the medication input box.

Unfortunately there are a lot of user that input the medications (their
way) into the text field.
Example: One user types: Fish Oil 2 grams per day
Another User : Fish Oil 2 g

The design of the form was created before I came into the picture. As
you can see the users are typing in the dosage and frequency within the
same field.

What I need:
A list of patients using certain medications by physician. Each
physician has his/her own database tables which are linked when you
select the physician.

I created a simple query and based a form off of the query with combo
boxes to select the medication name.

SELECT PatientMedication.Medication, Patient.IDNumber, Patient.Name,
Patient.Address, Patient.City, Patient.State, Patient.ZipCode,
Patient.Birthdate, Patient.DateofLastVisit, ReferringPhysician.[Proper
Name], Eval("[Forms]![PatientVisitDate]![Attending].column(0)") AS
Physician
FROM (Patient INNER JOIN PatientMedication ON Patient.IDNumber =
PatientMedication.IDNumber) INNER JOIN ReferringPhysician ON
Patient.ReferringPhysician = ReferringPhysician.IDNumber
WHERE
(((PatientMedication.Medication)=[Forms]![frmMedications]![cmbMed1] Or
(PatientMedication.Medication)=[Forms]![frmMedications]![cmbMed2] Or
(PatientMedication.Medication)=[Forms]![frmMedications]![cmbMed3] Or
(PatientMedication.Medication)=[Forms]![frmMedications]![cmbMed14] Or
(PatientMedication.Medication)=[Forms]![frmMedications]![cmbMed5]));


PROBLEM:
There are about a dozen (if not more) versions of the same medication.

Does anyone know how to return all of the versions of the same
medication if the user types part of the medication?

Example: User types Fish
Query returns all of the medications with the word
fish in it.

Thanks
Click to expand...
Click to expand...
 
J

John Spencer

Until, you get your data cleaned up.

You can use NZ and a bogus value to search for a value that should never
exist.

Like NZ([Forms]![frmMedications]![cmbMed1],"xttx99rSarsa") & "*"

(hopefully you will never have a medication with the name xttx99rSarsa as
the beginning of the name)

Or you can use IIF to change the criteria

Like [Forms]![frmMedications]![cmbMed1] & IIF(
[Forms]![frmMedications]![cmbMed1] is Null, "","*")


This will work as long as you don't allow a zero-length string in the field.
If you do, then



Thank you for your input Jeff.

I know I have to fix the original form for the medications. This is
not my issue.
You are right about not returning all for "fish". Say for instance I
have a medication "fish oil 2 grams" and "fish oil 2 g" and "fish oil
1". I only want the "fish oil 2 grams" and the "fish oil 2 g". When I
use the Like, it returns all of the "fish oil".
Here's my criteria
Like [Forms]![frmMedications]![cmbMed1] & "*" OR
Like [Forms]![frmMedications]![cmbMed2] & "*" OR
Like [Forms]![frmMedications]![cmbMed3] & "*" OR
Like [Forms]![frmMedications]![cmbMed4] & "*" OR
Like [Forms]![frmMedications]![cmbMed5] & "*"

You already know this returns all of the records in the table.

If I use one criteria, it works great. The problem is I will have at
least three criteria.

Thanks


Jeff said:
Yes, you can do that, no, you probably don't want to...

Never underestimate the ... "creativity" of you users.

I can imagine a well-intentioned user entering "Give a man a fish, ...
Teach
a man to fish ...". Did you really want every "medication" with the word
"fish" somewhere in it?

Another approach would be to restrict what can be entered in that field
by:
1. you, using your brain, creating a list (i.e., a table) of "valid"
medications (one per medication only)
2. you modifying the form to have a combo box, not a text box
3. you basing the combo box on the list (and setting the LimitToList
property to Yes)

At this point, the users can ONLY select from the list of valid
medications.

Or, if you absolutely, positively HAVE to find every row with the word
"fish" in that field, use the following as a selection criterion:

Like *fish*

under that field in the query design grid.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/


I have a table which has a list of medications for a patient.

Table: PatientMedications
Field: PatID
Field: Medicaiton
Field: Dosage
Field: Frequency

The information is updated to the table using a form with a text box as
the medication input box.

Unfortunately there are a lot of user that input the medications (their
way) into the text field.
Example: One user types: Fish Oil 2 grams per day
Another User : Fish Oil 2 g

The design of the form was created before I came into the picture. As
you can see the users are typing in the dosage and frequency within the
same field.

What I need:
A list of patients using certain medications by physician. Each
physician has his/her own database tables which are linked when you
select the physician.

I created a simple query and based a form off of the query with combo
boxes to select the medication name.

SELECT PatientMedication.Medication, Patient.IDNumber, Patient.Name,
Patient.Address, Patient.City, Patient.State, Patient.ZipCode,
Patient.Birthdate, Patient.DateofLastVisit, ReferringPhysician.[Proper
Name], Eval("[Forms]![PatientVisitDate]![Attending].column(0)") AS
Physician
FROM (Patient INNER JOIN PatientMedication ON Patient.IDNumber =
PatientMedication.IDNumber) INNER JOIN ReferringPhysician ON
Patient.ReferringPhysician = ReferringPhysician.IDNumber
WHERE
(((PatientMedication.Medication)=[Forms]![frmMedications]![cmbMed1] Or
(PatientMedication.Medication)=[Forms]![frmMedications]![cmbMed2] Or
(PatientMedication.Medication)=[Forms]![frmMedications]![cmbMed3] Or
(PatientMedication.Medication)=[Forms]![frmMedications]![cmbMed14] Or
(PatientMedication.Medication)=[Forms]![frmMedications]![cmbMed5]));


PROBLEM:
There are about a dozen (if not more) versions of the same medication.

Does anyone know how to return all of the versions of the same
medication if the user types part of the medication?

Example: User types Fish
Query returns all of the medications with the word
fish in it.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
T

tminihan

Thanks John! Works great.

John said:
Until, you get your data cleaned up.

You can use NZ and a bogus value to search for a value that should never
exist.

Like NZ([Forms]![frmMedications]![cmbMed1],"xttx99rSarsa") & "*"

(hopefully you will never have a medication with the name xttx99rSarsa as
the beginning of the name)

Or you can use IIF to change the criteria

Like [Forms]![frmMedications]![cmbMed1] & IIF(
[Forms]![frmMedications]![cmbMed1] is Null, "","*")


This will work as long as you don't allow a zero-length string in the field.
If you do, then



Thank you for your input Jeff.

I know I have to fix the original form for the medications. This is
not my issue.
You are right about not returning all for "fish". Say for instance I
have a medication "fish oil 2 grams" and "fish oil 2 g" and "fish oil
1". I only want the "fish oil 2 grams" and the "fish oil 2 g". When I
use the Like, it returns all of the "fish oil".
Here's my criteria
Like [Forms]![frmMedications]![cmbMed1] & "*" OR
Like [Forms]![frmMedications]![cmbMed2] & "*" OR
Like [Forms]![frmMedications]![cmbMed3] & "*" OR
Like [Forms]![frmMedications]![cmbMed4] & "*" OR
Like [Forms]![frmMedications]![cmbMed5] & "*"

You already know this returns all of the records in the table.

If I use one criteria, it works great. The problem is I will have at
least three criteria.

Thanks


Jeff said:
Yes, you can do that, no, you probably don't want to...

Never underestimate the ... "creativity" of you users.

I can imagine a well-intentioned user entering "Give a man a fish, ...
Teach
a man to fish ...". Did you really want every "medication" with the word
"fish" somewhere in it?

Another approach would be to restrict what can be entered in that field
by:
1. you, using your brain, creating a list (i.e., a table) of "valid"
medications (one per medication only)
2. you modifying the form to have a combo box, not a text box
3. you basing the combo box on the list (and setting the LimitToList
property to Yes)

At this point, the users can ONLY select from the list of valid
medications.

Or, if you absolutely, positively HAVE to find every row with the word
"fish" in that field, use the following as a selection criterion:

Like *fish*

under that field in the query design grid.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/


I have a table which has a list of medications for a patient.

Table: PatientMedications
Field: PatID
Field: Medicaiton
Field: Dosage
Field: Frequency

The information is updated to the table using a form with a text box as
the medication input box.

Unfortunately there are a lot of user that input the medications (their
way) into the text field.
Example: One user types: Fish Oil 2 grams per day
Another User : Fish Oil 2 g

The design of the form was created before I came into the picture. As
you can see the users are typing in the dosage and frequency within the
same field.

What I need:
A list of patients using certain medications by physician. Each
physician has his/her own database tables which are linked when you
select the physician.

I created a simple query and based a form off of the query with combo
boxes to select the medication name.

SELECT PatientMedication.Medication, Patient.IDNumber, Patient.Name,
Patient.Address, Patient.City, Patient.State, Patient.ZipCode,
Patient.Birthdate, Patient.DateofLastVisit, ReferringPhysician.[Proper
Name], Eval("[Forms]![PatientVisitDate]![Attending].column(0)") AS
Physician
FROM (Patient INNER JOIN PatientMedication ON Patient.IDNumber =
PatientMedication.IDNumber) INNER JOIN ReferringPhysician ON
Patient.ReferringPhysician = ReferringPhysician.IDNumber
WHERE
(((PatientMedication.Medication)=[Forms]![frmMedications]![cmbMed1] Or
(PatientMedication.Medication)=[Forms]![frmMedications]![cmbMed2] Or
(PatientMedication.Medication)=[Forms]![frmMedications]![cmbMed3] Or
(PatientMedication.Medication)=[Forms]![frmMedications]![cmbMed14] Or
(PatientMedication.Medication)=[Forms]![frmMedications]![cmbMed5]));


PROBLEM:
There are about a dozen (if not more) versions of the same medication.

Does anyone know how to return all of the versions of the same
medication if the user types part of the medication?

Example: User types Fish
Query returns all of the medications with the word
fish in it.

Thanks
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Dynamic Query Criteria and returning all records (*) 1
Basic Question- "IIF/Like" 5
Query Criteria 1
Query to find records containing all words 2
Query Criteria (QBF) 1
Access Create Access query dynamically 0
String Express in Append Query 1
Adding query results to a table field 2

Top