Update one table from another

[yoshimarine]

I am trying to update one table that has one record for each employee(table
1) with available vacation time. The other table records every time off
request(table 2) and how much time they want off. I have the update query
and it works fine. The problem is that everytime it is ran every requested
time off amount(from table2) is subtracted from the available time(table1)
again and again. I want the records for requested time(table2) to update the
employee available time off(table1) only once, but keep the records on the
table as that is the basis for a report.

Thank You,
Brett

 

[John W. Vinson]

I am trying to update one table that has one record for each employee(table
1) with available vacation time. The other table records every time off
request(table 2) and how much time they want off. I have the update query
and it works fine. The problem is that everytime it is ran every requested
time off amount(from table2) is subtracted from the available time(table1)
again and again. I want the records for requested time(table2) to update the
employee available time off(table1) only once, but keep the records on the
table as that is the basis for a report.

Thank You,
Brett

Don't store the available time off in ANY table anywhere. Instead, calculate
it using a totals query on demand.

 

[yoshimarine]

Don't store the available time off in ANY table anywhere. Instead, calculate
it using a totals query on demand.

I have to have a record of the total available time somewhere so that a
report can be ran to show how much time an employee has available. I don't
want to total the time used, but subtract the amount scheduled to be used
from what's available. Employees sometimes schedule vacations months in
advance and I don't want to total or subtract those numbers until the
vacation dates start. If there isn't a simple way to do this, the only other
thought I had was to create another table that will store all of the vacation
scheduled dates and amounts as historical data and have another table that
the employees can enter vacation times, this table will run an update to the
available time table and then run an append to the historical data table(for
the reports) and then run a delete query so that the same records will not be
updated twice.

Thank You,
Brett

 

[yoshimarine]

Don't store the available time off in ANY table anywhere. Instead, calculate
it using a totals query on demand.

I have to have a record of the total available time somewhere so that a
report can be ran to show how much time an employee has available. I don't
want to total the time used, but subtract the amount scheduled to be used
from what's available. Employees sometimes schedule vacations months in
advance and I don't want to total or subtract those numbers until the
vacation dates start. If there isn't a simple way to do this, the only other
thought I had was to create another table that will store all of the vacation
scheduled dates and amounts as historical data and have another table that
the employees can enter vacation times, this table will run an update to the
available time table and then run an append to the historical data table(for
the reports) and then run a delete query so that the same records will not be
updated twice.

Thank You,
Brett