Solved

How to make a relationship db for billing

Posted on 2014-10-24
6
34 Views
Last Modified: 2015-11-10
Currently have a Client_tbl and Horses_tbl, (these two are somewhat relational for reference and history of horse visits).

A third tlb I use is called a Billing_tbl  which combines clients and horses (as some clients have more than one horse).  Clients have a unique ID which is numerical i.e.123 and Horses_tbl has a Unique Horse ID which is a txt field. i.e. H123-1.  

The Billing_tbl has the Horse ID and is the Primary.  The Billing_tbl is used on my Billing form which also has a Price_list_tbl.  These are actually queries at this point.  

The form uses the Billing_qry to select the Horse and appropriate client and after that I select an Item on the Price_List_qry and duplicate record.  This then calulates the item, QTY and Price and duplicates record.  Once all items are individually selected, I have a subform with a datasheet of the Billing_qry which shows me the total for the invoice.  I then use a command button to append to the Billing_History_tbl and another cmd button to manually update a Subtotal field in the Billing_History_tbl.

I would like to build a relationship db using the Clients_tbl and Horses_tbl and Price_List_tbl to do all of these functions and perhaps have it automatically calculate and insert the Subtotal when it appends the the Billing_History_tbl.

Any Suggestions?

Thanks,

Lynks
0
Comment
Question by:lynksko
  • 3
  • 3
6 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40403020
A sample database will go a long way in helping us understand the functionality of your existing system.
There is a lot we do not know about the entirety of what this system is ultimately trying to keep track of...:
-Can one client own many horses and bring one or more horse in for one "visit")
-Can one horse be owned by many "clients"?
...etc
So you will have to explain to us, in more detail, what you are tying to accomplish, ...both in terms of your existing design and in terms of what you need from our "suggestions"
...hence the request for a sample database

I see something like this as a very general start.:

tblClients
cID (PK)
cName
cAddress
cPhone
cEmail

tblHorses
hID (PK)
h_cID (FK)
hName
hDOB

tblService
sID (PK)
sName
sPrice

tblBilling
bID (PK)
bDate

tblBillingServices
bsiD
bs_hID (FK)
bs_sID (FK)
bsQuant

From this you can create a query to pull in all the needed fields, then calculate the cost of each "service" in each "visit"
A form/report could calculate any "Totals"

This would represent a design similar to the design of the Access Northwind sample database, where the counterparts would be: Customers (Horses), Products (Services), Orders (Billing), OrderDetails (BillingServices)

I may have missed a few things, ...but his seems like where you might wish to start.

Just be aware that these "Any Suggestions?" type questions can quickly escalate into a full scale "design discussion".
So you need to carefully state what you are looking for here as an "Answer"
...lets see what other Exerts may suggest...
;-)

JeffCoachman
0
 

Author Comment

by:lynksko
ID: 40403088
Thanks Jeff,

The answer to your two questions about Horses and Clients is Yes, those scenarios do happen.

 I will give the Northwind example a try with the counterparts you sugessted.  My current db is not relational so I use the additional Billing_tbl as sort of a holding place prior to appending to Billing_History_tbl.  This seems clunky and I would like to get rid of the Billing_tbl all togehter and relate Clients, Horses and Price_List tables so I can directly append or relate to the Billing_History_tbl.

I do understand how to make a form/report calculate "Totals"  but like to achive the Sub Totals, Payments, etc. in a field for future reference.  So, with your breakdown of my tbl's, I would like to get rid of Billing_tbl but keep Billing Services which I call history.

Lynks
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40403470
Again, without any detailed knowledge of your database or it's purpose, ...
...it may not be advisable to simply remove a table just because you would like to...

in fact,  it is not even clear what tables you really need.

In addition, (now that you have answered yes to both of my questions), ...your design will need to become even more complex...
...and those were just two of perhaps dozens of other questions that any expert would raise.

To continue further, ...you really must post a sample of your database and explain its purpose clearly.

