Linked table advice

I have a database that records specimen results. It was meant to be a nice
simple database so that paper records could be logged and found easily, but
seems to expand every 6 months! I was originally told that the Lab no. was
unique, only to then find out that it isn't, they may be re-used annually.
Therefore I had to make the primary key a combination of the year and lab no.

I have now been asked to add which antibiotics have been tested, and whether
they are resistant or sensitive (R or S or blank if not known). There are 22
antibiotics that are tested. My question is, should I add 22 columns (one
for each) so that it can be marked R or S, or create a separate table? The
former I know how to do, but I suspect it may be better to do the latter from
a 'correct design' perspective?

If I need to store the data in a separate table, how do I:
a) get the combined unique ID to automatically go to the 2nd table
b) would it be best to do this as a pop up form on the main form as there's
a shortage of space!
c) what would be the quickest way of recording the R, S, blank? Tick box
(messy?), list or text box?

Many thanks for any advice you can offer.

Your suspicions are correct in that you wIll want to create a table for the
antibiotics rather than adding 22 new fields to your current table. You may
need to provide a little more info in order for someone to give more specific
advice.

For example, you said that you need to track which antibiotics are tested,
so presumably you'll need a field to indicate if an antibiotic was tested, in
addition to whether it was resistant/sensitive etc. Also, do you need to
track what date it was tested, who it was tested by, etc?

As far as how to relate the tables, you would probably want each anitibiotic
to have a unique ID number, then you would add an anitibioticID field to your
other table and relate them via this field (or something similar). It's hard
to say exactly without knowing more.

--
_________

Sean Bailey


"KateB" wrote:

I have a database that records specimen results. It was meant to be a nice
simple database so that paper records could be logged and found easily, but
seems to expand every 6 months! I was originally told that the Lab no. was
unique, only to then find out that it isn't, they may be re-used annually.
Therefore I had to make the primary key a combination of the year and lab no.

I have now been asked to add which antibiotics have been tested, and whether
they are resistant or sensitive (R or S or blank if not known). There are 22
antibiotics that are tested. My question is, should I add 22 columns (one
for each) so that it can be marked R or S, or create a separate table? The
former I know how to do, but I suspect it may be better to do the latter from
a 'correct design' perspective?

If I need to store the data in a separate table, how do I:
a) get the combined unique ID to automatically go to the 2nd table
b) would it be best to do this as a pop up form on the main form as there's
a shortage of space!
c) what would be the quickest way of recording the R, S, blank? Tick box
(messy?), list or text box?

Many thanks for any advice you can offer.

On Thu, 20 Dec 2007 10:34:01 -0800, Beetle
wrote:

Your suspicions are correct in that you wIll want to create a table for the
antibiotics rather than adding 22 new fields to your current table. You may
need to provide a little more info in order for someone to give more specific
advice.

For example, you said that you need to track which antibiotics are tested,
so presumably you'll need a field to indicate if an antibiotic was tested, in
addition to whether it was resistant/sensitive etc. Also, do you need to
track what date it was tested, who it was tested by, etc?

As far as how to relate the tables, you would probably want each anitibiotic
to have a unique ID number, then you would add an anitibioticID field to your
other table and relate them via this field (or something similar). It's hard
to say exactly without knowing more.

And you can automatically link the main table with the antibiotic
table using a subform. The Master & Child fields will link them using
the common key between them as mentioned above.

Regarding the lack of space, using putting the antibiotic subform on
another tab would probably work better than a separate pop-up form.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

Hi Beetle, thanks for responding.

If an antibiotic is tested then it is returned as either R or S - I was
intending to leave the field blank if it wasn't tested, or use a 'not tested'
option.

