Laboratory work table linked to an alert table one to many join help

[crazy_ivan_1]

Hi all,

I am currently building a labwork tracking database.

In my main tracking table I have a.
ID(autonumber) - PK
patient_appt (date) - PK
patient_name (text) - PK
clinician (text) - PK
laboratory (text) - PK

I may not need that many primary keys, but I am new to access and I think they are required here to minimise duplication. THere are other data fields, but they are used primarily for querying dates and keeping track of storage areas.

What I am trying to do is have an alert table where if there are any issues associated with a particular patient they can be logged by date and a text description. So 1 patient connected to one ID number can be linked to multiple alert dates and text descriptions that are stored in a separate table.

I cannot figure our how to use primary keys and foreign keys to create the second table so that it is a one to many relationship that I can view multiple logged issues for 1 patient.

Thanks for any advice or guidance

Cheers

Andrew

 

[June7]

You only need one PK - the autonumber ID.

Can set a compound index with the other 4 fields to prevent duplicate combinations.

Have a field in log table to hold PatientID as FK

Build a form/subform arrangement with link on PK and FK fields.

 

[jdraw]

Further to June's advice, see this link on composite Unique index.

 

[crazy_ivan_1]

Hi June7 and jdraw,

I did what June recommended and actually found the weblink jdraw mentioned.

I found that using the composite indexes actually created more issues and I realised that I didnt need that many unique fields.

So I restricted the PK to just the autonumber in the main table

In my issues table i have the autonumber as the PK
I have patientID as a field with no PK
alertinfo
alertdate

In my forms when I call the issues form and I try and do the ID=patientID transfer to link the records but I cannot create more than 1 record per patient ID

im using this command on my main form page that opens a modal that allows me to enter the alert info. But If I click it again, it goes to the previous entry rather than a new entry

DoCmd.OpenForm "tbl_alert_info", acNormal, "patientID=" & ID

So I tried this

DoCmd.OpenForm "tbl_alert_info", acNormal, acNewRec, "patientID=" & ID

but this just opens a new empty record but the ID is not transferred.

Am I doing something stupid?

 

[Gasman]

The way I handled this was to pass through the ID as OpenArgs, and in the opened form test if new record.?

DoCmd.OpenForm "frmSubmitterClient", , , "ClientID = " & Me.ClientID, , acDialog, Me.ClientID

Private Sub Form_Current()
If Me.NewRecord Then
    Me.ClientID = Me.OpenArgs
End If
End Sub

There might be better ways though?

HTH

 

[The_Doc_Man]

Andrew, forgive me for the observation, but you are obviously new to Access. You need to appreciate some fine points about indexes. And please don't take my comments as being critical. You came here for advice, so I'm offering a micro-tutorial on indexes.

You have multiple ways to use indexes. One of them is to create a table's primary key. That PK is used so that you can establish relationships between tables where a copy of the PK on table A becomes a foreign key on table B to identify "child" or "dependent" records in B. (More about this in a moment.)

Another use for indexes is SEARCHING. If you intend to write queries to group together patients in that main table, you need an index on patients. However, the odds are quite great (based on what little you showed us) that "patient" in your "main" table should probably be an FK to a separate table of patients for which you have some kind of patient number as the PK for that table. That is because in that "main" table, patient will almost surely be repeated frequently and thus is not unique.

Having indexes on your dates becomes useful if you want to search by dates. Having indexes on your clinician and laboratory fields makes THAT kind of searching work. But none of those fields should actually be part of a PK because individually, NONE of them will be unique. You probably could make a unique index by combining enough fields. However, Linking tables together requires that you link ALL FIELDS of the PK which means lots of duplication of data. I.e. you would have had to include a date and three text fields in the child table that depended on your main table.

There is a limit to how many of these should be considered as a PK. I can tell you flat out that dates are almost never good candidates for being a PK. In your situation, I would recommend use of a synthetic key, perhaps a simple autonumber integer, and you appear to have that. Your ID field is sufficient for the purpose of linking, though in fact what YOU call your main table is itself dependent (or should be) on other tables that should be independent. Like your patient, laboratory, and clinician.

Another use for indexes - and this is legal but can sometimes be awkward - is to establish a constraint, and I think this is more of the intent of your original question. If you have a group of fields in a single table that IN COMBINATION can never be duplicated, you make a multi-field index with the UNIQUE property. This index would stop duplication of particular combinations, but be warned that it can be expensive for large tables with multiple fields participating. Part of the cost is that if you ever have to edit one of the index member fields, rewriting an index takes time in proportion to the (Number of FIelds x Number of Rows). Also, the efficiency of the index depends on its size, and it would appear that your combination of fields would be VERY large.

Finally, a comment on strategy. If you HAD made that combination of fields your PK, it would not have have had the effect you wanted because you had the Autonumber "ID" field as part of the key. Which means that every other field could have been effectively a constant but the presence of the autonumber field would have made the combination unique no matter what was in the other fields.

One more suggestion: You need to look up Database Normalization and study it a bit. With a normalized database, you can save yourself some space and some text repetition. To do this study, you need to search this forum for "Normalization" or the web for "Database Normalization." You have to qualify the search on the world wide web because there are other types of normalization. Also, if you search the web, look at the domains of the references and limit your initial study to .EDU sites, because the .COM sites often have something to sell you.

 

[June7]

I recommended a form/subform arrangement, not one form opening another form. If you want the FK to automatically populate then build form/subform. No VBA would be needed.

 

[crazy_ivan_1]

Thanks everyone for your input and there are no feelings of criticism. I think that feedback is always helpful as there are often times we might overlook something.

I am a dentist by education and I am doing these databases to help me with my clinic management.

I will take all the suggestions and comments and try to build it in to what I am trying to do.

 

[Pat Hartman]

Private Sub Form_Current()
If Me.NewRecord Then
Me.ClientID = Me.OpenArgs
End If
End Sub

I prefer to not dirty the record with code. It is less confusing for the user if you wait to populate fields until he dirties the record. So, I would use:

Private Sub Form_BeforeInsert()
    Me.ClientID = Me.OpenArgs
End Sub

The BeforeInsert event runs once per record after the user types something into any control. And it only runs for new records so you can eliminate the If.

 

[Gasman]

I take your point Pat, but your way gives no indication to the user who the record is for?
I wanted to show the user (mainly me) the correct client that the record was going to be linked to?

I was using a combo for ID, FirstName & Surname to confirm the client name, so a simple Dlookup would not work, I would need at least two. However the DB is not used anymore, but I'll take the feedback onboard, in case I ever need to do something similar in the future, however unlikely that would be.

 

[Pat Hartman]

If you want to show the user some identifying information up front, use unbound controls. They won't dirty the form and contribute to the problem of creating "empty" records.

 

[zfar]

Another point on structure:
One type of test can be taken by many patients
One patient can have many tests of the same type
One clinician can have many patients
One clinician can order many different types of test.

You will need some junction tables to cope with many to many relationships.
In your tracking table, you won't be able to have clinicians, patients or tests as Primary keys that are unique.

Even composite keys can be difficult in this scenario.
You might have thought you could make a composite key out of:

• patient_appt (date) - PK
• patient_name (text) - PK
• clinician (text) - PK
• laboratory (text) - PK

But it is even conceivable that the same test could be repeated on the same patient on the same day.
I would recommend the autoincrement number unique ID for each test ordered.

I would appreciate the experienced members commenting on this reply too.