If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Contract Tracking
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? |
#2
|
|||
|
|||
Contract Tracking
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 On Apr 22, 7:11*am, Jen wrote: 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? * |
#3
|
|||
|
|||
Contract Tracking
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, -- Mark Andrews RPT Software http://www.rptsoftware.com http://www.donationmanagementsoftware.com "Jen" wrote in message ... 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? |
#4
|
|||
|
|||
Contract Tracking
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 "Jen" wrote: 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? |
#5
|
|||
|
|||
Contract Tracking
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 "Jen" wrote: 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? |
Thread Tools | |
Display Modes | |
|
|