Estimateing .mdb database plan.

Posted on 2009-04-24
Last Modified: 2012-05-06
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 ?
Question by:Grant Fullen
    LVL 36

    Accepted Solution

    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
    LVL 22

    Assisted Solution

    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 ;-).

    Author Comment

    by:Grant Fullen
    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.

    Author Closing Comment

    by:Grant Fullen
    Both Apply

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
    Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    26 Experts available now in Live!

    Get 1:1 Help Now