Data Retention

[jsc0624]

Hi guys,

How can I retain my records for the month of January even I updated it during the month of February?

What I mean is?

For example for the month of January, I have inserted the following records;

ID     Quantity     Price     Total
10     2              30         60
11     1              20         20

Month of February comes, but I have to update ID 10 so the table becomes;

ID     Quantity    Price  Total
10     1             30      30
11     1             20      20

Month of March comes, but I have to update ID 10 so the table becomes;

ID     Quantity    Price  Total
10     3             30      90
11     1             20      20

Let's say this always happens for the succeeding year, ID 10 usually change its quantity and price.

Now the question is, as we all know that when we do an update the previous records will be replaced by a new one. How can I make a year end reports that will shows something like this?

Records of ID 10 for the year ___
January    February    March    April and so on and so forth
60           30            90           -

Note: ID Column must be unique, that's why I need to update instead of inserting a new record for the succeeding months.

Thanks in advance!

[si_the_geek]

If you want separate values, the best way is to have separate records.

Do the changes only ever apply to an entire calendar month, or could there be any other possibilities?

If it is an entire calendar month, rather than having just the ID as the PrimaryKey, add another field for the month and use a compound PK (based on both fields).

[jsc0624]

What about this one?

For example I have a list of PO's for the month of February which all of them are accrued. So I tag it inside the database as accrued based on invoicenumber column. If there's no invoice they are accrued and as soon as the database have a record on invoicenumber column the database will automatically changed the status to invoiced.

Now the problem is with the report.
If today is February, it is clear that my PO's are still accrued. So, it will appear in my accruals final report that in February I have for example 10 PO's which are accrued. March is about to come and for sure in my current 10 PO's some of them will be invoiced. So, for example I have already an invoice for 5 out of 10 PO's and I need to update those PO's who have already an invoice. It will be automatically marked by the database as invoiced or completed after updating. If I am going to generate the report for the month of March, the reports will show that I have 5 Invoiced and 5 Accrued PO's which is okay, because I do only have 10 POs. But the problem is if I am going to regenerate the reports for the Month of February, it also reflects that I have 5 Invoiced and 5 Accrued the same with the report for the month of March. February report was also updated. What must I do to make sure that my previous month report will not be affected if I have made some changes for the succeeding month, so that if I am going to create a year end report I could easily identify which PO have been processed for how many months. Thank you in advance.

[si_the_geek]

If you use another column for month as I suggested before, you can work with just the data from that month.