If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
split or merge ..please help
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. |
#2
|
|||
|
|||
split or merge ..please help
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. -- ~Your Friend Chris http://myvbastuff.blogspot.com/ "mhmaid" wrote: 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. |
#3
|
|||
|
|||
split or merge ..please help
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. -- ~Your Friend Chris http://myvbastuff.blogspot.com/ "vbasean" wrote: 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. -- ~Your Friend Chris http://myvbastuff.blogspot.com/ "mhmaid" wrote: 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. |
#4
|
|||
|
|||
split or merge ..please help
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" wrote: 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. -- ~Your Friend Chris http://myvbastuff.blogspot.com/ "vbasean" wrote: 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. -- ~Your Friend Chris http://myvbastuff.blogspot.com/ "mhmaid" wrote: 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. |
#5
|
|||
|
|||
split or merge ..please help
Templates are a great place to start but I'm totally not understanding how
Employee Expenses relate to Patient Invoices. Can you please explain. |
#6
|
|||
|
|||
split or merge ..please help
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. -- ~Your Friend Chris http://myvbastuff.blogspot.com/ thinking out loud "vbasean" wrote: Templates are a great place to start but I'm totally not understanding how Employee Expenses relate to Patient Invoices. Can you please explain. |
#7
|
|||
|
|||
split or merge ..please help
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. "vbasean" wrote: 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. -- ~Your Friend Chris http://myvbastuff.blogspot.com/ thinking out loud "vbasean" wrote: Templates are a great place to start but I'm totally not understanding how Employee Expenses relate to Patient Invoices. Can you please explain. |
Thread Tools | |
Display Modes | |
|
|