I have created a database that will tracker invoice and contract information for my company - I've linked so that I can (hopefully) run queries showing what invoices have been paid by what contracts. As I'm setting this up - I have essentially two tables housing the information - Contracts and Invoices. The contracts table is basically a pool of $$ that is used to pay the invoices. I'm wondering if there is a way (other than linking them in Relationships which I have already done) to accomplish what I'm looking to do. Also, each invoice that is paid will reduce the total amount of the contract - is there a way to calculate this in Access? Any guidance is thoroughly appreciated, I need someone to bounce ideas off of as no one else in my office is Access-literate!

Thanks for the reply Scott - this is an internal database, so there are no 'customers' per say. This will just be a way to track what contract(s) are used to pay a particular invoice. Each invoice number is unique though.

I know there is a way to set this up to perform these actions, just need direction! Tables and queries are already set up for certain things, I'm hoping that adding in this other function will be possible. Example 1: Invoice 123 comes in for 50,000 It will be paid with 25,000 from contract 1 and 25,000 from contract Example 2: Invoice 1234 comes in for 50,000 It is paid with contract 2 (which totals 100,000 - so a remaining balance will be left on that contract for 50,000) Hope this is more descriptive, thank you again for the help! Elektor December 2013. It sounds like you have a many-to-many relationship type between Invoices and Contracts, i.e.

I have created a database that will tracker invoice and contract information for my company - I've linked so that I can (hopefully) run queries showing what invoices.

Each invoice could be paid from one or more contracts, and each contract could pay for one or more invoices. For this you need a third table, InvoicePayments say, with two foreign key columns, InvoiceID and ContractID referencing the primary keys of the two referenced table respectively, and an Amount column to record the amount paid in each case, There would now be no ContractID foreign key column in invoices as the relationship type is modelled by the InvoicePayments table. This type of table is sometimes known colloquially as a 'junction' table. Daz3d - Infinito Expansion Pack 1 here. Mamiya 75mm Shift Lens Manual Or Dc. By joining the three tables in a query you can return the payments for each invoice/contact. You can also return the balances per contact at any time by summing the payments per contract and subtracting this from the contact provision. This type of information would normally be presented in reports based on the queries. You'll find an example of a basic many-to-many relationship type and how it can be represented in a form/subform as ParentActivities.zip in my public databases folder at: While the subforms in this little demo file include only the one control, a combo box, in your case there would also be an amount text box in a contracts subform within an invoices form, or conversely an invoices subform within a contacts form.