• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 261
  • Last Modified:

Estimateing .mdb database plan.

I am writeing a estimateing application.
I have a .mdb database.
Table:= Customer Info
Table:= Estimates.
I need a game plan to make an estimate useing the tables.
So here is what i have now.
Enter in all the customers Name,Add,Phone,CarInfo in the Customer Info.

Now I do all my math , Sums, Tax, Total in the Estimates table.

Sine there may be 10 to 20 lines to calcuate in the estimates table i can not add all this info in the customers table.

Question: How can i get my customers info tied to the Estimates table properly so i can add/edit/ save info ?
Grant Fullen
Grant Fullen
  • 2
2 Solutions
Geert GruwezOracle dbaCommented:
the Estimates table is information for each customer
so i assume you need the primary key from the customer table in the estimates table too
if you have allways recurring same lines per customer you add an extra key to designate what information it is
if you have multiple lines you can also add an index like in a stringlist
the primary key for the estimates would then be:
customerid + estimateinfoid + estimateinfoindex

records could then contain information like this:
customerid;firstname;lastname;address; ...
1;John;Doe;"Whitehouse 1";...
2;Jane;Doe;"Whitehouse 2";...

estimateinfo table:
1;taxtotalyear;"calculated total of tax for a year";
2;taxtotalmonth;"calculated total of tax for a month";

1;1;2008;"Total tax 2008";23000;
1;1;2009;"Total tax 2009";3000;
1;2;200801;"Total tax january 2008";2000;
1;2;200802;"Total tax february 2008";1800;


you can then update a record with a statement like this:
update estimates
set value = 1900
where customerid = 1
  and estimateinfoid = 2
  and estimateinfoindex = 200802;

or add info
insert into estimates (customerid,estimateinfoid,estimateinfoindex,descr,values)
values (1,2,200803,'Total for march 2008',1700);

in delphi code you would off course be using parameters
I am going to assume that you have a CUstomers table that has Customer (only) information like Name, address, phone, fax, and other data specific only to the customer.
I would suggest tat you consider making a table for the kinds of things (Products, Services, Items, whatever) that you will include in your estimates.  This table. and I'll use Items because it is generaic, should have the following columns:
  • ItemID (an Auto Increment Identity column),
  • ItemCOde (for ease of entry),
  • ItemName (the human-consumable short name for the estimate/invoice),
  • Description,
  • UnitCost, I generally make this a Currency field,
  • UnitPrice, I generally make this a Currency field, too.  
  • Avaialbe (Y/N, default = Y) to indicate whether you have/use the items anymore.
Now, you'll need an Estimates Table.  That one should have the following columns
  • EstimateID,
  • CustomerID (for the link to the Customer Table),
  • EstimateDate (for the date the estimate was made and don't just call it "Date, please ;-),
  • EstimateValidPeriod (an integer value for the number of days the estimate is good),
  • Taxable (Y/N flag defaulting to Y) to indicate whether to calculate taxes (in case you estimate for a 501c3 ;-),
  • EstimateSubtotal,
  • Discount (a Percentage field), in case you want to figure the total and then calculate the discount to show on the Estimate ;-),
  • TaxRate (defaulting to your local tax rate),
  • EstimatePreTaxTotal,
  • EstimateTax columns.  
    Note: You won't need the EstimateGrandTotal total column because that can be easily calcuated.
Now, you need an EstimateDetails table.  Ths one needs the following columns:
  • EstimateDetailID (again, an auto increment)
  • EstimateID (so you can link it back to the correct estimate)
  • ItemID (so you can link it to the correct Item being estimated
  • Qty (I generally carry this as a Doulbe unless I know I am only going to have integer amounts)
  • UnitPrice, I generally make this a Currency field (this may or maynot be the same as the Unit price of the Item, although that would be the default, becuase you may give a qty discount or mark it up more ;-)
  • Taxable (you may or may not need this because you may not be dealing with a mixture of things that are or are not taxable)
I would go one step further and have a matching set of tables for the Estimates that turn into Jobs.  When you have a customer accept an estimate, you can just execute a procedure that copies the Estimates and EstimateDetails into the Jobs and JobDetails table and, poof, you have your invoicing information. ;-)
 Now you table linkages are:
Customer => Estimates => EstimateDetails => Items
This lets you modify Customer Information without having to do so for the each of the 3 estimates you gave the Customer.  You can modify the Items entry to correct a misspelling or change the Unit Price/Cost information.  You have the Estimates information so that you can change the Discount or anything else specific to the whole Estimate.  You have the ability to add or delete specific items or change their quantity and then recalculate the Estimate very easily using a query.
 If you add the Jobs and JobDetails table, you have the following table linkages:

Customer => Jobs=> Job=> Items
Notice how the Items and Customers didn't change . . . and, if you do a good job and a customer comes back, you don't have to re-enter their information (unless they moved ;-).
Grant FullenAuthor Commented:
Diver I understand Customer => Estimates => EstimateDetails => Items
Mine will be CustomerInfo=>Estimates=>CompletedEstimates.
I do not know how to do this realtions in delphi....... But I get the basic ideal.
Grant FullenAuthor Commented:
Both Apply

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now