Report - Summing fields

[Guest]

I am using access to do payroll (tracking hours only) and I have a report
based on a query (the query prompts you for a date range). 1 field sums
hours for total hours, 2nd field gives regular hours, 3rd field gives
overtime hours, 2nd and 3rd fields are using an iif statement based off of
the sumof hours field. Now in my report, how do I sum field 1, field2, and
field3 for a total of each field?

 

[Duane Hookom]

Could you share your sql and possibly some sample output? Have you tried
using:
=Sum([Field1])

 

[Guest]

Here is my SQL for the report. I am trying to sum each of the expression in
my report (maybe 3 fields in the footer of the report) so I can see all of
the total hours(sumofhours), all of the regulat hours (expr1) and all of the
overtime hours (expr2).

SELECT Payroll.Driver_Num, Sum(Payroll.Hours) AS SumOfHours,
IIf(Sum(Payroll.Hours)>40,40,Sum(Payroll.Hours)) AS Expr1,
IIf(Sum(Payroll.Hours)>40,(Sum(Payroll.Hours)-40),0) AS Expr2,
Driver.First_Name, Driver.Last_Name
FROM Driver INNER JOIN Payroll ON Driver.Driver_Num = Payroll.Driver_Num
WHERE (((Payroll.Date)>=[Enter 1st Date] And (Payroll.Date)<=[Enter 2nd
Date]))
GROUP BY Payroll.Driver_Num, Driver.First_Name, Driver.Last_Name;

Could you share your sql and possibly some sample output? Have you tried
using:
=Sum([Field1])

--
Duane Hookom
MS Access MVP
--

I am using access to do payroll (tracking hours only) and I have a report
based on a query (the query prompts you for a date range). 1 field sums
hours for total hours, 2nd field gives regular hours, 3rd field gives
overtime hours, 2nd and 3rd fields are using an iif statement based off of
the sumof hours field. Now in my report, how do I sum field 1, field2,
and
field3 for a total of each field?

 

[Guest]

Sean Again, can a sum function be used in a sub query? I am having a little
trouble. What I was trying to do also (see previous reply) is sum the field
hours in a sub query as total_hours and possible sum expr1 and 2 in a sub
query.

Could you share your sql and possibly some sample output? Have you tried
using:
=Sum([Field1])

--
Duane Hookom
MS Access MVP
--

I am using access to do payroll (tracking hours only) and I have a report
based on a query (the query prompts you for a date range). 1 field sums
hours for total hours, 2nd field gives regular hours, 3rd field gives
overtime hours, 2nd and 3rd fields are using an iif statement based off of
the sumof hours field. Now in my report, how do I sum field 1, field2,
and
field3 for a total of each field?

 

[Duane Hookom]

Why would you not use:
SELECT Payroll.Driver_Num, Sum(Payroll.Hours) AS SumOfHours,
IIf(Sum(Payroll.Hours)>40,40,Sum(Payroll.Hours)) AS RegHours,
IIf(Sum(Payroll.Hours)>40,(Sum(Payroll.Hours)-40),0) AS OTHours,
Driver.First_Name, Driver.Last_Name
FROM Driver INNER JOIN Payroll ON Driver.Driver_Num = Payroll.Driver_Num
WHERE (((Payroll.Date)>=[Enter 1st Date] And (Payroll.Date)<=[Enter 2nd
Date]))
GROUP BY Payroll.Driver_Num, Driver.First_Name, Driver.Last_Name;

Then in your report, use
=Sum([SumOfHours])
=Sum([RegHours])
=Sum([OTHours])

--
Duane Hookom
MS Access MVP
--

Here is my SQL for the report. I am trying to sum each of the expression
in
my report (maybe 3 fields in the footer of the report) so I can see all of
the total hours(sumofhours), all of the regulat hours (expr1) and all of
the
overtime hours (expr2).

SELECT Payroll.Driver_Num, Sum(Payroll.Hours) AS SumOfHours,
IIf(Sum(Payroll.Hours)>40,40,Sum(Payroll.Hours)) AS Expr1,
IIf(Sum(Payroll.Hours)>40,(Sum(Payroll.Hours)-40),0) AS Expr2,
Driver.First_Name, Driver.Last_Name
FROM Driver INNER JOIN Payroll ON Driver.Driver_Num = Payroll.Driver_Num
WHERE (((Payroll.Date)>=[Enter 1st Date] And (Payroll.Date)<=[Enter 2nd
Date]))
GROUP BY Payroll.Driver_Num, Driver.First_Name, Driver.Last_Name;