Apologies - I didn't explain myself very well with regards to the rest of
the database. Basically we get a result slip from the lab with a lab ID
number which relates to a patient's test, and has the result of the test for
MRSA, urine infection, or 2 other options (each report relates to only one
test - if a patient is tested more than once, or for more than one infection,
they will have multiple lab reports, therefore one patient may appear several
times, but each lab report is individual). The current table records the lab
ID and year as primary key, name, DOB, date of test, site of infection, what
was tested for, date, GP, various other related items. Previously all we
needed to know was if the patient was positive or negative. The purpose of
recorded which antibiotics were tested for is to identify the strain of the
infection. (e.g. if resistant to antib.A and sensitive to antib.B, that can
be compared to other patients for cross infection). Therefore I need to
relate the patient's lab report back to the antibiotics, i.e. I think the 2nd
table needs the year&LabID primary key number to link to. Is this any
clearer? Its very difficult to explain something you do every day in simple
terms isn't it!

Thanks again for any help.


"Beetle" wrote:

Your suspicions are correct in that you wIll want to create a table for the
antibiotics rather than adding 22 new fields to your current table. You may
need to provide a little more info in order for someone to give more specific
advice.

For example, you said that you need to track which antibiotics are tested,
so presumably you'll need a field to indicate if an antibiotic was tested, in
addition to whether it was resistant/sensitive etc. Also, do you need to
track what date it was tested, who it was tested by, etc?

As far as how to relate the tables, you would probably want each anitibiotic
to have a unique ID number, then you would add an anitibioticID field to your
other table and relate them via this field (or something similar). It's hard
to say exactly without knowing more.

--
_________

Sean Bailey


"KateB" wrote:

I have a database that records specimen results. It was meant to be a nice
simple database so that paper records could be logged and found easily, but
seems to expand every 6 months! I was originally told that the Lab no. was
unique, only to then find out that it isn't, they may be re-used annually.
Therefore I had to make the primary key a combination of the year and lab no.

I have now been asked to add which antibiotics have been tested, and whether
they are resistant or sensitive (R or S or blank if not known). There are 22
antibiotics that are tested. My question is, should I add 22 columns (one
for each) so that it can be marked R or S, or create a separate table? The
former I know how to do, but I suspect it may be better to do the latter from
a 'correct design' perspective?

If I need to store the data in a separate table, how do I:
a) get the combined unique ID to automatically go to the 2nd table
b) would it be best to do this as a pop up form on the main form as there's
a shortage of space!
c) what would be the quickest way of recording the R, S, blank? Tick box
(messy?), list or text box?

Many thanks for any advice you can offer.

As soon as you create 22 more columns in your table, a 23rd will surface.
In any event, once you have more than one of something, you have many and
when you have many, you use a separate table and usually a subform to manage
the data.

Once my tables have "children" I switch to an autonumber primary key (use a
unique index to enforce the business rule of uniqueness for the two fields
that used to be the pk). I find it easier to work with in queries since
there will be only one field to join on although leaving the natural key is
not wrong. When you use a subform, Access will automatically populate the
foreign keys in the child record as long as the master/child links are
properly defined. If you use a popup form, you'll need to do this yourself.
The best place will be in the popup form's BeforeInsert event. You would
copy the two key fields from the open parent form to the foreign key fields
on the child form. Using the BeforeInsert event is preferable because it
eliminates the possibility that your code will be the first to dirty the
record. Many people use the open event, the load event, or the current
event. For this purpose, IMHO, the BeforeInsert event is correct.

I would use a combo for the antibiotics and a combo for the sensitivity.
both are easily expandable should more options become necessary. For
entering data in a combo, the user just starts typing and as soon as the
correct entry is found, tabs to the next field. This functionality means
that in most cases, your users will not need to use the mouse to do data
entry which most people consider a good thing.



"KateB" wrote in message
...
I have a database that records specimen results. It was meant to be a nice
simple database so that paper records could be logged and found easily,
but
seems to expand every 6 months! I was originally told that the Lab no.
was
unique, only to then find out that it isn't, they may be re-used annually.
Therefore I had to make the primary key a combination of the year and lab
no.

