Patients Record Table - Guide me someone!

[cluendo]

Hello Access Users!



I am planning to create a Medical Record database.
There are quick overview what I need:
A simple Form to enter the patients data( Name, LastName, Date Attended, Email, Address, Price Paid)
I need to create reports weekly and monthly.

So there is what I know how to do:
1. I know how to create Form I mentioned above, along with the table.

What I don't know:
1. How to arrange those weekly and monthly reports( Reports should be in order of date and ID. for example weekly report from 1 to 32 and from 01/12/2010 to 05/12/2010. )
2. Do I need separate Tables and Forms for each month, or 1 Form for each month table, or I can simply use 1 Form and One table for all record and then somehow manage to get the weekly and monthly reports ?

3. How to delete some error records from the table without affecting order of ID ?

Thank you for reading, maybe someone will guide me how to sort those problems.

[pbaldy]

1) Not clear on what you're asking here.

2) You absolutely positively do NOT want separate tables/forms for each month. You want a single table and form, which would include a date field. With that date field, you can sort and group the data any way you want.

3) Think of the table as a bucket holding the data. You don't care what order the records are in. You use the date field or other appropriate field(s) to sort the records when viewing on a form or report.

By the way, the typical normalized design would be two tables, one with just patient info, with one record per patient, including a patient ID of some sort. Then an activity table that included info about each visit, with a field for patient ID that links back to the patient table. In the activity table, you wouldn't have the name, email, etc. You'd only have info related to the visit; date, price, etc. That way you're not duplicating all the patient info every time they visit.

[cluendo]

Thank You for your help and tips! They are really useful.
Ok, I will try to explain 1st step: How will I create report for printing each week? For example: I have a filled records table of 1 month (for instant 300 people) how will I create separate reports for each week of the month ? Is it involves VB programming ?
Thank You again!

[pbaldy]

Typically you would create a parameter query. It would allow the user to input the date range they wanted to see, typically on a form. Then you'd base a report on that query.

[cluendo]

pblady, thank You so much, I managed to get everything I wanted by using your guide. Now I have: 1 Table for records, a proper "Add New Patient" form, have a Query based Report in which I can choose Dates I want, and I have a "Patient List" where is a search field, so I can find patients very quickly. But as you mentioned before, I need TWO tables - one for Names and other for dates,prices, etc... I was thinking how I will do this, but nothing came to my head(probably lack of knowledge of MS Access ) ). One thing I need to mention - I used "Contacts" sample database and modified it to my requirements. But there as couple of problems:

1. How to have a proper "Patient List" without Names and Last Names duplication. For example: If I add the same "Name and Last name" using my patient filling Form, how to make it add to the same patient "ID" and somehow update and note new "Visit Date" and "Price". Without affecting my weekly reports which should be made separately with stated dates and patients which visited between the stated date.

If something is unclear feel free to ask, I will try to explain more!

Than You!

[cluendo]

Ok guys, I will be more specific.Basically what I need is Group the duplicate records by Name and Last Name. Each group need to have a separate table for "Atend Date" and "Price". Thats it. So for now I have two tables - one for "Contact names,address, and etc." and other for "Price, Date attended". I havo those tables in "relationship" so that there is a little "+" sign in the main table. I kind of know how to do this, but what is unclear is how to prepare a Form which would have "fields" to enter data in those TWO tables(because now the form is just for 1 table". And how the Form will "detect" and somehow "update" the existing record if it is entered twice.

Thanks Again!

[pbaldy]

If I'm understanding correctly, you would typically have your form bound to the "activity" table. You would use a combo box that got its selections from the "patients" table. You can use the Not in List event of that combo to add new people to that table. Searching here for "notinlist" should turn up a couple of methods.

[cluendo]

I looked at NotInList event but I am not sure if it is a right choice for me? Again I will try to tell what I would like to achieve. Also I uploaded my DB. As I said before it is a modified version of Sample "Contacts" DB which you can find in MS Access 2010 version(not sure about 2007 and older versions...) So ok, if someone could look at the DB and tell me how to:
1. Avoid entering the Name And Last Name which is already in the Database.
2. If the Name and Last Name is found as existing record, update the information of that particular record to the other table(which contains basically price,date,address,e-mail,phone, etc.)
3. If the Name and Last Name is not in the record, then add as a new.
4. How to arrange the Form which would provide 1,2,3 steps ?

[cluendo]

Ups, I forgot to upload DB, so here you go.

[pbaldy]

Which are the two tables in question?

[cluendo]

Sorry about that - it is table "Contacts" and table "Comments". Table "Comments" should store info such as address,e-mail,date,price. Table "Contacts" should store Name, Last Name am I right ? At the moment table "Comments" has fields "comment" and "date". It is not modified completely in a way I want - I will do it after I will get an idea how to solve problems mentioned in the post above. I uploaded the DB to get an idea of what I want to achieve, I hope it is not too complicated )

