creating a date range based form

[fredindy]

Hope the subject is sufficient. Here is what is going on.

I need to create a form that will display data within a date range...
no
biggie. However, I'd like to keep it as close to the example I have
been
working off of, which is very similar to a datasheet view or crosstab
style.

Information I am going to pull is:
EmployeeID (should be a hidden field), Employee name (concatenated
data),
Pay Rate, Hours Worked, and then sum of payrate * hours worked. Then,
I need
to sum all of the pay amounts (sum of payrate * hours worked) on the
form
footer (which I assume will just be a dsum).

Also should say that I want to group the info by employee and only have
one
row of data per employee. So I need a total of hours worked and pay
rates
per day. There is a possibility that an employee could have a
different pay
rate assigned based upon the work they did.

Is there a way to do this and keep it in a crosstab-like style?

Hope that's clear enough. Any help is greatly appreciated!

[fredindy]

Would be nice for a bit of help. I'd greatly appreciate it.
fredindy wrote:

Hope the subject is sufficient. Here is what is going on.

I need to create a form that will display data within a date range...
no
biggie. However, I'd like to keep it as close to the example I have
been
working off of, which is very similar to a datasheet view or crosstab
style.

Information I am going to pull is:
EmployeeID (should be a hidden field), Employee name (concatenated
data),
Pay Rate, Hours Worked, and then sum of payrate * hours worked. Then,
I need
to sum all of the pay amounts (sum of payrate * hours worked) on the
form
footer (which I assume will just be a dsum).

Also should say that I want to group the info by employee and only have
one
row of data per employee. So I need a total of hours worked and pay
rates
per day. There is a possibility that an employee could have a
different pay
rate assigned based upon the work they did.

Is there a way to do this and keep it in a crosstab-like style?

Hope that's clear enough. Any help is greatly appreciated!

[Ron2006]

I am seeing a conflict in what you are saying you want.

You say only one per employee, but at the same time are saying that you
may have different payrates for different days. So what pay rate do you
want to show.

If you created a sum query that broke on employee and payrate, that
summed on hours, with criteria of the date range and created a field in
the query that was the product of that sum of hours times the payrate,
you would have everything EXCEPT the only one per employee you said you
wanted.

>From there it depends on what your answer to the question in para 2 is.

Ron.

[fredindy]

Ok, I think I see what your saying. Here's the final result I'm
looking for (in regards to the employees).

If Employee A has 3 different jobs on the same day at 3 different pay
rates, I want to get the total amount of pay Employee A earned on that
day.

Ex.
Employee A
Day x does a job at rate 1 for 4 hours and earns a total of 60 bucks
Same day does a job at rate 2 for 2 hours and earns 40 bucks
same day does a job at rate 3 for 1 hour and earns 25 bucks

Desired result would be:

Employee Day x
A $125

Hope that lines up right.

Once that is resolved, I want to take Employees that worked during a
given date range and display them on a form and again on a report, in
crosstab fashion.

Hope that makes sense.
Ron2006 wrote:

I am seeing a conflict in what you are saying you want.

You say only one per employee, but at the same time are saying that you
may have different payrates for different days. So what pay rate do you
want to show.

If you created a sum query that broke on employee and payrate, that
summed on hours, with criteria of the date range and created a field in
the query that was the product of that sum of hours times the payrate,
you would have everything EXCEPT the only one per employee you said you
wanted.

From there it depends on what your answer to the question in para 2 is.

Ron.

[Ron2006]

Here is a simple query that will work for a simple table:

SELECT [pay table].Emp, Sum([pay table]![pay rate]*[pay table]![hours])
AS DayPay
FROM [pay table]
WHERE ((([pay table].wrkdate) Between [Enter Start Date] And [Enter End
Date]))
GROUP BY [pay table].Emp;
In place of the [Enter Start Date] and [Enter End Date] you can
reference two unbound fields on the form you are executing this from.

for example:

SELECT [pay table].Emp, Sum([pay table]![pay rate]*[pay table]![hours])
AS DayPay
FROM [pay table]
WHERE ((([pay table].wrkdate) Between Forms![Report Selection
Form]![Start Date] And Forms![Report Selection Form]![End Date]))
GROUP BY [pay table].Emp;

Ron