I have now been asked to add which antibiotics have been tested, and
whether
they are resistant or sensitive (R or S or blank if not known). There are
22
antibiotics that are tested. My question is, should I add 22 columns (one
for each) so that it can be marked R or S, or create a separate table?
The
former I know how to do, but I suspect it may be better to do the latter
from
a 'correct design' perspective?

If I need to store the data in a separate table, how do I:
a) get the combined unique ID to automatically go to the 2nd table
b) would it be best to do this as a pop up form on the main form as
there's
a shortage of space!
c) what would be the quickest way of recording the R, S, blank? Tick box
(messy?), list or text box?

Many thanks for any advice you can offer.

Hello KateB

Well, I'm not in the medical profession, but here's my opinion for what it's
worth.

I can't tell for sure from you post, but it sounds like your current setup
has the user manually entering the infection type in your lab report table.
If so, this is generally not a good idea because of spelling errors etc. I
would suggest adding a table for infection types as well as antibiotics.
(ditto for any other info that you have to repeatedly enter manually)

Your table structure might look something like the following. This setup
will be easier to manage if you get rid of your multi-part PK in the lab
table and use a single autonumber PK, so that's what I've done here (you may
want to create a new table and then append the existing records to it if
there are alot of them)

tblLabs
*****
LabID (PK)
LabNumber
LabDate
InfectionID (foreign key to tblInfectionTypes)
Other fields related to lab report

tblInfectionTypes
************
InfectionID (PK)
InfectionName
Other fields related to infection type

tblAntibiotics
*********
AntibioticID (PK)
LabID (FK to Lab table)
AntibioticName
Other fields related to antibiotic

Then you would use a Form/Subform setup where the main form would have the
lab info with a combo box to select infection types and the subform would
show the list of antibiotics that were used

I'm kind of winging this based on what I think about your DB, so you'll
probably need to make some modifications. If I've totally misunderstood your
DB structure (wouldn't be the first time) let me know.

HTH

--
_________

Sean Bailey


"KateB" wrote:

Hi Beetle, thanks for responding.

If an antibiotic is tested then it is returned as either R or S - I was
intending to leave the field blank if it wasn't tested, or use a 'not tested'
option.

Apologies - I didn't explain myself very well with regards to the rest of
the database. Basically we get a result slip from the lab with a lab ID
number which relates to a patient's test, and has the result of the test for
MRSA, urine infection, or 2 other options (each report relates to only one
test - if a patient is tested more than once, or for more than one infection,
they will have multiple lab reports, therefore one patient may appear several
times, but each lab report is individual). The current table records the lab
ID and year as primary key, name, DOB, date of test, site of infection, what
was tested for, date, GP, various other related items. Previously all we
needed to know was if the patient was positive or negative. The purpose of
recorded which antibiotics were tested for is to identify the strain of the
infection. (e.g. if resistant to antib.A and sensitive to antib.B, that can
be compared to other patients for cross infection). Therefore I need to
relate the patient's lab report back to the antibiotics, i.e. I think the 2nd
table needs the year&LabID primary key number to link to. Is this any
clearer? Its very difficult to explain something you do every day in simple
terms isn't it!

Thanks again for any help.


"Beetle" wrote:

Your suspicions are correct in that you wIll want to create a table for the
antibiotics rather than adding 22 new fields to your current table. You may
need to provide a little more info in order for someone to give more specific
advice.

For example, you said that you need to track which antibiotics are tested,
so presumably you'll need a field to indicate if an antibiotic was tested, in
addition to whether it was resistant/sensitive etc. Also, do you need to
track what date it was tested, who it was tested by, etc?

As far as how to relate the tables, you would probably want each anitibiotic
to have a unique ID number, then you would add an anitibioticID field to your
other table and relate them via this field (or something similar). It's hard
to say exactly without knowing more.

--
_________

Sean Bailey


"KateB" wrote:

I have a database that records specimen results. It was meant to be a nice
simple database so that paper records could be logged and found easily, but
seems to expand every 6 months! I was originally told that the Lab no. was
unique, only to then find out that it isn't, they may be re-used annually.
Therefore I had to make the primary key a combination of the year and lab no.

I have now been asked to add which antibiotics have been tested, and whether
they are resistant or sensitive (R or S or blank if not known). There are 22
antibiotics that are tested. My question is, should I add 22 columns (one
for each) so that it can be marked R or S, or create a separate table? The
former I know how to do, but I suspect it may be better to do the latter from
a 'correct design' perspective?

If I need to store the data in a separate table, how do I:
a) get the combined unique ID to automatically go to the 2nd table
b) would it be best to do this as a pop up form on the main form as there's
a shortage of space!
c) what would be the quickest way of recording the R, S, blank? Tick box
(messy?), list or text box?

