Re: Linked table advice

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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 spread***
scenario.

"KateB" <KateB@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E0F32CA0-5247-4E90-A46B-B58CC3CE93C3@xxxxxxxxxxxxxxxx
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" <KateB@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:9F110680-9D2E-4188-8C17-8C729B8034A6@xxxxxxxxxxxxxxxx
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.






.

Quantcast