How do I get same name from six fields on same record? query?

[Hal]

I need to search across six fields, which will enable me to get an idea of
the total amount of drugs used. I need to do this without having to query
every field though.

ie, search for quetiapine, which in one record is in field one, in the
second record, quetiapine is in field three and in the third record the
quetiapine is in field six.

How do I find out that there are three quetiapines without going to search
all the fields?

 

[Rick Brandt]

I need to search across six fields, which will enable me to get an
idea of the total amount of drugs used. I need to do this without
having to query every field though.

ie, search for quetiapine, which in one record is in field one, in the
second record, quetiapine is in field three and in the third record
the quetiapine is in field six.

How do I find out that there are three quetiapines without going to
search all the fields?

Sounds like your table design is incorrect. With the current design you
have no choice but to search all of the fields.

SELECT Count(*)
FROM TableName
WHERE Field1 = "quetiapine"
OR Field2 = "quetiapine"
OR Field3 = "quetiapine"
OR Field4 = "quetiapine"
OR Field5 = "quetiapine"
OR Field6 = "quetiapine"

 

[tina]

suggest you try using a Union query to get the drugs all into one field, so
you can search on one field.

a more basic issue is: why do you have 6 drug fields in one record? sounds
like your table's data is not normalized. if you're not familiar with data
normalization concepts and tables/relationships design rules, suggest you
see
http://www.ltcomputerdesigns.com/JCReferences.html
starting with the "Database Design 101" link.

hth

 

[Hal]

Hi there Tina,

Thanks for replying.

Basically, I have clients who can have up to six sets of drugs each. I
created a table and form so that I could have a master set of drugs which I
then created the drop down boxes from. Unfortunately because they can have up
to six drugs and in no particular order I am left with no choice (to my very
basic knowledge) but to do it this way. If you know of a way that will allow
me to create a list for six fields I would be grateful of advice on this.

Many thanks
Hal

 

[Hal]

Hi there Rick

Thanks for replying

So with this set of instructions can I have it so I can put in another name
as with a query [ ]? As you may be able to tell am an amateur at this sort of
thing!!!

Many thanks
Hal

 

[Douglas J Steele]

While the approach suggested to you by Tina is far better, yes, you can use
a parameter:

SELECT Count(*)
FROM TableName
WHERE Field1 = [What Drug?]
OR Field2 = [What Drug?]
OR Field3 = [What Drug?]
OR Field4 = [What Drug?]
OR Field5 = [What Drug?]
OR Field6 = [What Drug?]

It's absolutely critical that you type the 6 parameters identically: any
differences in spelling or spaces will result in multiple prompts.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi there Rick

Thanks for replying

So with this set of instructions can I have it so I can put in another name
as with a query [ ]? As you may be able to tell am an amateur at this sort of
thing!!!

Many thanks
Hal

Sounds like your table design is incorrect. With the current design you
have no choice but to search all of the fields.

SELECT Count(*)
FROM TableName
WHERE Field1 = "quetiapine"
OR Field2 = "quetiapine"
OR Field3 = "quetiapine"
OR Field4 = "quetiapine"
OR Field5 = "quetiapine"
OR Field6 = "quetiapine"

 

[tina]

you're making a common newbie mistake, Hal - thinking "spreadsheet"(also
referred to as "flat file"), instead of "relational database". most folks do
this when they start out, especially if they've used Excel for *anything*!