Could you share your sql and possibly some sample output? Have you tried
using:
=Sum([Field1])

--
Duane Hookom
MS Access MVP
--

I am using access to do payroll (tracking hours only) and I have a
report
based on a query (the query prompts you for a date range). 1 field
sums
hours for total hours, 2nd field gives regular hours, 3rd field gives
overtime hours, 2nd and 3rd fields are using an iif statement based off
of
the sumof hours field. Now in my report, how do I sum field 1, field2,
and
field3 for a total of each field?

 

[Guest]

Thanks for the reply. I discovered what I was doing wrong... I was trying
to sum the report and have the fields sum in the report footer and I would
get a #Error in the field. Once I moved it out of the footer it worked.

Why would you not use:
SELECT Payroll.Driver_Num, Sum(Payroll.Hours) AS SumOfHours,
IIf(Sum(Payroll.Hours)>40,40,Sum(Payroll.Hours)) AS RegHours,
IIf(Sum(Payroll.Hours)>40,(Sum(Payroll.Hours)-40),0) AS OTHours,
Driver.First_Name, Driver.Last_Name
FROM Driver INNER JOIN Payroll ON Driver.Driver_Num = Payroll.Driver_Num
WHERE (((Payroll.Date)>=[Enter 1st Date] And (Payroll.Date)<=[Enter 2nd
Date]))
GROUP BY Payroll.Driver_Num, Driver.First_Name, Driver.Last_Name;

Then in your report, use
=Sum([SumOfHours])
=Sum([RegHours])
=Sum([OTHours])

--
Duane Hookom
MS Access MVP
--

Here is my SQL for the report. I am trying to sum each of the expression
in
my report (maybe 3 fields in the footer of the report) so I can see all of
the total hours(sumofhours), all of the regulat hours (expr1) and all of
the
overtime hours (expr2).

SELECT Payroll.Driver_Num, Sum(Payroll.Hours) AS SumOfHours,
IIf(Sum(Payroll.Hours)>40,40,Sum(Payroll.Hours)) AS Expr1,
IIf(Sum(Payroll.Hours)>40,(Sum(Payroll.Hours)-40),0) AS Expr2,
Driver.First_Name, Driver.Last_Name
FROM Driver INNER JOIN Payroll ON Driver.Driver_Num = Payroll.Driver_Num
WHERE (((Payroll.Date)>=[Enter 1st Date] And (Payroll.Date)<=[Enter 2nd
Date]))
GROUP BY Payroll.Driver_Num, Driver.First_Name, Driver.Last_Name;

Could you share your sql and possibly some sample output? Have you tried
using:
=Sum([Field1])

--
Duane Hookom
MS Access MVP
--

I am using access to do payroll (tracking hours only) and I have a
report
based on a query (the query prompts you for a date range). 1 field
sums
hours for total hours, 2nd field gives regular hours, 3rd field gives
overtime hours, 2nd and 3rd fields are using an iif statement based off
of
the sumof hours field. Now in my report, how do I sum field 1, field2,
and
field3 for a total of each field?

 

[Guest]

In addition, since the report is built off of a prompted query (prompting for
2 dates), is there a way to show ton the report he 1st date and the last date
used to run the query?

Sean Again, can a sum function be used in a sub query? I am having a little
trouble. What I was trying to do also (see previous reply) is sum the field
hours in a sub query as total_hours and possible sum expr1 and 2 in a sub
query.

Could you share your sql and possibly some sample output? Have you tried
using:
=Sum([Field1])

--
Duane Hookom
MS Access MVP
--

I am using access to do payroll (tracking hours only) and I have a report
based on a query (the query prompts you for a date range). 1 field sums
hours for total hours, 2nd field gives regular hours, 3rd field gives
overtime hours, 2nd and 3rd fields are using an iif statement based off of
the sumof hours field. Now in my report, how do I sum field 1, field2,
and
field3 for a total of each field?

 

[Duane Hookom]

I don't ever agree with using parameter prompts in queries. I recommend
using references to controls on forms. That said, you should be able to add
a text box bound to:
=[Enter 1st Date]

