Contract Tracking

[Jen]

I am trying to build a database where I can track multiple contracts. I have
my tables made (Contract type, Contract Services, Customers, Sales Reps, etc)
but I have become tripped up on one issue. We have 2 contracts where the
services are done 3-4 times a year. I want to be able to track when each
service was performed and the invoice amount. The only way I could think of
doing it is making the Contracts table have about 20 columns with a pattern
of "ContractType - DatePerformed-InvoiceAmount -SalesRep".

My first couple of columns are "ContractID -
CustomerID-ContractType-DateSigned-DateExpired"

Is there a better way?

 

[a a r o n . k e m p f]

ACT is a great answer for this.. www.act.com

We got the corporate premium edition, there is a web interface, and I
get a SqlServer login (ActReader) to write queries directly against
the DB

 

[Mark Andrews]

I usually design this as:

tblContract (think of this as the header for the contract)
ContractID
CustomerID
ContractDate
etc...

tblContractLine (this of this as each line on the contract)
- ContractLineID
- ContractID
- ProductID (this is a dropdown to select product/service)
- description
- qty
- price
- possibly flag for taxable

tblProduct
- this table holds the products/services and is used to drive the dropdown
on the contract lines, also populates default prices, descriptions etc...

In your case you might also have salesrepID on each contractLine (if
portions of the contract are done by different people).

Feel free to steal back-end tables from my CRM template
http://www.rptsoftware.com/products/crmtemplate/

It does Invoices but Contracts are usually almost the same design

HTH,

 

[roccogrand]

Jen,

I suggest thinking your database through a little further. First, each
customer will have multiple contracts, multiple sales reps (at different
times perhaps), and will purchase different services.

Second, a sales rep will manage multiple contracts, have multiple customers,
and provide multiple services.

Third, a service will be sold by multiple sales reps, be consumed by
multiple customers, and will be associated with multiple contract agreements.


Fourth, each contract (agreement) will be sold by multiple sales reps, each
contract will have multiple services, and each contract can be owned by
multiple customers (not always, but it possible).

So you need to model your database as several many-to-many relationships.
The easiest way that I have been able to do this in Access is using
Form-Subform pairs. I don't understand all that Access does in this regard
but it works for me. Each of your tables (tblContracts, tblServices,
tblCustomers, and tblSalesReps) will be a Form in a simple scenario. And the
other tables will be the subforms. I am not sure how it should be created
for your database but take a look at Viescas' Building Microsoft Access
Applications for tips on how to model and implement a workable solution.

HTH

David

 

[roccogrand]

Jen,

I thought of a few other things to mention overnight.

If your Contract Services are things like (1) Provide maintenance services,
(2) Provide commercial insurance, and (3) Provide auto insurance, they can be
implemented using Access' multivalue fields. I use multivalue fields in all
of my applications because I am not an expert data modeler. If you are using
Access 2007, then multivalue fields are 'the best thing since sliced bread.'

Another resource is Microsoft Access Small Business Solutions by Teresa
Hennig et al. This book is all about data modeling with Access. You won't
find complete applications in it but I am sure that you'll learn how to model
your data, which is the first thing that you need to do when starting to
learn database management (Access).

David