Mass update (1 Viewer)
- Thread starter joe31709
- Start date
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
Update or append? Either shouldnt be that hard but some more info please.....
Regards
Tha Mailman
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
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
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?
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?
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
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
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
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
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
. 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
you can put that SQL into any code /button you want...
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
Code:
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 subIf you post your next question WITH the post you started "here is example" it will speed up things considerably, dont you think?
Regards
