Back To Basics Question

[Lusitan]

Hello guys.

Just starting a very simple (one should think) DB for keeping track of medical doctors. How should I approach this?

Doctors_Table should contain :
- Id
- name
- address
- state
- phone
- medical area
- addicional comments

Medical_Area_table
- Id
- Medical_area

State_Table
- Id
- State

The queries should only be able to produce results by Medical Area, by State or by last introducing/changing records.

Am I approaching this correctly? How should I relate these tables?
Could use your help on this.

Thank you very much.

 

[Crilen007]

Yea, but preceed each thing with the name of the table.

DoctorID(PK, AutoNumber)
DoctorAddress

etcc..

Also

in the doctor field, you want to have (so change the ones you have in that table already)

MedicalFieldID (number)
StateID (number)

And in the MedicalField Table, you want the ID to be Autonumber and PK, same with the StateID.


Now, link the stateID in the doctor table to the stateID in the state table..
do the same for the medical field ID.

 

[KenHigg]

What exaclty does 'last introducing/changing records' mean?

ken

 

[Crilen007]

Here.

Oh, please check through on how this was made.

I don't usually do this, but you looked like you were just starting out. Check the relation ships, and table designs.

Learn from this, dont just use it.

The append2table was from someone on this board a long time ago,

i do not get credit for that.

 

[Crilen007]

Oh, to add States and Medical areas, just type them in and it will ask if you want to add em.

 

[Debased]

You might also want to create a separate table for doctor sites with the addresses there instead of in the doctor table IF you need to keep track of more than one location OR if you need to keep a history when a doctor relocates. Use DoctorID to link.

Doctor Table

DoctorID
DrLastName
etc

Site table

SiteID
DoctorID
Address

 

[Pat Hartman]

I wouldn't prefix every field with the table name. That's overkill. I would use the table's name in the ID field though - DoctorID, MedicalAreaID, StateID, etc. Also avoid names that duplicate the names of functions or properties. Names like - Date, Year, Name, etc. will cause problems in VBA. NEVER use spaces or special characters in your object names either. Access doesn't complain but VBA will kill 'ya.

If by medical_area you mean the doctor's specialty, you need to define a many-to-many relationship because occasionally you'll run into a doctor that specializes in more than one area. So, remove medical_area from the doctor table and create a new table:

tblDoctorSpecialty
DoctorID (primary key fld1)
MedicalAreaID (primary key fld2)

Don't forget to define primary keys for all your tables. Then define relationships and check the enforce referential integrity box.

 

[Lusitan]

Thank you all for your input.

I'll study the example you've provided, Crilen007 (you're correct with your assessment on my Access knowledge) and I'll get back with my doubts. No doubt I will have them.

Thank you.

 

[Lusitan]

Crilen007, the example you provided pretty much covers it, with the alterations Pat Hartman mentioned (thank you).

But what about this:

What exaclty does 'last introducing/changing records' mean?

ken

Maybe I didn't make myself very clear. What I meant was how I could find out if a new doctor is introduced or if somone's address changes or something like that in a given period of time.

Example:

How many new doctors have been introduced this last month?

or

Doctor X has a new office. What were the address changes?

Something like this. Hope this makes it clearer.

Once again, thank you.

 

[Crilen007]

Ill make some additions.

 

[Crilen007]

Here ya go.,

I am not gonna take a ton of time on what it looks like, you can do that yourself.

 

[Lusitan]

Hello Crilen007,

Thank you for your post. I'm sorry I didn't post sooner, but the webserver at work was down today. I'll study your modifications now that I've downloaded the file and I'll get back to you.

Thank you

 

[Crilen007]

no problem.

 

[Lusitan]

Hi Crilen007,

I've checked out your updates to the file and I wonder if you can enlighten me on someyhing: editing the Add/Change doctor's subform's properties, I've stumbled across some VBA code.
Was that your doing or was it automaticly generated? Am I going to learn something about VBA to do the maintenance on the db?
The case is: this example you've provided suits perfectly my needs. However, since I'm portuguese, I have to translate it, and since I'm sure that altering the db would certainly damage it, I'm trying to do another one, copying your example and doing the translation simultaniously.

Or do you have any other suggestions for this procedure?

Thank you.

 

[Crilen007]

Just translate it. Just dont change the "name" of the things.

you can change what they say though.

the VB was my doing.

 

[Lusitan]

Just translate it. Just dont change the "name" of the things.

you can change what they say though.

I'm not sure I undestood you correctly. Do you mean change the items names directly on the tables or just on the forms?

If I change them on the tables won't there be some warnings about relationships or something? (just guessing here...)

Thank you.

 

[Lusitan]

Sorry about this, but just wanted to see if Crilen007 (or someone) could answer me this last question.