--
Duane Hookom
MS Access MVP
--

In addition, since the report is built off of a prompted query (prompting
for
2 dates), is there a way to show ton the report he 1st date and the last
date
used to run the query?

Sean Again, can a sum function be used in a sub query? I am having a
little
trouble. What I was trying to do also (see previous reply) is sum the
field
hours in a sub query as total_hours and possible sum expr1 and 2 in a sub
query.

Could you share your sql and possibly some sample output? Have you
tried
using:
=Sum([Field1])

--
Duane Hookom
MS Access MVP
--

I am using access to do payroll (tracking hours only) and I have a
report
based on a query (the query prompts you for a date range). 1 field
sums
hours for total hours, 2nd field gives regular hours, 3rd field gives
overtime hours, 2nd and 3rd fields are using an iif statement based
off of
the sumof hours field. Now in my report, how do I sum field 1,
field2,
and
field3 for a total of each field?

 

[Guest]

Thank you. I am having trouble with time fields. I have a start time and a
stop time field and they are set up in the table as "short time." However, I
am using military time (my guys work 3rd shift) and when I take the stop time
minus the start time it works unless the hour is midnight 0:00. Is there a
way to get the query to recognize 0:00 as 24:00 without having everyone use
24:00 as midnight?

I don't ever agree with using parameter prompts in queries. I recommend
using references to controls on forms. That said, you should be able to add
a text box bound to:
=[Enter 1st Date]

--
Duane Hookom
MS Access MVP
--

In addition, since the report is built off of a prompted query (prompting
for
2 dates), is there a way to show ton the report he 1st date and the last
date
used to run the query?

Sean Again, can a sum function be used in a sub query? I am having a
little
trouble. What I was trying to do also (see previous reply) is sum the
field
hours in a sub query as total_hours and possible sum expr1 and 2 in a sub
query.

:

Could you share your sql and possibly some sample output? Have you
tried
using:
=Sum([Field1])

--
Duane Hookom
MS Access MVP
--

I am using access to do payroll (tracking hours only) and I have a
report
based on a query (the query prompts you for a date range). 1 field
sums
hours for total hours, 2nd field gives regular hours, 3rd field gives
overtime hours, 2nd and 3rd fields are using an iif statement based
off of
the sumof hours field. Now in my report, how do I sum field 1,
field2,
and
field3 for a total of each field?

 

[Duane Hookom]

Provide some sample records and desired results.

--
Duane Hookom
MS Access MVP

Thank you. I am having trouble with time fields. I have a start time and a
stop time field and they are set up in the table as "short time." However, I
am using military time (my guys work 3rd shift) and when I take the stop time
minus the start time it works unless the hour is midnight 0:00. Is there a
way to get the query to recognize 0:00 as 24:00 without having everyone use
24:00 as midnight?

I don't ever agree with using parameter prompts in queries. I recommend
using references to controls on forms. That said, you should be able to add
a text box bound to:
=[Enter 1st Date]

--
Duane Hookom
MS Access MVP
--

In addition, since the report is built off of a prompted query (prompting
for
2 dates), is there a way to show ton the report he 1st date and the last
date
used to run the query?

:

Sean Again, can a sum function be used in a sub query? I am having a
little
trouble. What I was trying to do also (see previous reply) is sum the
field
hours in a sub query as total_hours and possible sum expr1 and 2 in a sub
query.

:

Could you share your sql and possibly some sample output? Have you
tried
using:
=Sum([Field1])

--
Duane Hookom
MS Access MVP
--

I am using access to do payroll (tracking hours only) and I have a
report
based on a query (the query prompts you for a date range). 1 field
sums
hours for total hours, 2nd field gives regular hours, 3rd field gives
overtime hours, 2nd and 3rd fields are using an iif statement based
off of
the sumof hours field. Now in my report, how do I sum field 1,
field2,
and
field3 for a total of each field?

 

[Guest]

Property_Name Start_Time Stop_Time Total_Time
Kohl's #483 20:55 21:55 1:00
Jewel-Osco #447 22:05 22:55 0:50
Miracle Mile 23:20 0:10 23:10

Provide some sample records and desired results.

--
Duane Hookom
MS Access MVP

