How to make a relationship db for billing
Posted on 2014-10-24
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.