date increasing

Copper Contributor

Hello all
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

8 Replies

@mok1995 

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?

@mok1995 

 

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:

Sections

Machines

Parts

Tasks 

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.

i am new to access, but i am responsible to do this, can you do it for me please ?
in excel the dates are in 2012, i need to reach my today date to see what tasks need maintenance today but in access
i am new to access i do not know how to work in access, can you help me and do it for me please

@mok1995 

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.

 

 

thank you so much, it is a project for university, but i am new user in access
really thank you dear
"...it is a project for university...."
Do you mean that it is a part of a class you are taking at your university? Or that you are taking on the project while working for the university?

If it is the former, then the best option would be to ask the teacher of that class for assistance.
If it is the latter, then the best option is to discuss this with the person who hired you and clarify your ability to perform the work.

thank you dear