If not, then I would stick to the basic design I offered, ...and also refer to the "Old" Northwind 2000 sample Access database.
(I think you can still find it here: http://download.cnet.com/Access-2000-Tutorial-Northwind-Traders-Sample-Database/3000-2251_4-10742880.html)

(...and archiving the totals is an advanced technique, ...not even worth considering at this point.)

JeffCoachman
1
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

Accepted Solution

by:
lynksko earned 0 total points
ID: 40404366
Hi Jeff,

Thanks for the link.  At this point my db is too big and complex to send, but I will attempt to send you a scaled down or sample version by early next week if needed.  The tbl's have too many redundant fields making it very flawed, however, it has served its purpose for 3 years.   Now is the time to make it relational and I will be starting from scratch.   I'll just import all the data when this is achieved and can also send you a sample of my attempt.

This is where I need help.

Note: Originally, I wanted to make this db relational, but my knowledge of how to do that and the intial urgency to get bills out, forced me to build it quickly and by the only way I knew how.

As for the purpose of my db:

1.   WHY:
I work for a large animal veterinarian in the SF bay area, (mostly horses).  When we started the practice, we discovered purchasing a db was about $20,000.  I had some experience with MS Access and offered to build a db.

2.  MAIN FORM: Basically. I have a Main form, (used to be called switchboard), and it's pretty straighforward with cmd buttons opening forms.  i.e  client_frm, Horses_frm, Billing forms, a price list lookup and various others. The users are myself and the vet and currently we use one office computer and a laptop.  

3. CLIENTS: On the Client_frm, a cbo box selects the client and info appears, (name address, phone etc.)  This form links to the Horses_frm  and is shown by datasheet view.  Once a client is selected, the Horse/Horses appears on subform with age, breed etc.  The HorseID is the PK and the MS wizard links CliendID.    I have also linked billing information to the client_frm.  i.e. balance due, last bill sent date and such.  This comes from a VERY large tbl which I call: All_Billing_tbl and is also linked by clientID and filtered by invoice number in various ways from qry's.

4. HORSES: On the client form, one can click on the HorseID on the datasheet subform and open the Horses_frm to see all info on the horse and there's another datasheet subform with visits dates and description "ITEM" and this is linked to the All_Billing_tbl and also a redundant Medical_History_tbl with more detailed info.  It works but, I don't trust it. re: Orphans, possible missing data etc.

5.  HOW NEW DATA GETS ENTERED: Adding client and horse data is done with a form and subform for Horses_tbl.  These are somewhat related through a qry but limited.   It then appends to the Billing4qry_tbl.  This what I refer to as a "holding"  tbl and is pretty much identical to the Horses_tbl with more billing info and has no PK because each Item or "service" from the price list duplicates when making an invoice by a cmd button (Macro).  It has client name, address and other billing info along with one to many horses to intitally select from.

6.  BILLING: The billing is the most complicated and clunky because nothing is relational and works with cbo boxes, mostly Wizard design, and some limited code I've written in the the cbo After Update for a price_list_tbl.    The Billing form control source is the "holding" tbl called "Billing4qry_tbl".

The price list code on the cbo box after update looks like this:

'These are in from the Price_List_tbl in appropriate column order'
Private Sub ITEM_AfterUpdate()

Me![Quantity] = Me.ITEM.Column(1)
Me![Price] = Me.ITEM.Column(2)
Me![NotesOnPrice] = Me.ITEM.Column(3)
Me![Numbering] = Me.ITEM.Column(4)
Me![Discounts] = Me.ITEM.Column(5)
Me![DiscountPrice] = Me.ITEM.Column(6)
Me![DiscountNumber] = Me.ITEM.Column(7)
Me![ID_PDB] = Me.ITEM.Column(8)

End Sub

This inserts data from a Price_List_tbl duplicates records to the holding tbl "Billing4qry_tbl" then appends to the  All_Billing_tbl via a save cmd button (macro).  As soon as I close the form, It deletes the invoice related records from Billing4qry_tbl and also appends to the Medical_History_tbl.

7.  HOW A BILL IS MADE: After selecting the horse and apprpriate owner, from the Billing4qry_frm, the date and invoice no is entered manually. This goes on one by one and there's a datasheet view that shows me the running total from the same control source.  Upon the last entry, I manually update the All_Billing_tbl with a subtotal.  The invoice report calculates many things and so does an All billing_qry.


As you can guess, I had no concept how to use a PK and esp. a FK. and found I could not use the Horses_tbl because you cannot duplicate the PK.

There are identical fields in each tbl that append to the All_Billing_tbl and Medical_History_tbl.  The Price_list_tbl I select from, also has identical fields.   Silly, but it works.

If you still think it would be helpful for me to send you my current non-relational db, let me know.   I'd really like to study the tutorials you suggested and attempt to take my data and make a relational db and get that working instead of tinkering with what I have.

Lyn
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40404390
If you still think it would be helpful for me to send you my current non-relational db, let me know.
Yes, ...but remember that the structure of this site is not really set up for detailed "Design Advice", ...because there is simply too much involved for a member here to know about this system to make any meaningful suggestions....
Thus, any design advice:
1. May not even be valid
2. May be to complicated to implement
3. May not take into account any unknown factors
...etc

..So to me, it still sounds like the similarities between what you need and what is done in the Northwind sample are relevant.

But the best course of action would be to partner with a database professional to work with you.

JeffCoachman
0
 

Author Closing Comment

by:lynksko
ID: 41219478
So Sorry for the delay.  Got too busy at work but will refer to your advise.  Thank you!
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now