Using Access to Schedule Dates (1 Viewer)

[JaoJack]

Greetings from a first timer.

I am working on a db for the company I work for. The db is a computer/asset inventory and it captures a ton of information. One particular piece of info on each computer is [Workstations].[Implementation Date]. What I need to accomplish is to look at the date in the [Implementation Date] and return a date that would be the designated "Refresh Date" (or Replacement Date if you will).

Here is where it gets tricky;

1. We are on a minimum 4 yr lifecycle for the refresh schedule
2. We have 2 type of devices (Laptops and Workstations)
3. We can only refresh 1 workstation or 2 laptops per month

So, if I have 5 computers all due in the same month, I need it to push the remaining to the next month and continue the building the schedule. I can (and already have) accomplish this in Excel, but need to figure out how to automat it in Access.

I was able to do a simple DateAdd in a query to add 4 years, but have been unsuccessful in getting it to determine any information from the previous line item in the query.

Thank you in advance for any direction or assistance.

 

[theDBguy]

Hi. Not sure there is a problem here. Let's pretend you have 10 computers that are due for a refresh, 5 this month and 5 next month. So, when you run your query today, you'll get 5 computers back. However, you can only refresh 3 of them. So, you update their implementation date, correct? Afterwards, if you rerun the same query for this month, you'll get the remaining two computers due this month.

Okay, you wait until the next month, where we expect 5 more computers are due for a refresh. So, when you run the query again, it should return 7 computers due for a refresh: the 5 for this month and the two from last month. Again, you can only refresh 3 of them, so you update their implementation date, so when you rerun the query again, you'll get back 4 computers still due.

Every month you do this, you get all the computers due for a refresh and only update the ones you actually replace. You can even sort the result if you want to prioritize the oldest ones to be replaced first.

What am I missing?

 

[Isaac]

Are you wanting to pre-assign all replace dates, one time, at the beginning? you might need to write some vba for this

 

[JaoJack]

I am hoping it will refresh each time the query (or report) is ran, I do not need to maintain this data permanently as it is always subject to change based on if a computer is replaced early and the inventory changes.

At the moment I only have a query that sorts the Implementation Date in Ascending Order. Then the Refresh Date shows me 4 yrs out (though this is incorrect, it is my current placeholder). However, if I need to move the idea to a Report I can (i tried starting with a report, but had no luck there either)

 

[CJ_London]

To get information on the previous item you will need to use a sub query, however in databases ‘previous’ means nothing without an order- presumably the refresh date. But this assumes these are unique (no two machines refresh on the same day). If this is the case you will need a second field you can use to define the order - might be the pk for example)

there may be other priorities- senior management take priority for example

 

[JaoJack]

@theDBguy Essentially, you are correct on the shifting out multiple months. The issue that I have is that I do not want to change the Implementation Date in the table, as this data is linked to other areas in the overall db, warranty expiration dates for example.

Trying to automate it and finally print a report for my CFO... and would be nice to glace and tell a Sr. Exec that I cannot replace his computer for another xx number of months.

 

[JaoJack]

@CJ_London SO Query_1 pulls the information and sorts it by ImpDate and Query_2 queries Query_1 to find the order and build the Schedule? Would this be an On Load VBA code assigned to Query_2?

 

[theDBguy]

@theDBguy Essentially, you are correct on the shifting out multiple months. The issue that I have is that I do not want to change the Implementation Date in the table, as this data is linked to other areas in the overall db, warranty expiration dates for example.

Trying to automate it and finally print a report for my CFO... and would be nice to glace and tell a Sr. Exec that I cannot replace his computer for another xx number of months.

Hi. At my work, when IT refreshes my machine, I get a new one. I don't know what they do with the old one. So, if you're saying you need to keep them in the inventory, then simply add a RefreshedDate field to indicate when the machine was refreshed. You can then use this information as a criteria to exclude them from the next batch to refresh. So, in the above steps I posted, you don't update the Implementation Date; instead, you update or enter the Refreshed Date. Hope that makes sense...

 

[Pat Hartman]

As theDBguy was trying to explain, your query should always return all PC's whose install dates are > 4 years in the past rather than those due to refresh in a particular month.