Relationships: I know what I want, but dont know how to do it!

[Aimee]

I have a database with a table that records patient details, including each persons 'Practice Name' and the ' Practice Code'.

Each practice name has a unique practice code. Rather than filling out both of these fields for each patient I want to be able to write the Practice Name, and for Access to know which code this corresponds to (preferably for this to be displayed automatically).


So far I have for so far as to create a separate table with each name and its code. What do I do next?

[Bob Fitz]

This sort of thing is usually done within a form.
The form would have a combo box which uses the table as lts Row Source property.
It would show/auto fill with the Practice name.
The form would also have a text box which references the second (probably hidden) column of the combo box which would be holding the Practice Code data.

[Bob Fitz]

Take a look at the attached db.

[orange]

For a great tutorial on tables and relationships

see http://www.rogersaccesslibrary.com/T...lationship.zip

[Aimee]

Thank you very much for your feedback - very helpful

[Aimee]

I think I am on the right track with the 'design' now, and have created the form with a combo box/drop down menu for the practice name.

I have added a text box for the practice code, but how do I make this display the corresponding code automatically once I have selected the name?

[orange]

Without a picture or some code and a clear statement of your business rules, it is difficult to offer advice.

[Aimee]

Without a picture or some code and a clear statement of your business rules, it is difficult to offer advice.

If I were to send you the file (minus any pt info of course), would you be able to help?

[orange]

Just post your database with some (non private/non confidential) data.

[Aimee]

Apparently the file is too big - do you have an email address?

[Aimee]

I have tried to convert it to zip - Let me know if you get this..

[orange]

The zip file is fine.
Why do you have 2 copies of the Practice codes table?
In plain English what does
a) your database represent, and
b) what is the purpose of your Form?

You should research Primary key and Foreign key.
see http://technet.microsoft.com/en-us/l.../ms179610.aspx

[Aimee]

I have an extra table with the practice codes because I thought that I might have to create some kind of relationship or junction for access to know which code goes with each practice name.
...I have obviously gone completely wrong somewhere because when I type the practice name into the form, this now changes into the code.

My database tracks the process of care for each patient receiving treatment. This information needs to be filled out by form. Both the practice name and code of where the patient is being treated need to be recorded, but rather than entre this manually I was hoping that there was a way for Access to recognize that each practice name relates to a specific code, and for this to be displayed on the form and database automatically.