Re: Linked table advice

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

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.


.

Relevant Pages

  • RE: Linked table advice
    ... For example, you said that you need to track which antibiotics are tested, ... simple database so that paper records could be logged and found easily, ... Therefore I had to make the primary key a combination of the year and lab no. ... If I need to store the data in a separate table, ...
    (microsoft.public.access.tablesdbdesign)
  • Re: beak infection
    ... I assume I should separate her from the others. ... Do not accept antibiotics for it, ... to be the specific remedy. ...
    (sci.agriculture.poultry)