Jun 30 2021 05:42 AM
Jun 30 2021 05:42 AM
I have an excel sheet containing information for many machines that needs maintenance, each machine has a starting date of maintenance and the maintenance will be done repetitively in a specific frequency ( some are daily, weekly, monthly, semi annual or annual and many frequencies), I need to put the date of today or tomorrow or any coming date to see what are the machines that need maintenance at that date and the information of each machine
i need to link excel file to access and show the results in access
I will link the file, the task schedule is the frequency (NM:1 is monthly....) the task last date done is the reference date of each machine
how can I do it ?
the dates are in 2012, but I need to increase dates to reach today as in 2021
Jun 30 2021 08:22 AM - edited Jun 30 2021 08:23 AM
There are two parts to the question.
First, filtering on a specific date to see which machines are due for maintenance.
Second, advancing the dates to the present.
From Access, you can link the excel worksheet and use it similarly to how you would a native table, albeit with some limitations. Excel, for example, can't be updated directly from Access, so any new data entries or updates will need to be done on the Excel side. You could resort to VBA to use recordsets to update the workbook, I think, although that's probably more involved than you want.
Write a query in Access, using this linked Excel worksheet, and apply a criteria to the field containing the maintenance date you want to filter on. In essence it would be:
SELECT * FROM YourLinkedExcelWorkSheet WHERE MaintenanceDate = Today()
Noting, however, that you have different schedules for different machines, you'll need to adopt a more complex evaluation. In order to work that out, we'd have to have a full explanation of each of your codes in the column called Tasks.Schedule, though.
Advancing the dates is a matter of updating the value in Tasks.DateLastDone as appropriate. How do you do that now?
Jun 30 2021 08:27 AM
In addition to the basic functionality needed, it is interesting to look at the names of the columns in your worksheet and speculate why they are what they are. It looks to me, as a matter of fact, this is probably an exported query from another database. Tables in that source database are probably:
And maybe one other for People Needed, wherever that comes from.
If that's case, I'd strongly recommend bypassing this intermediate Excel step and go directly to that source database to run your queries to get due dates for maintenance.
Jun 30 2021 10:18 AM
Jun 30 2021 10:39 AM
I am retired and I no longer take on client work.
It sounds like this is one of those situations where you have to make some investment, either in the form of effort to learn how to use tool, or in the form of paid consultation to do it for you.
You have data from 2012, correct? That's nine years old. What's been happening in that time? How has this been done? Who did it before? Do you know where the data actually came from? How would you expect to advance the dates? Are all of those machines still in operation after nine years? No new machines? No obsolete machines? At a minimum I'm going to guess this would be a 30 - 50 hour project, if it is done properly. That's much more than is possible in a forum like this unless you are able to do the bulk of the work yourself.
Jun 30 2021 03:50 PM