We help IT Professionals succeed at work.

Access 2007 Question on automation

jjc9809
jjc9809 asked
on
Hi everyone,

I am trying to determine the number of months an employee has worked to pay an employee a Longevity check for the year.  The check will be based on the number of months worked.  

I have an Access 2007 database that is updated monthly and all of my fields for the an employee are updated to a table called Complete Employment New.  I do not want to mess with this table by adding a field to it, because I could mess up the automation that is happeing behind the scenes with this table.  So I need to take the Complete Employment New Table and place it in a select query and then create a Make Table Query which will give me a new Table called LongevityNewTableOctober.  Once the new table is created with the Make Table Query, I need to automatically add a new field to the table behind the scenes called DATETO and have DATETO as a Text Data Type.

I then will have an Update Query that will need to run and place a December date in the DATETO field each year for each employee in the LongevityNewTableOctober.  The December Date will change each year.  This year the Update Query had the DATETO field from the LongevityNewTableOctober and when it ran it updated the DATETO field to "12/1/11".  I have placed in the Update Query on the Section "UPDATETO:  the date, "12/1/11" manually.  I need to somehow increment the year by one each year automatically without having to go in and change the "updateTo in the query section each time.  Can this be done automatically somehow?  Also, can I add a new field to the table that is made with the Make Table each time automatically without having to go in the created Make Table and manually place a DATETYO Field and make the Data type Text?

jjc9809
Comment
Watch Question

CERTIFIED EXPERT

Commented:
You can use this equation in your query:
UPDATETO: "12/1/" & right(Year(Date()),2)

Author

Commented:
Yes this will help tremendously.  What about the creation of the DATETO field to the new table made by the Make Table Query?  Is this possible to do this automated?  I just do not see in my mind how this can be done by the computer.  Maybe it can.
CERTIFIED EXPERT
Commented:
If you add this field to make table query, date will be generated automatically.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.