split or merge ..please help

[mhmaid]

need your help please
first I will till you what is my work.
I am working in a govermental office
I recieve and account patients treatment invoices from varios hospitals , in
many countries.

for some countries , invoices will come to me thru another gov. office. and
in this case , they will pay the invoices , then send to me along with the
cash payment voucher , to pay amount due to them.

ok , now , one letter(batch) I receive may contain the following
1.covering letter ( as a batch)
2.cash payment voucher ( may contain many invoices for many patients)
3.one invoice may be for many visits for the same patient.
4.our system is to add a new record for each visit, as sometimes patient is
sent to diff country , diff Doctor etc.

currently , I have four tables to do this.
table 1:I enter cpv No , date , amount,Exchange rate, currency.
table 2 : I enter Invoice number, date, issuer, amount, Discount
table 3 : I enter caseid , Exp rpt amt ,
table 4 : expensecategoryid, datefrom,dateto,expenseitemamount.

one record in table one may contain many records in table 2
same thing to the rest of tables.

is this the correct way.
any ideas.
offcourse i have the main table wich is "Patients" will hold
name,dob,hospital,etc.

 

[vbasean]

I would say tht your post contains many of the 'tables' you need
'Patients'
'Invoices'
'Visits'
'Cash_Payment_Vouchers'
if you have many to many relationships you'll need an intermediate table
that contains the ID from each table and whatever information belongs to each
instance.

 

[vbasean]

A 'Patient' has 1 to many 'Invoices'
Patients.PatientID=Invoices.PatientID

An 'Invoice' has 1 to many 'Visits'
Invoices.InvoiceID= Visits.InvoiceID

a 'Cash_Payment_Voucher' has 1 to many 'Invoices'
Cash_Payment_Voucher.Cash_Payment_VoucherID = Invoices.Cash_Payment_VoucherID

if an invoice can have more than one payment then you need an intermediate
table
Invoice_Payments:
Cash_Payment_Voucher.Cash_Payment_VoucherID,
Invoices.InvoiceID,
Amount,
Date

You may have tables for:
Countries
Facilities
Goverment_Offices

depending on how detailed your application is.

 

[mhmaid]

thank you for reply
let me make it more clear

my db is a developed copy of the sample db that comes with the windows
access , named as "exense reports"

now , my main table is "patients" which holds patients data like dob , name
of doctor etc.

the remaining tables are the following in order
1.CashPaymentVouchers , primary key is CpvId
2.Invoices, InvoiceId , and the related cpvid ( many invoiceid for one cpvid)
3.Expense reports , ExpenseReportid, and the related invoiceid , here also,
many Expensereportid for one invoiceid)
4.Expense details, Expensedetailsid, and the related Expensereportid , here
also , many Expensedetailid for one Expensereportid

as you see , the link between the main table "patients" holding Caseid is on
the third table "Expense reports " not the first table which is
"cashpaymentvouchers"


previosly , as i have mentioned that i am using a developed copy of access
sample "Expense reports" , i used to have two tables instead of the above
four tables . but in that case i have to enter some data many times i.e. if
one invoice belongs to two visits ,i used to enter the invoice number , date,
two times (one for the first visit, second for visit 2) same thing for many
other fields.

the table order is important to me cs one cpv may have many invoice, one
invoice may be for many cases(visits)

thanks

 

[vbasean]

Templates are a great place to start but I'm totally not understanding how
Employee Expenses relate to Patient Invoices.

Can you please explain.

 

[vbasean]

I took a look at the 'Expense Report' template.

I'm assuming that the reason you picked this model is because it somewhat
mimicks the:
Patient / Invoice / Visit
type senario. Where you have:
Employee / Expense Report / Expense Item

Although the template has a great looking GUI (form set up) my opinion is
that it's more work to rework a database created for one purpose into
another, than it is to just start from scratch.

If anything, copy the form pieces as you desire and implement them into your
own forms based off of a correct database model.

Going back to the original post.
You have some basic business objects:
- Patients
- Invoices
- Visits
- Cash Payment Vouchers

These are your beginning tables.
- ONE Patient is related to MANY Invoices
- ONE Invoice is related to MANY Visits
- is ONE Cash Payment Voucher related to MANY Invoices?

Trying to match the structure of your data needs to a template based off of
a different business problem/model is (in my opinion) not a good idea.

 

[mhmaid]

actullay I was new to access , thats why i used this template to start.
then , I have added many new fields to each table, renamed most of the other
fields to match my needs
so , all fields related to "employee" became related to 'patient" with new
names.
employeeid , became Caseid
etc.
so , currently , there is nothing related to employees in my db.

the only thing is I am thinking of a better structure for my tables.