Archive or Delete old Records with some complication :)

[hardhitter06]

Hi Everyone,

Access 2003.

I am new to access and this is going to be the first time I attempt to archive or just delete old records, on one of the databases I have created. I first need an approach on how I am going to solve my problem along with some help with coding.

Here is the situation:

My database has 2 operations both centered around a “UNSPSC” code. This given code and 4 other fields (FedID, CommAmt, Date, PONumber) is all the data this database consists of. To give some meaning to this UNSPSC code, it is an 8 digit number that links to Purchase orders in my office. The two operations my database can perform are to Input a new UNSPSC code (with these fields) if this code doesn’t exist or to search a UNSPSC code to retrieve the records that correspond with this number.

Another important field on the Input/Search forms is CommAmt. This is the amount the user is going to spend given the Purchase order and date (of the PO) that is on the UNSPSC form. The catch is given this UNSPSC code, a user CAN ONLY spend 20,000 in a 365 day span from any PO dates. For example, a user receives a purchase order with a UNSPSC code of 123 for the first time with a date of 3/1/08 and $10,000. They will input this information and will still have 10,000 more to spend within a years time from 3/1/08 (So 3/2/09 the limit will reset to another 20,000 if no other POs fall in this window). Come 10/1/08, another $10,000 purchase order comes with the UNSPSC code of 123 and the user inputs this information once again. Now, the user has reached the 20,000 limit. The user MAY NOT enter another Purchase Order/Date/Amt no sooner than 3/2/09. Now on 3/2/09, the user has $10,000 of the budget left because although the 3/1/08 PO of 10,000 has been over 365 days, the 10/1/08 is still active and you have up to 10/2/09 now to spend 20,000 total. To continue this example, on 5/1/09, user inputs a PO with $5000. Now the user has only $5000 left to spend because now the new period is from 10/1/08-10/2/09 and the previous $10,000 amount is now active with the 5/1/09 PO. Therefore, come 10/2/09, the latest $10,000 PO on 10/1/08 is no longer active (or within the 365 day span) and the user is left with $15000 to spend. I know this sounds extremely confusing so here is a chart to help matters:

Date---------Purchase---------Amount Removed----------Remaining Limit
3/1/08------$10,000-------------$0-----------------------------10,000
10/1/08---- $10,000-------------$0-----------------------------0
3/2/09------$0--------------------$10,000----------------------$10,000
5/1/09------$5,000---------------$0-----------------------------$5,000
10/2/09---- $0--------------------$10,000----------------------$15,000
5/2/10------$0 -------------------5,000-------------------------$20,000

--- Spacing purposes


So basically it’s a flex year because you never really know when the year’s budget will begin, but what is always a given is the 20,000 dollar CommAmt over a 365 day span starting with the first PO Date.

An idea I thought maybe could work is on the input screen, when a user went to fill in a CommAmt after the date was entered, for it to return a Error message explaining that they have exceeded the Budget Amt and either will have to return at a later date or could even display the date when they amt will reset. This would mean it would have to search within the table (there’s only one tbl in this database) to find out what the 365 span is with the amts. I’m not sure…

If there is a better idea out there, pllllllzzzzz help me out because this is the last thing I need to figure out and this database will be set to go.

Thank you!!

 

[WayPay]

Your problem was more "interesting" than I thought ..

I see some possible problems with your approach. Is the UNSPSC code an account code that never changes? It looks like you can only get at the latest expiration date for a given code by following the complete history of that code. That might give problems when the table has a few million records. Then again, it might not . Testing is required. I think I would go for a Balance table that is updated when records are added to tblMain.

A few pointers on the "year plus one day" (that was the "interesting" bit):

Date() + 1

will yield tomorrow.

Date() + 366

will yield 1 year from tomorrow, unless it's between januari 1st and februari 28th in a leap year.

(DateSerial((DatePart("yyyy", dtDate) + 1),DatePart("m", dtDate),DatePart("d", dtDate)))+ IIf(((DatePart("d", dtDate) = 29) And (DatePart("m", dtDate) = 2)),0,1)

will always yield 1 year from tomorrow .

I thought that wouldn't work, but DateSerial( 2009, 2, 29 ) yields 3/1/2009, hence the check for Februari 29th.

This is a bit cleaner (use it in a global module, not a class module):

Public Function DatePlusYearAndOneDay(dtDate As Date) As Date
    DatePlusYearAndOneDay = _
        (DateSerial( _
            (DatePart("yyyy", dtDate) + 1), _
            DatePart("m", dtDate), _
            DatePart("d", dtDate)) _
        ) _
        + IIf( _
            ((DatePart("d", dtDate) = 29) And (DatePart("m", dtDate) = 2)), _
            0, _
            1 _
        )
End Function

Public Function IsLeapYear(nYear As Integer) As Boolean
    IsLeapYear = IIf((Int(nYear / 4) = (nYear / 4)), True, False)
End Function

 

[hardhitter06]

Yes, UNSPSC will always stay the same, but there will be multiple UNSPSC accounts in this database ofcourse This database will also never get this large just based on my office size and what not and if I can get some help on how I can delete records once they are out of the current 20,000 spending year, then there won't be that many records in this database. I appreciate you helping me out with the date stuff but you are just hitting the surface because I am a complete NOOB at coding haha and everything I have coded in the past has been self taught or help through this forum (which I greatly appreciate and can't thank everyone who has helped me enough!).

With that said, if anyone is out there who can help me through this delete coding you would be a huge help for me to finish up this project.

Thanks again Way Pay and if you have anything more, my ears are open haha.

 

[hardhitter06]

Updated Database..doesn't have to do with the problem, but I figured if people are taking a look at my set up, they should see the most current...