Many thanks for any advice you can offer.

Hi Pat,

Thanks for the suggestion - my knowledge is limited but this sounds do-able!
I already have an autonumber field for each entry so can use that as the
link between the 2 forms. Another post suggested a tab rather than a pop-up
- is there a right or wrong choice?

The only problem I can see is that when the lab reports arrive they only
list antibiotics that have been checked - they don't always check them all.
Therefore if it isn't on the list the person inputting won't record that it
wasn't tested, which can be just as important. Also, when testing the
different organisms, different antibiotics are used, for example, MRSA has a
list of 12 antibiotics, ESBL has 10. Only 2 appear on both lists. Whether
it is MRSA or ESBL is recorded on the main form - can this field be used to
limit the combo list?

"Pat Hartman" wrote:

As soon as you create 22 more columns in your table, a 23rd will surface.
In any event, once you have more than one of something, you have many and
when you have many, you use a separate table and usually a subform to manage
the data.

Once my tables have "children" I switch to an autonumber primary key (use a
unique index to enforce the business rule of uniqueness for the two fields
that used to be the pk). I find it easier to work with in queries since
there will be only one field to join on although leaving the natural key is
not wrong. When you use a subform, Access will automatically populate the
foreign keys in the child record as long as the master/child links are
properly defined. If you use a popup form, you'll need to do this yourself.
The best place will be in the popup form's BeforeInsert event. You would
copy the two key fields from the open parent form to the foreign key fields
on the child form. Using the BeforeInsert event is preferable because it
eliminates the possibility that your code will be the first to dirty the
record. Many people use the open event, the load event, or the current
event. For this purpose, IMHO, the BeforeInsert event is correct.

I would use a combo for the antibiotics and a combo for the sensitivity.
both are easily expandable should more options become necessary. For
entering data in a combo, the user just starts typing and as soon as the
correct entry is found, tabs to the next field. This functionality means
that in most cases, your users will not need to use the mouse to do data
entry which most people consider a good thing.



"KateB" wrote in message
...
I have a database that records specimen results. It was meant to be a nice
simple database so that paper records could be logged and found easily,
but
seems to expand every 6 months! I was originally told that the Lab no.
was
unique, only to then find out that it isn't, they may be re-used annually.
Therefore I had to make the primary key a combination of the year and lab
no.

I have now been asked to add which antibiotics have been tested, and
whether
they are resistant or sensitive (R or S or blank if not known). There are
22
antibiotics that are tested. My question is, should I add 22 columns (one
for each) so that it can be marked R or S, or create a separate table?
The
former I know how to do, but I suspect it may be better to do the latter
from
a 'correct design' perspective?

If I need to store the data in a separate table, how do I:
a) get the combined unique ID to automatically go to the 2nd table
b) would it be best to do this as a pop up form on the main form as
there's
a shortage of space!
c) what would be the quickest way of recording the R, S, blank? Tick box
(messy?), list or text box?

Many thanks for any advice you can offer.

The choice between a pop-up and a tab is just that - a choice. My
preference is for tab pages but since your form is already built, it will be
more trouble to modify it than to create a pop-up which is why I explained
how the pop-up would work. If you suspect that there will be requests for
other types of data, you might want to bite the bullet now and rebuild the
form as tab pages. After that, additional tab pages will be trivial to add.
When I use tabbed forms, I use a separate subform on each tab page which
makes them a little easier to manage. In fact, you might try building a new
form with two tab pages and place your current form on the first tab.

