Community Clinic Database help

[alwayshouston]

Hi All!
I have been given an assignment by local church to create a small database application to to track and report daily clinic visit information. I have design the tables to capture visit information of patients. But I am having problems with the designing of the database to include clinic daily information (such as clinic daily expenses, clinic daily revenues) in a table based on visit date. How would I create a relational design that would incorporate patient visit by date and clinical daily information only on DAY basis? What should be ideal design for clinic daily information table and how should I relate it to visit table? Let me explain the scenario with an example. Let say patients come to the clinic for their blood pressure check up. This information is inserted into tblVisit table. At the end of the day, if the clinic administrator wants to store information based on clinic (revenue, expenses etc...), how can you store this information in a table that relates to the visits by patients? Do you really have to relate it or you can create a table mutually exclusive of tblVisit to store daily clinic information.

Please Advise!
Thanks!

 

[GingerR]

so i want to be clear: when the administrator wants to store information about revenue, expenses, etc...that's is like "we bought 50 pens" or "a check came in the mail today" right? That's diff info that anything to do with 'visitors' so that can be an independent table.
here's how you'd have the basic structure of your tables with example fields:

Table1: "CLINICS"
Field: ClinicID
Field: ClinicAddress
Field: ClinicAdministrator

Table2: "VISITORS"
Field: VisitorID
Field: VisitorName
Field: VisitorAddress

Table3: "VisitorVisits"
Field: VisitorID (from Table2)
Field: VisitDate
Field: VisitDesc

Table4: "ClinicAccounting"
Field: ClinicID (from Table1)
Field: Type (Revenue or Expense)
Field: Amount (dollars)

-------------------------------------

I don't know how much you know about databases or what all you have done so far, but you'd probably want to use subforms (look in HELP). You'd have a little pop-up form that first opens when someone opens the database. There could be buttons:

-----------------------------------
1) Add Clinic
2) Clinic Accounting
3) Add New Visitor
4) Visitor Visits
5) Reports
-----------------------------------
1) would open where you can add more data to table #1.
2) would be a form based on table 1, with a subform based on table 4 ("ClinicAccounting") where people enter Revenue and Expenses for any date.
3) Would be adding new data to table #2
4) would be a form based on table 2, with a subform based on table 3 (in datasheet format) which shows an on-going list of visits for a visitor.
5) in REPORTS, you'd maybe make an accounting report by Clinic, where you add revenue and subtract expenses. It's not a good idea to then store this information - it's best to store the raw data and do calculations on the fly in queries and reports.

So this is sort of the basic set up. Hope this helps.

g

 

[alwayshouston]

Thanks Ginger!
I have created the database in the same exact design as you had recommended in your post. I have one minor concern/issue. My concern is with the ClinicAccounting table. I want the clinicAccounting to store information based on the visit dates. For example, if the patient visited the clinic on 11/1/2004, the ClinicAccounting should have data for that specific date i.e. 11/1/2004. How would I create a relationship from the visit table (visitdate) to ClinicAccounting table?
Thank you very much for your prompt feedback!

 

[GingerR]

is the accounting data specific to the visitor? like do they pay? what are examples of the data that goes into the ClinicAccounting table and why/when does it go there?

so you would just want the ClinicAccounting table to have only a date in it?

sorry for all of the questions, I'm trying to get it all straight )

 

[alwayshouston]

The accounting data is not specific to the visitor. Its relevant to the clinic for each visit day. The data that goes into ClinicAccounting is as follow:
* Amount of money spent on stationery (expense)
* Amount of money spent on rent for special meeting (expense)
* Amount of money for catering for volunteers (expense)
* Revenue come from donation (revenue)
etc...
I do want the ClinicAccounting table to have date in it, but that date must exists in Visit table. How can I accomplish that in my design? I want my application to have a form for clinic administrator to input expenses and revenues relevant to visit date. I hope I am making sense. Don't hesitate asking questions for clarification.

Thanks again for taking time to address my problem.
I really appreciate it!

 

[GingerR]

why are you trying to connect the Accounting Date with the Visit Date? From what you say, they are exclusive of each other.

Monday - 5 people visit
Tuesday - 0 people visit but the clerk puts in 2 accounting records because they bought coffee and pens.