in order to use Access successfully and leverage its' full (and
considerable) power, you need to normalize your data, and build your
tables/relationships appropriately. learning to do this, and then doing it,
takes a certain investment of time, which you will more than recoup as you
continue building the rest of your database on correctly structured
tables/relationships. (not to mention the many tension headaches, gray
hairs, ulcers, etc, that you'll avoid! <g>)

recommend you go to
http://www.ltcomputerdesigns.com/JCReferences.html
for many, many valuable links to resources on all aspects of database
design.
begin with the "Starting Out" and "Database Design 101" links.

to help you relate what you read to your situation, here are a minimum of
three tables you'll need:

tblDrugs
DrugID (primary key)
DrugName
(other fields that describe a *drug*)

tblClients
ClientID (pk)
FirstName
LastName
(other fields that describe a *client*)

tblClientDrugs
ClientDrugID (pk)
ClientID (foreign key from tblClients)
DrugID (fk from tblDrugs)
(note: you can use a separate pk field, as above. or you can leave out that
field, and use the two foreign key fields as a combination pk.)
if one client has 3 drugs, then you'll enter three records for that client -
one for each drug. if a client has 1 drug - one record. and so on.

one of the many advantages of relational design is that it is infinitely
expandable: one client may have 1 drug, or 10 drugs, or 100 drugs, or no
drugs. it doesn't matter, doesn't affect the structure of the tables, or
queries, forms, etc. by comparison, using a "flat file" design is only
expandable with extreme effort: if you create a table with 6 drug fields,
then your queries, forms, reports, etc are all structured to manipulate and
display 6 drug fields (with some difficulty, as you've already experienced).
to accomodate a client with 7 drug fields, you have to begin by adding
another field to the table - and then continue by modifying every other
object connected to that table, which constitutes a major rewrite.

hth

 

[John Vinson]

Hi there Tina,

Thanks for replying.

Basically, I have clients who can have up to six sets of drugs each.

And you can be ABSOLUTELY CERTAIN that none of them will ever need
*seven* drugs? Because if so, your table design won't work: you'll
need to redesign your table, all your queries, and all your forms to
accommodate this one patient! NOT a good prospect!

I created a table and form so that I could have a master set of drugs which I
then created the drop down boxes from. Unfortunately because they can have up
to six drugs and in no particular order I am left with no choice (to my very
basic knowledge) but to do it this way. If you know of a way that will allow
me to create a list for six fields I would be grateful of advice on this.

Tina's link to the normaliztion tutorial would be well worth following
and studying. In short - you need to follow the old saw, "Fields are
expensive, records are cheap". You have a Many (patients) to Many
(drugs) relationship; the proper solution is to use *THREE TABLES":

Patients
PatientID
<biographical & contact info, no data about drugs at all>

Drugs
DrugID
DrugName
<other info about the drug, e.g. manufacturer, cost, whatever>

Prescriptions
PatientID << link to Patients
DrugID << link to Drugs
<other info, such as dosage, dose schedule, comments>

To enter data into this structure you could use a Form based on
Patients with a Subform based on Prescriptions; this will allow you to
enter one, or six, or seventeen drugs per patient.

You can then find out which patients take a given drug by creating a
Query joining Patients to Prescriptions, and searching this query for
a particular DrugID.

John W. Vinson[MVP]

 

[Hal]

WOW!!!! I have read through the Tina suggestion and being a bit of newbbie as
we have now certainly ascertained, I am in a flumux again. tbls and queries
and all sorts. Can I run what I think the instructions are:

1. Create a table for each element, 1 for personal information etc, 1 for
drugs information.

Then create a query to link the two.

Within the query how the devil do you link the two? (I am so sorry for sound
like acomplete "ass" in this but I am rapidly noticing my amateur side!!!!

A step by step would be really really helpful, if you can spare the time.

Many thanks
Hal

 

[Hal]

By way of a short term fix until I have learned the normalisation, which I
fear may take some time, the solution below seems a logical one. By way of
running this passed you, I have a report and use the code below, I have given
an example of the text I used and it came up with errors.

SELECT Count(*)
FROM table - main
WHERE drug 1 = [Aricept]
OR drug 2 = [Aricept]
OR drug 3 = [Aricept]
OR drug 4 = [Aricept]
OR drug 5 = [Aricept]
OR drug 6 = [Aricept]

drug 1 is the field title exactly, Aricept is the drug and table - main is
the table title. Are all these correct? Do I need to use the expression maker
or the ... in the properties menu? or does this go straight in to a text box.
Again sorry for all the requests for help.

Many thanks
Hal

While the approach suggested to you by Tina is far better, yes, you can use
a parameter:

SELECT Count(*)
FROM TableName
WHERE Field1 = [What Drug?]
OR Field2 = [What Drug?]
OR Field3 = [What Drug?]
OR Field4 = [What Drug?]
OR Field5 = [What Drug?]
OR Field6 = [What Drug?]

It's absolutely critical that you type the 6 parameters identically: any
differences in spelling or spaces will result in multiple prompts.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi there Rick

Thanks for replying

So with this set of instructions can I have it so I can put in another name
as with a query [ ]? As you may be able to tell am an amateur at this sort of
thing!!!

Many thanks
Hal

Hal wrote:
I need to search across six fields, which will enable me to get an
idea of the total amount of drugs used. I need to do this without
having to query every field though.

ie, search for quetiapine, which in one record is in field one, in the
second record, quetiapine is in field three and in the third record
the quetiapine is in field six.

How do I find out that there are three quetiapines without going to
search all the fields?

Sounds like your table design is incorrect. With the current design you
have no choice but to search all of the fields.

SELECT Count(*)
FROM TableName
WHERE Field1 = "quetiapine"
OR Field2 = "quetiapine"
OR Field3 = "quetiapine"
OR Field4 = "quetiapine"
OR Field5 = "quetiapine"
OR Field6 = "quetiapine"

 

[John Vinson]

WOW!!!! I have read through the Tina suggestion and being a bit of newbbie as
we have now certainly ascertained, I am in a flumux again. tbls and queries
and all sorts. Can I run what I think the instructions are:

1. Create a table for each element, 1 for personal information etc, 1 for
drugs information.

Here's my suggestion for the three tables: Tina's is very similar.

Patients
PatientID
<biographical & contact info, no data about drugs at all>

Drugs
DrugID
DrugName
<other info about the drug, e.g. manufacturer, cost, whatever>

Prescriptions
PatientID << link to Patients
DrugID << link to Drugs

Then create a query to link the two.

Within the query how the devil do you link the two? (I am so sorry for sound
like acomplete "ass" in this but I am rapidly noticing my amateur side!!!!

Hey, don't be hard on yourself! Access is a MUCH more complex
application than anything else in the Office suite, and relational
thinking doesn't come automatically.

Once you've created the Tables open the Relationships window (looks
like three boxes with lines joining them). Add all three tables to the
window using the + icon. Drag PatientID from Patients to
Prescriptions, then drag DrugID from Drugs to Prescriptions. In each
case select the checkbox "Enforce Relational Integrity" - this will
ensure that you cannot issue a prescription to a nonexistant patient,
or for a nonexistant drug.

You add the [Patients] table to the query; then add the
[Prescriptions] table to the query; then add the [Drugs] table to the
query. If Access doesn't link them automatically (it should, if you've
defined the relationships), use the mouse to drag the PatientID field
from [Patients] to [Prescriptions], and then drag the DrugID field
from [Drugs] to [Prescriptions]. You can then select whichever fields
you want to see in your report.

This query will NOT be updateable. You don't want to update using a
table or query datasheet in any case! Instead, create a Form based on
the Patients table, with a Subform based on the Prescriptions table.
The Form Wizard will help with this.


John W. Vinson[MVP]