If you keep a table of infections and a table of antibiotics, you can create
a junction table that will form the lists of antibiotics that are tested for
specific infections. You would then use that junction table to control what
shows in your combos.

Entering results for only the antibiotics that were tested is correct. You
can make use of the junction table to easily obtain a list of antibiotics
that were not tested in a particular case. Use the unmatched query wizard
to help you build the query. the absence of a row indicated not tested just
as the absence of a value would indicate not tested in the spreadsheet
scenario.

"KateB" wrote in message
...
Hi Pat,

Thanks for the suggestion - my knowledge is limited but this sounds
do-able!
I already have an autonumber field for each entry so can use that as the
link between the 2 forms. Another post suggested a tab rather than a
pop-up
- is there a right or wrong choice?

The only problem I can see is that when the lab reports arrive they only
list antibiotics that have been checked - they don't always check them
all.
Therefore if it isn't on the list the person inputting won't record that
it
wasn't tested, which can be just as important. Also, when testing the
different organisms, different antibiotics are used, for example, MRSA has
a
list of 12 antibiotics, ESBL has 10. Only 2 appear on both lists.
Whether
it is MRSA or ESBL is recorded on the main form - can this field be used
to
limit the combo list?

"Pat Hartman" wrote:

As soon as you create 22 more columns in your table, a 23rd will surface.
In any event, once you have more than one of something, you have many and
when you have many, you use a separate table and usually a subform to
manage
the data.

Once my tables have "children" I switch to an autonumber primary key (use
a
unique index to enforce the business rule of uniqueness for the two
fields
that used to be the pk). I find it easier to work with in queries since
there will be only one field to join on although leaving the natural key
is
not wrong. When you use a subform, Access will automatically populate
the
foreign keys in the child record as long as the master/child links are
properly defined. If you use a popup form, you'll need to do this
yourself.
The best place will be in the popup form's BeforeInsert event. You would
copy the two key fields from the open parent form to the foreign key
fields
on the child form. Using the BeforeInsert event is preferable because it
eliminates the possibility that your code will be the first to dirty the
record. Many people use the open event, the load event, or the current
event. For this purpose, IMHO, the BeforeInsert event is correct.

I would use a combo for the antibiotics and a combo for the sensitivity.
both are easily expandable should more options become necessary. For
entering data in a combo, the user just starts typing and as soon as the
correct entry is found, tabs to the next field. This functionality means
that in most cases, your users will not need to use the mouse to do data
entry which most people consider a good thing.



"KateB" wrote in message
...
I have a database that records specimen results. It was meant to be a
nice
simple database so that paper records could be logged and found easily,
but
seems to expand every 6 months! I was originally told that the Lab no.
was
unique, only to then find out that it isn't, they may be re-used
annually.
Therefore I had to make the primary key a combination of the year and
lab
no.

I have now been asked to add which antibiotics have been tested, and
whether
they are resistant or sensitive (R or S or blank if not known). There
are
22
antibiotics that are tested. My question is, should I add 22 columns
(one
for each) so that it can be marked R or S, or create a separate table?
The
former I know how to do, but I suspect it may be better to do the
latter
from
a 'correct design' perspective?

If I need to store the data in a separate table, how do I:
a) get the combined unique ID to automatically go to the 2nd table
b) would it be best to do this as a pop up form on the main form as
there's
a shortage of space!
c) what would be the quickest way of recording the R, S, blank? Tick
box
(messy?), list or text box?

Many thanks for any advice you can offer.

I'm on Christmas leave after today and this will have to be my New Year
project! Hopefully I've had enough advice to help me along the way, so many
thanks and also to the other posts. It is much appreciated. Regards,

"Pat Hartman" wrote:

