Table relationship: Bill & Fees tables

[Sonny Jim]

What kind of relationship do I need between a table created for "bills" and one for "fees", where there can be more than one fee per bill. In the "fee" table there is a "Fee Name" and a "Fee Charge"? On the "bill" form I use a combo box and I would like to be able to add to the "fee" table using this combo box.

 

[georgedwilkinson]

You said it in your description 1:M. Each Bill can have zero to many fees associated with it, each fee can be charged on one and only one bill. These are not standard business terms, however. You may want to change it from Bill to "Invoices" and from Fee to "InvoiceLineItems" to make your system easier to understand.

Typically, you don't add to detail records via a control on the main form. The standard way is to use a sub-form, which also requires zero coding. If you insist on going forward with the combo-box idea, you will have to write VBA code to insert data into your detail table. Then how will you know if you've already inserted a given fee?

 

[Sonny Jim]

I don't think I completely follow you, George. From this I can only envision using several subforms, one for each line item that is included on the invoice, or using a single subform that itself will call on a table several times.

I'm sure my scenario isn't all that uncommon but, just in case I wasn't clear, this is what a potential invoice would look like:
Invoice #1:
Line item #1
Line item #2
Line item #3
Line item #4
Line item #5
...
Line item #10
...where each line item includes a label that can be found in a drop down or created new, and an associated value in terms of currency (this too would have the potential to be inputted if the label hasn't been created yet).

Could you possibly offer more detail on how this would be done, or point me to a reference that explains/shows this in more detail?

Best regards,
Sonny Jim

 

[georgedwilkinson]

Well, since it is a one to many relationship, you would have a single (one) main form with a single sub-form (to handle the many side of the relationship). The sub-form will be embedded in the main form.

On the main form, you'll have the invoice header stuff:
InvoiceNumber, CustomerNumber, InvoiceDate, etc.

On the sub-form, you'll have one or more rows (I guess kinda like a spreadsheet) with the detail information:
Item, Quantity, UnitPrice, LineTotal (calculated), etc.

So the (single) subfrom would look something like this:
1 | Consulting Fees | 12.50 | 12.50
1 | Housing Fees | 19.95 | 19.95
7 | Hosting Fees | 10.00 | 70.00

In the footer of the subform, you'll have a calculated total field.

Take a look at the Northwind database sample to see and example of how this problem has already been solved.

 

[Sonny Jim]

Okay, I'll check it out. Thanks a lot George!

 

[Sonny Jim]

I think I've got the concept of how the form works, George, but what are the tables and their keys going to look like? I believe this is what I am struggling with.

 

[georgedwilkinson]

Table Invoices:
ID PrimaryKey Autonumber
Other stuff

Table InventoryItems:
ID PrimaryKey Autonumber
Other stuff

Table InvoiceLineItems:
ID PrimaryKey Autonumber
InvoiceID ForiegnKey Long Integer
ItemID ForiegnKey Long Integer
Other stuff

Obviously, the names should conform to whatever standard/regional norms you are using. Do yourself a favor and never use special characters or spaces to name any of your objects, though.

Does that help?

 

[Sonny Jim]

So I now have:

tblInvoice
Ship Date
BOL#
Pro # (Primary Key)

One-to-Many,

tblInvoiceDetail
InvoiceDetailID (Primary Key)
Pro #
AccessorialID

One-to-One (?!!)

tblAccessorials
AccessorialID(Prim. K)
AccName
AccFee




Assuming this is how I should do this, how do I make the One-to-One relationship above a One-to-Many relationship?

 

[Sonny Jim]

tblInvoice
Pro# (Primary Key)
Ship Date
BOL#

one-to-many

tblInvoiceDetail
DetailID (Primary Key)
Pro#
LineItemID

one-to-one (?!!)

tblLineItem
LineItemID (Primary Key)
LineItemName
LineItemChargeAssuming I have this correct, any ideas on why I can't create a one-to-many relationship with the second one?

 

[Sonny Jim]

I'm sorry, I see what I did, George. I had to allow duplicates of the lineItemID foreign key in the tblInvoiceDetail table.

 

[georgedwilkinson]

Don't use special characters (#) as you will one day rue it.

The relationship from InventoryItems to InvoiceDetail (aka InvoiceLineItems) is 1:M. In your case, there are three basic things you're keeping track of:
A transaction (Invoices)
The details of that transaction (InvoiceLineItems or InvoiceDetails)
The product that you have in stock or services in your repertoire (InventoryItems)

Of course there are plenty of other tables involved (Customers, potentially Addresses, TaxRates, other supporting tables, etc.), but they are not directly related to the original question. You can get a really good idea what is needed by looking at the invoice model in the Northwind sample database.

In a more advanced system you may have Chart Of Accounts, Accounts Receivables, Payments, which may all have supporting tables and junction tables, etc. You may need to relieve inventory, and so forth.