MS-Access auto enter information based on date
- by Desert Spider
I have a query that calculates an employees anniversary date. I would like that query to generate an entry event for my Table based on the current date. Basically automatically generate an anniversary vacation accrual when their anniversary date comes. Here is an example of my table.
Table name "SchedulingLog"
LogID "PrimaryKey AutoNbr"
UserID "Employee specific"
LogDate
EventDate
Category "ex Vacation, Anniversary..."
CatDetail "ex. Vacation Day Used, Anniversary..."
Value "ex. -1, 1..."
My query
Query Name "qry_YOS"
UserID "Employee Specific"
DOH "Employee hire date"
YearsOfService "calculated Field"
Annual "calculated Field"
Schedule "Employee Specific"
Annual Vac Days "calculated field"
Anniversary "calculated Field"
Query associated SQL
INSERT INTO schedulinglog
(userid,
[value],
eventdate,
logdate,
category,
catdetail)
SELECT roster.userid,
[annual] * [schedule] AS [Value],
Month([wm doh]) & "/" & Day([wm doh]) & "/" & Year(DATE()) AS EventDate,
DATE() AS LogDate,
category.[category name] AS Category,
catdetail.catdetail
FROM roster,
tblaccrual,
category
INNER JOIN catdetail
ON category.categoryid = catdetail.categoryid
WHERE (( ( [tblaccrual] ! [years] ) < Round(( DATE() - [wm doh] ) / 365, 2) ))
GROUP BY roster.userid,
roster.[wm doh],
Round(( DATE() - [wm doh] ) / 365, 2),
roster.schedule,
Month([wm doh]) & "/" & Day([wm doh]) & "/" & Year(DATE()),
DATE(),
category.[category name],
catdetail.catdetail
HAVING ( ( ( category.[category name] ) LIKE "vacation*" )
AND ( ( catdetail.catdetail ) LIKE "anniversary*" ) );
I know it is possible I just dont know where to begin.