The choice between a pop-up and a tab is just that - a choice. My
preference is for tab pages but since your form is already built, it will be
more trouble to modify it than to create a pop-up which is why I explained
how the pop-up would work. If you suspect that there will be requests for
other types of data, you might want to bite the bullet now and rebuild the
form as tab pages. After that, additional tab pages will be trivial to add.
When I use tabbed forms, I use a separate subform on each tab page which
makes them a little easier to manage. In fact, you might try building a new
form with two tab pages and place your current form on the first tab.

If you keep a table of infections and a table of antibiotics, you can create
a junction table that will form the lists of antibiotics that are tested for
specific infections. You would then use that junction table to control what
shows in your combos.

Entering results for only the antibiotics that were tested is correct. You
can make use of the junction table to easily obtain a list of antibiotics
that were not tested in a particular case. Use the unmatched query wizard
to help you build the query. the absence of a row indicated not tested just
as the absence of a value would indicate not tested in the spreadsheet
scenario.

"KateB" wrote in message
...
Hi Pat,

Thanks for the suggestion - my knowledge is limited but this sounds
do-able!
I already have an autonumber field for each entry so can use that as the
link between the 2 forms. Another post suggested a tab rather than a
pop-up
- is there a right or wrong choice?

The only problem I can see is that when the lab reports arrive they only
list antibiotics that have been checked - they don't always check them
all.
Therefore if it isn't on the list the person inputting won't record that
it
wasn't tested, which can be just as important. Also, when testing the
different organisms, different antibiotics are used, for example, MRSA has
a
list of 12 antibiotics, ESBL has 10. Only 2 appear on both lists.
Whether
it is MRSA or ESBL is recorded on the main form - can this field be used
to
limit the combo list?

"Pat Hartman" wrote:

As soon as you create 22 more columns in your table, a 23rd will surface.
In any event, once you have more than one of something, you have many and
when you have many, you use a separate table and usually a subform to
manage
the data.

Once my tables have "children" I switch to an autonumber primary key (use
a
unique index to enforce the business rule of uniqueness for the two
fields
that used to be the pk). I find it easier to work with in queries since
there will be only one field to join on although leaving the natural key
is
not wrong. When you use a subform, Access will automatically populate
the
foreign keys in the child record as long as the master/child links are
properly defined. If you use a popup form, you'll need to do this
yourself.
The best place will be in the popup form's BeforeInsert event. You would
copy the two key fields from the open parent form to the foreign key
fields
on the child form. Using the BeforeInsert event is preferable because it
eliminates the possibility that your code will be the first to dirty the
record. Many people use the open event, the load event, or the current
event. For this purpose, IMHO, the BeforeInsert event is correct.

I would use a combo for the antibiotics and a combo for the sensitivity.
both are easily expandable should more options become necessary. For
entering data in a combo, the user just starts typing and as soon as the
correct entry is found, tabs to the next field. This functionality means
that in most cases, your users will not need to use the mouse to do data
entry which most people consider a good thing.



"KateB" wrote in message
...
I have a database that records specimen results. It was meant to be a
nice
simple database so that paper records could be logged and found easily,
but
seems to expand every 6 months! I was originally told that the Lab no.
was
unique, only to then find out that it isn't, they may be re-used
annually.
Therefore I had to make the primary key a combination of the year and
lab
no.

I have now been asked to add which antibiotics have been tested, and
whether
they are resistant or sensitive (R or S or blank if not known). There
are
22
antibiotics that are tested. My question is, should I add 22 columns
(one
for each) so that it can be marked R or S, or create a separate table?
The
former I know how to do, but I suspect it may be better to do the
latter
from
a 'correct design' perspective?

If I need to store the data in a separate table, how do I:
a) get the combined unique ID to automatically go to the 2nd table
b) would it be best to do this as a pop up form on the main form as
there's
a shortage of space!
c) what would be the quickest way of recording the R, S, blank? Tick
box
(messy?), list or text box?

Many thanks for any advice you can offer.