Create priority using date

[MrGrumpy]

Hi everyone!

I am working on a Facilities Maintenance database, which allows users to create maintenance requests. The Facilities department views the logged maintenance requests, and creates a work order. This is 90% finished, but I have come to a problem that I can't find an answer to.

I need to prioritize the "Maintenance Request" list for the facilities department. When maintenance is requested, the following priority system is used.

Priority 1 = Urgent (Production has stopped - Over-runs any other priority level)
Priority 2 = High (Slows down production)
Priority 3 = Low (minor impact)

I have this working on a simple "ascending" scale query, but Management has asked for the "date raised" to be considered (allows for jobs that have been sitting in the queue for a longer time to have a higher priority). Good old bosses!!! 2 seconds to ask for a job that I have spent hours trying to fix!

I have this strategy that I want to implement (I have no idea how). I want to add a second field with a numerical value (priority score), which is possibly calculated by a query.
Priority 1 = 1000 (priority score)
Priority 2 = 20
Priority 3 = 10

I also want the date difference between now and the "request raised" date to be added into the equation. Eg, if the request was raised 5 days ago, then 5 is added to the priority score.

I can easily do this on excell, but unfortunately, Access won't accept the equation. Can someone please help me do this?

 

[DCrake]

You need

Priority = 10 + DateDiff("d",[RequestDate],Date)

Where 10 is the base figure for the relevant priority type

David

 

[WIS]

Hi everyone!

I am working on a Facilities Maintenance database, which allows users to create maintenance requests. The Facilities department views the logged maintenance requests, and creates a work order. This is 90% finished, but I have come to a problem that I can't find an answer to.

I need to prioritize the "Maintenance Request" list for the facilities department. When maintenance is requested, the following priority system is used.

Priority 1 = Urgent (Production has stopped - Over-runs any other priority level)
Priority 2 = High (Slows down production)
Priority 3 = Low (minor impact)

I have this working on a simple "ascending" scale query, but Management has asked for the "date raised" to be considered (allows for jobs that have been sitting in the queue for a longer time to have a higher priority). Good old bosses!!! 2 seconds to ask for a job that I have spent hours trying to fix!

I have this strategy that I want to implement (I have no idea how). I want to add a second field with a numerical value (priority score), which is possibly calculated by a query.
Priority 1 = 1000 (priority score)
Priority 2 = 20
Priority 3 = 10

I also want the date difference between now and the "request raised" date to be added into the equation. Eg, if the request was raised 5 days ago, then 5 is added to the priority score.

I can easily do this on excell, but unfortunately, Access won't accept the equation. Can someone please help me do this?

As in Excel, dates are stored in Access as numbers ("31/12/1899" being day 0). Date()-DateRequestRaised will give you a number provided that you are storing values in DateRequestRaised with data type Date.

 

[MrGrumpy]

Thanks for the quick reply David. I entered the data you gave me into both the "Criteria" line of the query, and a new column field. I got the following error message "Data type mismatch in criteria expression". I also tried :

Priority = 10 + DateDiff("d",[Date_Raised],Date())
I also used : Priority_Calculated = 10 + DateDiff("d",[Date_Raised],Date())

"Priority_Calculated" = the field with the new calculated priority level
"Date_Raised" = the date that the request was created

What am I doing wrong?

WIS, thanks for the info. I was only assuming that Access converted a date to a numerical value. It's good to know that it actually does work that way.

 

[DCrake]

Priority_Calculated = 10 + DateDiff("d",[Date_Raised],Date())

You are nearly there

PriorityCalculated:10 + DateDiff("d",[Date_Raised],Date())

PriorityCalculated is the alias name of the field. You need to follow this with a colon, not an equals sign, then the remailnder of the calculation.

David

 

[MrGrumpy]

You are a genius David! Thanks for your help. The quick response was excellent, and accurate You can go home early tomorrow ok!

Consider your scales clicked! Thats one more for your rating!