Creating Notifications

[qdaccounting]

Hi,

I'm not sure if this question has arisen before. I would like to create a database that lists our Sub Contractors and enter their contact information as well as when their insurance and/or license expires. Once that is entered is there a way that every day when you open that particular database something pops up with a list of all the subs that have insurances that are expiring so that I can contact them and get the new info before the old info expires?

I hope I explained this correctly?
Thanks,
DV

[UnklDonald418]

is there a way that every day when you open that particular database something pops up with a list of all the subs that have insurances that are expiring so that I can contact them and get the new info before the old info expires?

Yes, SQL has a DATEDIFF function that could be used in a query to give that information. For instance if you had a database table with a DATE field named "ExpDate", then you could add a clause to a query, something like

Code: Select all

DATEDIFF( 'day', CURDATE( ), "ExpDate" ) AS "Diff"

which would generate an integer value "Diff" for each record in your database table, "Diff" being the number of days from the current date until the expiration date.
Then if you added a WHERE clause to the query, something like

Code: Select all

WHERE "Diff" <  30 

you could limit the results to only the records where the expiration date is less than 30 days from the date the query is run.
To write a complete query requires knowledge of your database table structure.