Estimate to Actual

[Ronjing]

I am working on a database to track maintenance actions. I am trying to determine would it be better to break my estimate and actuals into 2 tables or should I have them in the same table they must be referenced back to the same Invoice_ID. Any thoughts? Thanks in advance.

 

[Isskint]

If you will only ever have 1 estmate and 1 Actual, then keep them in the same table. If you are likely to update the estimates or actual (and would need to track them) then seprate tables would be the route to take.

 

[Ronjing]

I am looking into setting this up so that after the Estimate gets approved I can autotransfer all or part of the estimates info into the Actual fields. estimates nor actuals should change. Not every Estimate will have an actual and not every actual will have an estimate.

 

[Isskint]

As you can not be sure how many estimates or actuals any InvoiceID can have, you can not have the fields in the 'main' table. You will need to store these seperatley. You could do it in 2 tables
tblEstimate;

• EstimateID - AutoNumber (PK)
• InvoiceID - datatype as your InvoiceID (FK)
• EstimateValue - Currency

tblActual;

• ActualID - AutoNumber (PK)
• InvoiceID - datatype as your InvoiceID (FK)
• ActualValue - Currency
• EstimateID - Number (FK) you will only need this IF you want to track an actual against an estimate

Or in 1 table
tblEstAct

• EstActID - AutoNumber (PK)
• InvoiceID - datatype as your InvoiceID (FK)
• EstimateValue - Currency
• ActualValue - Currency

Now the decision, I think, comes down to 'wasted' memory. For arguments sake lets say each InvoiceID has 3 estimates and 1 actual. The 2 table method generates 4 records, but no blank fields. The 1 table method has only 3 records BUT 2 empty fields. I am sure another member of this forum can answer that.

As to updating an estimate to an actual;

1 table method;

• On the form where this decsion will be recorded, use a command button to accept estimate
• Have code in the OnClick event
  • Me.Actual = Me.Estimate

2 Table method;

• On the form where this decsion will be recorded, use a command button to accept estimate
• Have code in the OnClick event
  • Run an UPDATE query (either SQL in VBA OR a premade query)

From this angle, the 1 table method is a LOT easier, lol.