How to make a relationship db for billing

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?


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
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"?
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.:

cID (PK)

hID (PK)
h_cID (FK)

sID (PK)

bID (PK)

bs_hID (FK)
bs_sID (FK)

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...

lynkskoAuthor Commented:
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.

Jeffrey CoachmanMIS LiasonCommented:
Again, without any detailed knowledge of your database or it's purpose, ... 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, 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:

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

The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

lynkskoAuthor Commented:
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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
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

..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.

lynkskoAuthor Commented:
So Sorry for the delay.  Got too busy at work but will refer to your advise.  Thank you!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.