Mass update

[joe31709]

I have a table that I would like to update daily with time. I want to update eveyone on the list with 8 hours. This table is a one to many table to the main table.

Any Ideas?

Thank you

 

[namliam]

shouldnt be that hard Some examples would have been nice. You have a list of people working today and want to update that to a main table (1 to many?!)

Update or append? Either shouldnt be that hard but some more info please.....

Regards

Tha Mailman

 

[joe31709]

There are about 70 employees and I would like to have them all updated. That way if anyone is off or taking sick leave I will just have to go in there and update one or 2 not 70.

Does that help anyone?

 

[namliam]

You are talking about a 1 to many relationship
Table1
employeeNr
1
2
3
4

Table2
employeeNR
1
1
1
2
2

That is a one to many relationship ! But i dont think that is what you are talking about

You have 1 table:
EmployeeNR-EmpName-morestuff-HoursWorked

and want to add 8 hours to HoursWorked:
UPDATE Table1 SET Table1.HoursWorked =nz( [HoursWorked])+8;
should do the trick, if you have some other settup or problem please be more clear....

Regards

 

[joe31709]

here is example

the main table has the first and last name, social security, wage grade, mostly personal stuff. The other table is called payperiod, in it has last name hours worked, leave hours, date, sick leave, and info on the payperiod.

the main table is the one and the payperiod is the many in the one to many

What I want to do is right a query where I can put every person in the payperiod table and put 8 hours in there every day.

example

joe monday august 18 2003 8 hours
joe tuesday august 19 2003 8 hours

That way I can have a record for everyday in the payperiod.
So all the last names in the main table will get updated everyday in the payperiod table.

Does that help any?

 

[namliam]

Yes that helps, but PLEASE tell me you are NOT linking two tables on LastName. There must be more than 1 Johnes working there or whatever. Please tell me you actually use the social sec no or some PK (autonumber) PLEASE???

Do you have a table with dates for the payperiods or do you want to enter them (one by one) by hand or run it from VBA?

Regards

 

[namliam]

Assuming you ARE using last name and dont have a workdays table and want to run in day by day entering the date manually

INSERT INTO PayPeriod ( LastName, WorkDay, HoursWorked )
SELECT MainTable.LastName, [Enter Date] AS ManualDate, 8 AS Hours
FROM MainTable;

If you want to use to system date change [enter date] to Date()

You might also want to check in the table if it has allready been done to prevent doubles....

Regards

 

[joe31709]

If i told you that I would be lying . As of now there is no duplicates in the main table. There is no payperiod table. For the payperiod I was going to use a report and enter in the dates of the payperiod.

What I was thinking was maybe like a button I could use on the switchboard. After I click it would update the hours for that day.

Anyhelp is good, as you can see i can use it

 

[namliam]

you can put that SQL into any code /button you want...

Sub addTodaysHoursToPayPeriod
    Currentdb.execute("INSERT INTO PayPeriod ( LastName, WorkDay, HoursWorked ) SELECT MainTable.LastName, Date() AS ManualDate, 8 AS Hours FROM MainTable")
    msgbox "Hours for today "& date() & "added", vbokonly
end sub

Use docmd.runsql to get messages instead of the silence i used above (currentdb.execute) but i wouldnt recomend it...

If you post your next question WITH the post you started "here is example" it will speed up things considerably, dont you think?

Regards