Drop Down List

[warburger]

Hello,

I'm having some trouble regarding 'same entries' selection...

I.E. I have the following data

List of Hospitals Machine Used Machine Readings

Hospital A Roche Analyzer 12.65
Hospital B Abbot Analyzer 25.9
Hospital C Vitros Analyzer 1.45
Hospital A Dimension Analyzer 3.6

I've structured a Report Form that contains a drop-down list fo
selecting the hospital, then upon selection (let's say Hospital B), th
machine used and the machine reading will appear in the form (Abbot an
25.9). This works out great for Hospitals B and C..but not wit
A....Upon selection of the first Hospital A (though in the drop-dow
list, the other Hospital A is at the bottom, the values for it wil
appear (Roche and 12.65). However, upon selection of the secon
Hospital A, the same values appear (First Hospital A values) and no
its corresponding data (Dimension and 3.6)....It's like it canno
recognize another entry bearing the same name....

Is there a way around this without renaming the second Hospital A t
"Hospital A (2)"? I want it to be named exactly as it is named (whic
is Hospital A).

Many thanks,

le

 

[RagDyeR]

I assume you're using something like a Vlookup formula to return the values
in the other two adjacent columns.

Even an Index and Match combo will still return the *first* match found.

You'll have to do something to differentiate between the 2 identical names.

Could you perhaps add the machine name to the drop down list:

Hospital A Roche
Hospital A Dimension
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


in message
Hello,

I'm having some trouble regarding 'same entries' selection...

I.E. I have the following data

List of Hospitals Machine Used Machine Readings

Hospital A Roche Analyzer 12.65
Hospital B Abbot Analyzer 25.9
Hospital C Vitros Analyzer 1.45
Hospital A Dimension Analyzer 3.6

I've structured a Report Form that contains a drop-down list for
selecting the hospital, then upon selection (let's say Hospital B), the
machine used and the machine reading will appear in the form (Abbot and
25.9). This works out great for Hospitals B and C..but not with
A....Upon selection of the first Hospital A (though in the drop-down
list, the other Hospital A is at the bottom, the values for it will
appear (Roche and 12.65). However, upon selection of the second
Hospital A, the same values appear (First Hospital A values) and not
its corresponding data (Dimension and 3.6)....It's like it cannot
recognize another entry bearing the same name....

Is there a way around this without renaming the second Hospital A to
"Hospital A (2)"? I want it to be named exactly as it is named (which
is Hospital A).

Many thanks,

lex

 

[Ryan.Chowdhury]

If I've understood your questions correctly, I think the answer is to
take a step back and view this as a database problem.

What you really have going on are three or four "tables"

table 1: Hospitals
table 2: Machines
table 3: Machines in Hospitals (optional)
table 4: Hospital, Machine Readings

separating the data like this will help with data integrity and in your
case, back end analysis

So let's assume a universe of 3 hospitals and 4 machines, where every
hospital has each of the 4 machines.

So, table 1 has 3 elements, table 2 has 4 elements and table 3 has 12
elements.
table1:
Hospital A
Hospital B
Hospital C

table2:
Machine 1
Machine 2
Machine 3
Machine 4

Table 3, is a composite table composed of elements from table 1 and
table 2. You can enforce referential integrity ("RI") by using data
validations to restrict the entries in table 3 to entries in table 1
and table 2. You'll want to have an extra column to identify each
unique hospital/machine combination. This is known as a primary key.

table3:
1 Hospital A Machine 1
2 Hospital A Machine 2
3 Hospital A Machine 3
4 Hospital A Machine 4
..
..
..
12 Hospital C Machine 4


Now table 4 is composed of elements from table 3 and readings entered
manually. Again you can enforce RI with data validations. In this
case, you'll need to use the ID# for the hospital, machine combo for
the reading in question

table 4:
1 12.65
3 1.45
7 4.6
8 25.9

3, in the example above refers to Hospital A, Machine 3. You may want
to add dates to table 4 is there is a time series of readings.

Now, on the back end, you want drop downs to refer to table 1 (for
hospitals). Thus, you will be guaranteed a unique list in your drop
down. The result of the drop down selection, of course, would perform
analysis on table 4 to get some useful answers.

Hope this helps,

Ryan