the two have nothing to do with each other.

In the ClinicAccouting table, there should be a date field that the administrator enters when they put in an expense or income record. An easy thing to do, if you want to make it so the administrator doesn't have to actually type "11/15" is to make the default of that field be "=date()" and it will put in the current date (which they can then alter if they want).

Again, it sounds like from your description that you are trying for some reason to connect the dates for visits and the dates for accounting, which are two entirely different things.

ClinicAccouting table will look like this:

ClinicID Date Type Amount Reason
1 11/15/04 Expense $15.00 Pens
1 11/15/04 Expense $13.59 Coffee

VisitorVisits table will look like this:

ClinicID Date VisitorID
1 11/14/04 13
1 11/16/04 13

so they are exclusive. what if no one comes on the 15th? then what?

 

[alwayshouston]

You are right on your logic, but my scenario is a bit different with various assumptions. Your logic is more accurate and takes in consideration of fixed/variable expenses. Unfortunately, the requirements given by the clinic is different. They want to store daily expense/revenue information for each day when the clinic is open. For e.g., if the clinic did'nt see any patient that day, there should not be any record in ClinicAccounting table because no expense or revenue was generated. If the patients come to the clinic, there are revenues or expenses related to the clinic for that day. Just take meal expense for the volunteers as an example. It can't incurred unless the clinic is open. Therefore, meal expense should be recorded in the ClinicAccounting table based on the day patients visited (or in other words the clinic was open). I hope I am clear on the logic. How would design the ClinicAcounting table in above scenario?
Again, thanks for your prompt reply!

 

[GingerR]

OK, *NOW* I get it )

make another table, ClinicDates.
field = ClinicID (number)
field = ClinicDate (date)
make both fields PRIMARY KEY

adjust the existing tables as shown in RED below:

Table1: "CLINICS"
Field: ClinicID
Field: ClinicAddress
Field: ClinicAdministrator

Table2: "VISITORS"
Field: VisitorID
Field: VisitorName
Field: VisitorAddress

Table3: "VisitorVisits"
Field: ClinicID (number)
Field: ClinicDate (Date)
Field: VisitorID (from Table2)
Field: VisitDate
Field: VisitDesc

Table4: "ClinicAccounting"
Field: ClinicID (number)
Field: ClinicDate (Date)
Field: Type (Revenue or Expense)
Field: Amount (dollars)

THEN:
1) Make a form based on Table #1 (Clinics)
2) Drag the new table (ClinicDates) on to it to make a subform. Make the Child and Master Links = ClinicID.
3) Drag Tables #3 & #4 onto the 2nd subform. Make the child and Master links = ClinicID;ClinicDate

so it will be this:

MAIN FORM = "CLINICS"
SUBFORM1 = CLINICDATES
Subforms within CLINICDATES subform = Accouting and Visits

so everything is tied to dates. as soon as you enter a date in the subform based on CLINIC DATES, all of the data in the two lower-level subforms will pertain to that certain ClinicID AND that certain DATE.

 

[alwayshouston]

Thanks Ginger! but how does the ClinicDate in ClinicDates is populated? With your solution it does not get populated automatically through the visit subform. Does the user have to populate the ClinicDates manually? I would want the ClinicDate to populate when the visit table has records for a particular date. For e.g., if the patients visit the clinic on 1/1/2004, the ClinicDate should have one record for that date. If multiple patients visited the clinics on 1/1/2004, it should only store one date to maintain the referential integrity. Any thoughts on how to accomplish above scenario?

 

[GingerR]

How exactly do you currently have the database forms set up? When a clerk opens the database, what are they seeing exactly? Do you have a menu or switchboard? How do they choose which clinic they are at and see only the data for that clinic? How do they get to the Accounting form for a single clinic? How do they get to the Visits form for a single clinic?

The way I proposed to set it up, a clinic date must be filled in manually (or you can program it to the current date) and then the dates for Visits and Accounting get filled in automatically because of the way the MASTER and CHILD links are set up. There is ONE combination of ClinicID and ClinicDate, so there is integrity.

 

[alwayshouston]

Thanks Ginger for your help! I deeply appreciate it.

 

[GingerR]

No problem helping, I enjoy it. After all, lots of people have helped me along the way...


g