2nd Post; Really Need Help, Cant calculate Invoice Price Without it!

D

Dave Elliott

Trying to calculate how many records have employees with a count greater
than 2 that worked on the same day.
That is any record that has more than 2 employees on the same day...
This is a single sub-form with record selectors

=DCount("[EmployeeID]","Employees","[Work Date] = " & [WorkDate])

SELECT Hours.SinMar, Hours.NoAllow, Hours.PayRate, Hours.Hours,
IIf([TEmpOrCon].[EmpOrCon]=0.2,1*[PayRate],[Hours]*[PayRate]) AS RegPay,
Hours.OTRate, Hours.Overtime, [Hours].[OTRate]*[Overtime]*[PayRate] AS
OTPay, [RegPay]+[OTPay] AS ExpPayPerDay, Hours.TimeID, Hours.HoursID,
Hours.EmployeeID, Employees.[First Name], Hours.[Work Date], Employees.[Last
Name], Hours.ChkNoID, IIf(IsNull([ChkNoID]),0,-1) AS Paid
FROM (Hours LEFT JOIN Employees ON Hours.EmployeeID = Employees.[Employee
ID]) LEFT JOIN TEmpOrCon ON Employees.EmpOrCon = TEmpOrCon.EmpOrConID
ORDER BY Hours.[Work Date] DESC , Employees.[Last Name];

Probably will have to put on either Time and Hours or footer on same and
then
The rest of the code will be on the main form TimeCards using a unbound
textbox using the values
of the Time and Hours form
 
J

JohnFol

Hi Dave, sorry to hear it's your 2nd post, but it's unclear what you want.

If you want to get a list of employees where 2 or more worked on the same
day, it would be usefull to give some indication regarding the table
structure.

The SQL you pasted seems completely unrelated as it covers pay rates, hours
etc.

You then mention about putting fields on a footer

Can you be a bit more specific about what you want rather than give subtle
digs that no-one has helped?
 
D

Dave Elliott

The sql is the code behind the sub-form Time and Hours where I have the
problem.
I need to know how many employees there are where the number of employees is
greater than 2 on the same day
Yes people have tried to help and I greatly appreciate it.
i.e. Forest worked on Monday, Bubba worked on Monday and Jack worked on
Monday (same date)

The form is used to enter the employee,work date,regular hours, overtime
hours,
it then calculates out the rate for the employee and their hours and totals
the payroll for the job.
This all works fine.

The problem I have is that if more than employees work on the same day then
the rate changes.
For 2 people the rate $65.00 an hour
For every other person on the same day the rate is $32.50 or 1/2 the normal
rate
So for three people the rate would be $97.50 for each man hour worked.,
etc... each additional man is $32.50

The sub-form has as it's record source a query named Time_Hours
3 tables make up this query; Hours:Employees:TEmpOrCon
Fields on the form are;
Employee: uses query Employee with tables Employee and TEmpOrCon
TEmpOrCon shows what type of employee, i.e. employee or sub-contractor
Work Date which shows the date;format is m/d/yy default is Date()
Also a field which shows the date in this format ; =Format([Work
Date],"dddd") Text34 is the name of this textbox
And a Hours field which is for inputting the hours
It has other fields , but these are not important for this calculation.

The form shows me all the answers I need, just not in a way for me to
calculate them.
I can scroll thru the records and manually add up the number of employees
who worked on the same day if the count was greater than 3 and then, etc...
Some records have many records on them in excess of 100
Remember this is a sub-form and so represents one (1) record via the main
form. (TimeCards is the name of this form)

Dont know what else to explain;

Thanks,

Dave
 
Top