Medical Database

[samia]

Hello Guys,

I appreciate all that you're doing and thank you for your time.

I would like to transfer pateint information from cards to access. I've so far managed to come up with the following tables. Please correct me if am wrong.

tblPatient
PatientID - PK
RegistrationNo (patient no on the card)
FirstName
LastName
DateAttend
etc, etc

tblRisks
RiskID
RiskType

tblHistory
HistID - PK
Date
Visit
Complaint1
Complaint2

tblOutcome
OutcomeID - PK
Complaint
Cause
Outcome

I want to be able to 1) link the tables, 2) move from the main form Patient and got into History table etc, 3) have the main form and history form together.

Thanks again for your time,

Samia

 

[Rich]

Since a patient can have more than one visit, date attended should be in a separate table with a one-to-many relationship with tblPatient

 

[Mile-O]

And Date should not be a field name in tblHistory - it's a reserved word in Access.

Can you, by all accounts, guarantee that someone can visit a GP and only have 2 complaints? Either way, if they only have one then that's still a lot of redundant space being wasted.

 

[ColinEssex]

You don't need to put the DateAttend in the patient details table. The patient details should only hold the 'fixed' details like address date of birth etc. (demographic data) then you'll only have each patient registered only once.

The attendance details (which is the 'many' side) go in a different table and you record everything to do with that attendance in there. Like new / follow-up, referral source, outcome etc etc.

You'll need a patientID field in any table that holds data relevent to that patient.

I would suggest (just thinking quickly) that you can have the attendances in a listBox on the main form if there's room then when you click on a date you can then have the full details shown for that attendance on another form.

Col

 

[Rich]

You might also want to store the family GP details etc

 

[samia]

Thank you all for the fast response.

CollinEssex, DateAttend is the date of registration (first). And the reason I've this is I didn't want to change to form so much. I want the screen to look exactly like the card.

Mile-O-Phile, that was just an example. I've provision for up to five complaints.

Lastly, how do I link the tables together? Is there a way after entering patient table to automatically jump to enter history? Sorry this basically sounds easy to do but am failing.

Thanks in advance,

Samia

 

[Mile-O]

Mile-O-Phile, that was just an example. I've provision for up to five complaints.

That's even worse. Do a search on First Normal Form (1NF).

 

[samia]

That's even worse. Do a search on First Normal Form (1NF).

OK, I get it. How bout if I code the complaints; say persistent headache = 1 and just enter the number 1. Will this solve the problem.

I greatly appreciate all your help. Am new to Access and I want to do a good DB.

Samia

 

[Mile-O]

Do a table for Complaints structured like this:

tblComplaints
ComplaintID (Autonumber) - Primary Key
Complaint

You'll also need to make a junction table to simulate this many-to-many relationship. There are many examples of this on the forum.

You'd add the complaints as part of a subform on your main form when you get past the table design stage and into the form design part.

 

[samia]

Mile-O-Phile, thanks again,

junction table to simulate this many-to-many relationship. There are many examples of this on the forum.

I don't seem to find it.

Thanks

Samia

 

[samia]

Do a table for Complaints structured like this:

tblComplaints
ComplaintID (Autonumber) - Primary Key
Complaint

You'll also need to make a junction table to simulate this many-to-many relationship.

OK, I finally found the many-to-many example. However, it just confused me the more. I've reduced my tables to three now like this;

tblPatients
PatientID - PK
FirstName
LastName

tblvisits
VisitID - PK
Date
Outcome

tblComp
CompID - PK
Complaint

Please help me.

 

[Mile-O]

You are missing two tables:

 

[samia]

Mile-O-Phile, I appreciate so much.

I'll look at this and try to follow. Just wish I could be able to do the forms like you did them.

Once again, thank you for your time.

Samia

 

[orbic1]

Samia, PM me if you need any support on this project as have developed several NHS patient DB solutions.

 

[mohammadagul]

Samia , EMail me on this as i have a predeveloped Database ready with me
mohammadagul@hotmail.com