Question Linking Information in a table/form

[mrpwagner]

I have spent MANY hours looking on the internet. reading two texts and just crunching to no avail. This is what I woud like to do.

A bit of history. I have a table/form called Patient. Each patient has a medical record number (MRN) In this table the MRN is the priamary key (no other patient can have that number). Remaining information is last name, first, etc. demographics.

I have a patient procedure table/form. As a safety measure I am planning on creating a combo box that will take the MRN from the patient table. Piece of cake. This is what I can't seem to do; I would like a field where, once I type in the MRN the corresponding last name appears to confirm we have tha right patient.

I tried a page from administrative arts titled create ans auto fill-in form in access.
When I took the information provided on this webpage it worked; when I did it EXACTLY using my access form it first worked but then didn't work once I started adding more relationships.

So what I am looking to do is figure out how to go about placing a MRN in a field and have the last name field automatically populated and placed in the patient procedure table.


Thanks for any help you can provide.


Mike

 

[John Big Booty]

Have a look at the sample posted here, it should give you some pointer. In stead of postcodes (zip codes) you would be talking MRN and instead of suburb you would be talking paitient name. And you would not need to cascade from state, so you can ignore that portion of the sample.

 

[mrpwagner]

I have downloaded this and studied this. I am still a bit unsure as to how to proceed. Do you have any suggestions? Do I need to copy everything into a VBA box? Keep in mind that I am a newbie and I am not used to a lot of this programing.
Why is there an unbound box labeled text 13 in the design view on the form address form? To what purpose does this serve? While this example appears to serve my purposes, I am not certain how to translate this to my needs. Any additional information that can be provided would be helpful.
Thanks
Mike

 

[John Big Booty]

Text13 is a control hidden behind the suburb combo combobox and is just there as a temporary tab stop for part of the procedure to sprevent the user from tabbing to a new record prior to properly completing the current record.

The Green comments will tell you what each section of the code is designed to do.

 

[gemma-the-husky]

p[ersonally I would be very cautions about using the MRN as a PK. I would use an autonumber, and add the MRN to the patient table, as a unique key - but not as a FK in other tables

Reason: You will get some cases where you either a) dont have a MRN, or b) enter it wrongly. Either way, its a problem if you are using it as a PK, and if you aren't, it isn't a problem.

 

[John Big Booty]

Here's a small sample DB that is probably more along the lines of what you are trying to do.

 

[mrpwagner]

p[ersonally I would be very cautions about using the MRN as a PK. I would use an autonumber, and add the MRN to the patient table, as a unique key - but not as a FK in other tables

Reason: You will get some cases where you either a) dont have a MRN, or b) enter it wrongly. Either way, its a problem if you are using it as a PK, and if you aren't, it isn't a problem.

As an emergency surgeon I anticipate re admissions. So I am planning a composite primary key composed from the medical record number, which will always stay the same, and the date of admission which should be unique. What are your thoughts on that?

 

[gemma-the-husky]

as I say, i would add a "surrogate" autonumber primary key. It isn;t there to maintain a sequence - just to provide a way of linking the patient table to other tables.

you can THEN add a unique key (which is akin to a PK, based on the MRN plus the date) - but the point is that you don't use this key to link tables. So it can be changed if you discover something wrong or need to maintain it.

The other thing is that in practice multi field keys are harder/more awkward to deal with - and are much less efficient for the dbs than a simple autonumber (ie long number) field


HOWEVER - the PATIENT table should not be including date of admisison at all. You need another admissions table

so you get

1 patient = many admisisons

and

1 admisison = many treatments

that way, you can establish a full history for the patient.

Having a patient table which includes MRN AND a date will mean duplication of data such as addresses, contacts etc etc. It will go out of synch, and errors will creep in.

 

[mrpwagner]

Gemma, thanks for the input. I will consider redesigning my tables based on your input. Thank you.
John, the query in the second database that you sent me is apparently dysfunctional and I could not open it but thanks for the effort.

 

[John Big Booty]

...

John, the query in the second database that you sent me is apparently dysfunctional and I could not open it but thanks for the effort.

Sorry Query1 is a left over from another DB and is irrelevant to the operation of this sample, please ignore it