Updating Cost without changing previous records

[johnherrerajuan]

I have this estimate database for a construction company. In this database I calculate how much will a project cost. It’s pretty much complete the only problem that I have is trying to figure out how to update the cost of a trade without affecting older records

Example let’s say we have a painter that makes $15/hr in project A,B,C,D,E we decide to give him a raise so project F would have a new amount for painter. The problem with that is that it will affect record A-E

I don’t want that my department wants to go back and view a history of records.
Also take a look at my database it’s my first time creating one I need some feed back

 

[JHB]

...
Example let’s say we have a painter that makes $15/hr in project A,B,C,D,E we decide to give him a raise so project F would have a new amount for painter. The problem with that is that it will affect record A-E

Then you need to store the value within the project, you could setup the default value for the field to $15/hr, if you want to raise/decries it, the put in the actually value.

...
Also take a look at my database it’s my first time creating one I need some feed back

Use subforms instead of "predefined lists", then what happen if the "Trade" excite 10 or "Materials" 20!
Using subforms then you can have as many/few as you need.
Use tabcontrols for organisation you data.
Looking at it, then the Excel way had too much influence on the design.

 

[CJ_London]

There are two things you can do, you decide which is the best one for you.

The first is to store the rates in the estimate having looked them up from the labor table or whatever. This 'breaks the rule' of only storing data once but this is a legitimate reason for doing so. This also has the potential benefit of being able to change the rate for some reason on the estimate without affecting other estimates. Many invoicing systems will do this as a matter of course because many things can change over time - rates, taxes, even addresses and customer names and you may need to reproduce the identical document some time after it has been created.

The second is to create a history table for prices which would consist of

ID to your labour table
Price
DateFrom
DateTo

I note you do not use IDs in your data so you would need to change that in yout db

DateFrom is the date a price becomes effective
DateTo remains empty and is only populated when the pricing ceases - probably never, in which case you can leave this field out

The benefit of this method is you can price forward - so if you are quoting for a job in the future and the history table has been completed with future prices you simply replace Date() in the above with the later date.

 

[jdraw]

Please see this link for comments on changing Price and affecting historic records.

 

[johnherrerajuan]

Then you need to store the value within the project, you could setup the default value for the field to $15/hr, if you want to raise/decries it, the put in the actually value.

Use subforms instead of "predefined lists", then what happen if the "Trade" excite 10 or "Materials" 20!
Using subforms then you can have as many/few as you need.
Use tabcontrols for organisation you data.
Looking at it, then the Excel way had too much influence on the design.

where with in the project would i need to store the data, in an other table?

I tried to add a sub form with the fields [Material SR] [Material] [Description] [Stock/NonStock] [QTY][UnitPrice] but when I jump to the next record on my main form the subform stays the same!
This is why I went with the predefined lists method.
I know its not the best way to do it but it was better than nothing. I am not sure if I'm setting the relationships right with the two tables.

 

[johnherrerajuan]

There are two things you can do, you decide which is the best one for you.

The first is to store the rates in the estimate having looked them up from the labor table or whatever. This 'breaks the rule' of only storing data once but this is a legitimate reason for doing so. This also has the potential benefit of being able to change the rate for some reason on the estimate without affecting other estimates. Many invoicing systems will do this as a matter of course because many things can change over time - rates, taxes, even addresses and customer names and you may need to reproduce the identical document some time after it has been created.

The second is to create a history table for prices which would consist of

ID to your labour table
Price
DateFrom
DateTo

I note you do not use IDs in your data so you would need to change that in yout db

DateFrom is the date a price becomes effective
DateTo remains empty and is only populated when the pricing ceases - probably never, in which case you can leave this field out

The benefit of this method is you can price forward - so if you are quoting for a job in the future and the history table has been completed with future prices you simply replace Date() in the above with the later date.

For the first method, where would I store this information?
For the second method I started doing something similar (RangeOfWageDates Table), but my question is would I need some coding that determines what price is going to be selected based on the date?

 

[CJ_London]

For the first method, where would I store this information?

you tell me - all you've said is

I have this estimate database for a construction company. In this database I calculate how much will a project cost

I'm guessing you have a table which stores your estimates - so you would store it there

but my question is would I need some coding that determines what price is going to be selected based on the date?

yes the sql would be something like

select HourlyRate from RangeOfWageDates WHERE startdate=(select max(startdate) from mytable as tmp where [enter date]<=nz(mytable.enddate,mydate) and [enter date]>=startdate and tradesID=[enter trade ID])

 

[JHB]

..
I tried to add a sub form with the fields [Material SR] [Material] [Description] [Stock/NonStock] [QTY][UnitPrice] but when I jump to the next record on my main form the subform stays the same!

Surely enough because you didn't link the main form and the subform using the "Link Master Fields" and "Link Child Fields" property.
It was a fatal mistake to abandon main-/subform the idea and move on to using predefined lists method because it has a major effect on how your tables look like now and how they actually should have been.
If you intend to use your database professionally so get it redone to use main-/subform, otherwise you will get lots of problem in the future, especially in connection with creating of queries, because your tables are storing data horizontally and not vertically.