Adding employee hours done for each completed sale

[crxftw]

Hello. I have database that stores data about sales that are done to the clients and sales will be made into invoices at the end of the month. For every sale there are certain employees doing work who get paid hourly. Previous solution for setting how many hours employee worked for that client that day was simply that employees were set up into teams and team was chosen who did work for client. After submitting sale, total hours done was split (divided by 2) and stored into table that held employee hours done. This system was not working very well as sometimes say total work was done 4 hours, but one employee did 1 hour and other one 3 hours of that and you had to change hours done manually because it was split after completing sale.

What I would like to do now is that when sale is already submitted, then you have an option to complete sale which should prompt up window where you can choose employees who did the work and enter hours done manually for every employee you chose. That way I could eliminate the team part. I would like to ask ideas how I can achieve that and get some tips maybe.



Hours done for every employee is needed of course to print payslips at the end of the month for every employee.

Regards

[TheShabz]

How you set it up on your form is up to you. As far as the table setup, I would create a junction table to facilitate a many to many relationship between your table containing the sales information and the table containing employee information. you would had a field to this table called hoursWorked or something like that. This table at the very minimum would have 4 fields. The first one would be an autonum primary key. the second would be a foreign key to the saleID. the third a foreign key to the empID. the fourth would be hours worked. Upon sale creation, you would query from your form to populate the junction table with the empIDs of all the employees involved with that sale. upon completion, you would update the records with the hours worked (UPDATE tblName SET hoursWorked = FormControl WHERE empID = FormControl AND salesID = FormControl).

You can then query the junction table for total hours worked or whatever else you want. Again, how you get it onto the form is up to you.

[crxftw]

Hehe thanks for answering, I figured that out already, forgot to edit the post tho. Cheers