Contract Database and Invoice Troubles

[phiednate]

First off, thanks for all the great help so for! Wouldn't be near as far as I am with the database I am trying to make for work without help from everyone here. I have run into another problem and have a few questions I was hoping yall could help with.

Here the situation:

I have created most of my database for tracking contracts. One major thing I want to track is how much money is left on the contract after invoices are processed. You can kind of think about it as a checking account with a set amount of money and invoices are put in depending on the portion of the work done until there is no more money left and the job is done.

I cant think of how I would implement this though. Down the road when I learn more VB scripting I would like to be able take the data out of the table and have the DB create an excel sheet so I would like to set it up with that in mind. Right now all I want is a form where the under can input an invoice and when they open the form for their contract, it will automatically deduct that amount from the original contract amount resulting in the remaining amount left on the contract.

Based on all the database design rules, what would be the best way to set this up? Thanks again for the help folks!

 

[The_Doc_Man]

In overview, I would treat this problem as any other inventory (except it is an inventory with only one part - cash.) Make the inventory starting value equal to the value on the contract. Make each invoice like a transaction. Then build two more queries.

#1: Use a UNION query to bring together the values in qryCtrXAct

SELECT CtrID, DollarValue as XActAmt, etc. FROM tblContracts UNION
SELECT CtrID, -DollarValue as XActAmt, etc. FROM tblInvoices ;

#2: SELECT Sum(XActAmt) FROM qryCtrXAct GROUP BY CtrID

Or you can at this point do a DSum( "[XActAmt]","qryCtrXAct","[CtrID] = " & CStr( your-selected-contract-ID-goes-here) & """" ) inside a query that groups by contract IDs.

The point being that the UNION query lets you define the starting point and the invoices to have different mathematical signs even though on reports, both appear positive.

 

[gemma-the-husky]

normally contracts are dealt with by staged payment claims

each claim includes a cumulative claim, less previous claim, less retention, plus vat (sales taxes), and this effectively constitutes the invoice

you may need to include additional stuff like measured work since

eg, a contract for £1million, may be half done (measured work £500K), but progress claims so far raised for £450K in total, less retention, plus VAt.

all you need to compare is total contract price, with the latest cumulative claim, to establish the outstanding amount.

payments are different - they comprise the allocation of cash/cheques received against the contract claim/invoices - if the invoices are posted to a normal sales ledger/contract ledger, the unpaid claims will be easy to assess

---------
if this is a biggish company system, you need to have this structured correctly

conversely if you dont get it normalised properly, it will give you much pain, i would have thought
--------