Append / Update Date ?

  • Thread starter Thread starter Channell
  • Start date Start date
C

Channell

Hello,

I have a question regarding an append query and using a date. I have a
database that keeps track employees, their work areas, and what sectors they
are in within their work areas. Every day, I create new workday records for
all the employees (with great ease, thanks to this community). After I
create workdays (and their work areas are auto-assigned by default), I go
into each employee and assign their sectors (yes, there may be multiple
sectors they work in such as 311, 313, and 315). It is time consuming and
annoying to assign the sectors to the employees when 90% of the employees are
in the same sectors each day.

So, I was curious, after creating the new workday records, if there is a way
I could take the employees who worked the previous days (using an append
query) and update the old date to today's date and append the records as new
records.

I know this was horribly explained, but if you could help me, I would
greatly appreciate it. Thanks!

-Scott Channell
 
Channell said:
Hello,

I have a question regarding an append query and using a date. I have a
database that keeps track employees, their work areas, and what sectors
they
are in within their work areas. Every day, I create new workday records
for
all the employees (with great ease, thanks to this community). After I
create workdays (and their work areas are auto-assigned by default), I go
into each employee and assign their sectors (yes, there may be multiple
sectors they work in such as 311, 313, and 315). It is time consuming and
annoying to assign the sectors to the employees when 90% of the employees
are
in the same sectors each day.

So, I was curious, after creating the new workday records, if there is a
way
I could take the employees who worked the previous days (using an append
query) and update the old date to today's date and append the records as
new
records.
Click to expand...


Sure, Scott, this should be quite easy. I don't know the name of the table
or the fields that have to be copied, but in principle a query to copy the
previous days' assignments to today would look roughly like this:

INSERT INTO WorkdayAssignments
( WorkdayDate,
EmployeeID,
Sector )
SELECT
Date() As WorkdayDate(),
EmployeeID,
Sector
FROM
WorkdayAssignments
WHERE
WorkdayDate = Date() - 1

The table and field names have to be changed, of course, and I may have left
out some fields, but I hope that gives you the idea.
 
Back
Top