Thank you. I am having trouble with time fields. I have a start time and a
stop time field and they are set up in the table as "short time." However, I
am using military time (my guys work 3rd shift) and when I take the stop time
minus the start time it works unless the hour is midnight 0:00. Is there a
way to get the query to recognize 0:00 as 24:00 without having everyone use
24:00 as midnight?

I don't ever agree with using parameter prompts in queries. I recommend
using references to controls on forms. That said, you should be able to add
a text box bound to:
=[Enter 1st Date]

--
Duane Hookom
MS Access MVP
--

In addition, since the report is built off of a prompted query (prompting
for
2 dates), is there a way to show ton the report he 1st date and the last
date
used to run the query?

:

Sean Again, can a sum function be used in a sub query? I am having a
little
trouble. What I was trying to do also (see previous reply) is sum the
field
hours in a sub query as total_hours and possible sum expr1 and 2 in a sub
query.

:

Could you share your sql and possibly some sample output? Have you
tried
using:
=Sum([Field1])

--
Duane Hookom
MS Access MVP
--

I am using access to do payroll (tracking hours only) and I have a
report
based on a query (the query prompts you for a date range). 1 field
sums
hours for total hours, 2nd field gives regular hours, 3rd field gives
overtime hours, 2nd and 3rd fields are using an iif statement based
off of
the sumof hours field. Now in my report, how do I sum field 1,
field2,
and
field3 for a total of each field?

 

[Guest]

If you look at the "Miracle Mile" entry, the total time shows as 23 hours and
10 minutes. It should show 0:50 meaning 50 minutes. Access is not
recognizing military time when using 0:00 as midnight (as it should be) but
it works in all other instances like for entry 1 and 2 below.

Property_Name Start_Time Stop_Time Total_Time
Kohl's #483 20:55 21:55 1:00
Jewel-Osco #447 22:05 22:55 0:50
Miracle Mile 23:20 0:10 23:10

Provide some sample records and desired results.

--
Duane Hookom
MS Access MVP

Thank you. I am having trouble with time fields. I have a start time and a
stop time field and they are set up in the table as "short time." However, I
am using military time (my guys work 3rd shift) and when I take the stop time
minus the start time it works unless the hour is midnight 0:00. Is there a
way to get the query to recognize 0:00 as 24:00 without having everyone use
24:00 as midnight?

I don't ever agree with using parameter prompts in queries. I recommend
using references to controls on forms. That said, you should be able to add
a text box bound to:
=[Enter 1st Date]

--
Duane Hookom
MS Access MVP
--

In addition, since the report is built off of a prompted query (prompting
for
2 dates), is there a way to show ton the report he 1st date and the last
date
used to run the query?

:

Sean Again, can a sum function be used in a sub query? I am having a
little
trouble. What I was trying to do also (see previous reply) is sum the
field
hours in a sub query as total_hours and possible sum expr1 and 2 in a sub
query.

:

Could you share your sql and possibly some sample output? Have you
tried
using:
=Sum([Field1])

--
Duane Hookom
MS Access MVP
--

I am using access to do payroll (tracking hours only) and I have a
report
based on a query (the query prompts you for a date range). 1 field
sums
hours for total hours, 2nd field gives regular hours, 3rd field gives
overtime hours, 2nd and 3rd fields are using an iif statement based
off of
the sumof hours field. Now in my report, how do I sum field 1,
field2,
and
field3 for a total of each field?

 

[Duane Hookom]

To display the number of the minutes.
SELECT Property_Name, Start_Time, Stop_Time,
IIf(Start_Time<Stop_Time, DateDiff("n", Start_Time, Stop_Time),
DateDiff("n", Stop_Time, Start_Time)) As Total_Minutes
FROM tblYourTable;
This will return the number of minutes and not a date/time value.

 

[Guest]

Can you help me with this.

On my form I have 2 list boxes and 1 text box (Box 3). Once I select the
data for list box 1 and list box 2, I want list box 3 to run a query against
a table using the values on the form in box 1 and 2 (the where statement) to
get the data for box 3. Afterward I want to beable to have a command button
that will append the form data into a different table.

I tried a couple of things but I do not have the correct syntax etc.

Thanks

 

[Duane Hookom]

Are the list boxes multiple select? Is the third control a text box or list
box? How do you expect a "box 3" control ro run a query?

Your question isn't any more clear than the same question I seemed to have
read elsewhere. Please continue in only the latest thread if they are the
same.