Thanks for posting back!

[pbaldy]

I only have 2007, and it won't let me open the Contacts table. I would expect name, address, etc to be stored in Contacts (anything that relates directly to the contact). I'd expect date, price and anything else that directly relates to the comment to be in the comments table, along with the ContactID from the Contacts table. Are your questions 1-3 above related to maintaining the contacts, or entering data into the comments table? I'd use the not in list event for the second, a form bound to the contacts table for the first.

[cluendo]

Ok I started everything from sratch!
Now I have Simply two tables - Patients and PatiensInfo. Also I have two forms with the same names.

So I use ComboBox in order to add not existing record. Everything seems ok, but here is a code:

Private Sub PatientLook_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_PatientLook_NotInList

Dim intAnswer As Integer

intAnswer = MsgBox("Would you like to add this value to the list?", vbYesNo, vbQuestion)
If intAnswer = vbYes Then
DoCmd.RunCommand acCmdUndo
DoCmd.OpenForm "Patients", acNormal, , , acFormAdd, acDialog
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

Exit_PatientLook_NotInList:
Exit Sub

Err_PatientLook_NotInList:
MsgBox Err.Description
Resume Exit_PatientLook_NotInList
End Sub

One question would be: how to make the form "Patients"(which will pop up as a dialog box to add new info about new record) have filled the values I entered in combo box, so I will not need to re-enter ?

[cluendo]

Ok now I have NotInList code like that:

Private Sub PatientLook_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_PatientLook_NotInList
  
Dim intAnswer As Integer
Dim strsql
Dim NewFirst As String
Dim NewLast As String
Dim SpacePosition As Integer

SpacePosition = InStr(NewData, " ")
If SpacePosition = 0 Then
        MsgBox "Your value has no First Name."
        Exit Sub
        End If
    NewLast = Trim(Left(NewData, SpacePosition - 1))
    NewFirst = Trim(Mid(NewData, SpacePosition + 1))
    
intAnswer = MsgBox("Would you like to add this value to the list?", vbYesNo, vbQuestion)
If intAnswer = vbYes Then
DoCmd.RunCommand acCmdUndo
DoCmd.OpenForm "Patients", acNormal, , , acFormAdd, acDialog
strsql = "Insert Into Patients ([First Name], [Last Name]) values ('" & NewFirst & "','" & NewLast & "')"
CurrentForm.Execute strsql, dbFailOnError

Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
  
Exit_PatientLook_NotInList:
Exit Sub
  
Err_PatientLook_NotInList:
MsgBox Err.Description
Resume Exit_PatientLook_NotInList
End Sub

Something Is wrong in the Bolded area, how to set the values to the Form instead of a table ?

[cluendo]

The latest code looks like this :

Private Sub PatientLook_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_PatientLook_NotInList
  
Dim intAnswer As Integer
Dim strsql
Dim NewFirst As String
Dim NewLast As String
Dim SpacePosition As Integer

SpacePosition = InStr(NewData, " ")
If SpacePosition = 0 Then
        MsgBox "Your value has no First Name."
        Exit Sub
        End If
    NewLast = Trim(Left(NewData, SpacePosition - 1))
    NewFirst = Trim(Mid(NewData, SpacePosition + 1))
    
intAnswer = MsgBox("Would you like to add this value to the list?", vbYesNo, vbQuestion)
If intAnswer = vbYes Then
DoCmd.RunCommand acCmdUndo
DoCmd.OpenForm "Patients", acNormal, , , acFormAdd, acDialog

Forms![Patients]![First Name] = NewFirst
Forms![Patients]![Last Name] = NewLast



'strsql = "Insert Into Patients ([First Name], [Last Name]) values ('" & NewFirst & "','" & NewLast & "')"
'CurrentForm.Execute strsql, dbFailOnError

Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
  
Exit_PatientLook_NotInList:
Exit Sub
  
Err_PatientLook_NotInList:
MsgBox Err.Description
Resume Exit_PatientLook_NotInList
End Sub

HINT: There are two(or maybe 3 as I do not know if [ID] counts) values in the ComboBox which are - one field: [ID] from table Patients and second field: [Last Name] & ' ' & [First Name] .