Many-to-Many.... with Many problems

[Josua]

I have been working on something that I feel should be very simple. It is a database for invoice and vouchers that pay for the invoices. On the basic level, it is a many to many relationship since one voucher can pay many invoices, and one invoice could be divided among many vouchers. So, I have 3 tables:

Invoice:
Inv_id
Inv#
Amount

Voucher:
Vouch_id
Vouch#

Mid:
Vouch_id
Inv_id
Amount (paid per invoice per voucher)

I use Auto-Increment in order to get the "id"s and link between the primary tables and the Intermediate using those ids, accepting them in Mid as numbers.

My issue is that I can't seem to get the Mid table to accept info when it is in this setup. Even if I have a form that just gets info for the Inv table, it creates an autonumber but is never received by the mid table.

Now, if I try to put info into the Voucher table as well, (to try to get a value to show up in mid) it gives me the error that says: You tried to assign the Null value to a variable that is not a Variant data type.

I feel like I am missing something simple, and this is a core application for Access.
Thanks in advance for any help!

 

[fneily]

First, you shouldn't be using autonumbers for your primary keys. You may search these Access forums to see why.
Here are some sites that have an invoice system already set up:
Fundamental Microsoft Jet SQL for Access 2000

http://msdn2.microsoft.com/en-us/library/Aa140011(office.10).aspx

You would download AcFundSQL.exe mentioned in the article and revise it to your specs.

Sample Access Invoices Database

http://www.bullschmidt.com/access/default.asp

Also, you have amount in two tables. Against the protocols of Normalization. It should not be in the Invoice table.
Take a look at the above databases and articles. Also, look at the coding behind the forms.

 

[Josua]

I see what you are saying, but I have a slightly different setup than these two samples.

Where as in the samples, the company is attaching invoices to a customer. But in my situation, it is more easily related to a company getting invoiced by others for doing a service for the company. Then, the company pays with a voucher. These vouchers can cover multiple invoices, or just bits and parts of different invoices.

This also explains why I have two amounts. They are labeled slightly different in my database, but the big difference being that the invoices are for a certain amount (thats the "amount" in the Invoice table), and then each voucher pays for a certain amount of each invoice (thats the "amount" in the mid table).

I will continue to research a better way of creating a primary key...
Thanks for the help so far!

 

[fneily]

First, don't use words as such MID for a table name or field. It's a built in function. Try to stay away from function names and keywords. Call it Transactions.
As I understand it, the amounts for a voucher would then add up to the amount for an invoice. Again, you would calculate that on a form or report. You DO NOT store the sum, such as amount in Invoice.
Also, could you provide the tables structures (tablenames and fieldnames). 'Cause you threw in "company" in your last post. It would just help to see everything you have.
Right now as you show it, you only need one table:
Inv#, Voucher# , Amount
Primary key would be multi fielded - Inv#/Voucher#
So you'd only have one form and Inv# and Voucher# could be comboboxes that could allow data entry for new items and would then be requeried to update them.

 

[Josua]

The most complicated part of this (at least to me) is that a voucher may pay for multiple invoices, but it may also only pay for parts of multiple invoices. For example:

Voucher A covers 25% of Invoice 1
and 50% of Invoice 2

Voucher B covers 75% of Invoice 1
and 50% of Invoice 2

But it rarely works out cleanly like that. Usually it is about 3 vouchers per invoice, and 3 invoices per voucher.
Does this make sense? this is why I don't think one table would work, because I need a way for it to be known that not only is A paying 1 and 2, but that 1 is being paid by A and B.

Does this make it any clearer?

 

[fneily]

Right Multi-primary key tables are not immediately clear so you use the old pencil and paper routine.
Let's say Invoices are denoted by alphas and vouchers by numerics.
So you have a table that looks like this:
Inv Voc Amount
A 123 amt
A 234 amt
A 543 amt
B 123 amt
B 899 amt
C 676 amt
C 123 amt
C 444 amt

So I need the total amount for Invoice A. I sum all A's. I need the total for Voucher 123, I sum All 123. So 123 of A, 123 of B and 123 of C. Drops right out. Access, and Relational Databases, are amazing for that. This meets your requirements.