Report - Summing fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
Could you share your sql and possibly some sample output? Have you tried
using:
=Sum([Field1])
 
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;




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

--
Duane Hookom
MS Access MVP
--

Sean said:
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?
Click to expand...
Click to expand...
 
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.

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

--
Duane Hookom
MS Access MVP
--

Sean said:
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?
Click to expand...
Click to expand...
 
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
--

Sean said:
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;




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

--
Duane Hookom
MS Access MVP
--

Sean said:
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?
Click to expand...
Click to expand...
Click to expand...
 
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.

Duane Hookom said:
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
--

Sean said:
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;




Duane Hookom said:
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?
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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.

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

--
Duane Hookom
MS Access MVP
--

Sean said:
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?
Click to expand...
Click to expand...
Click to expand...
 
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
--

Sean said:
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 said:
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.

Duane Hookom said:
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?
Click to expand...
Click to expand...
Click to expand...
 
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?

Duane Hookom said:
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
--

Sean said:
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 said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Provide some sample records and desired results.

--
Duane Hookom
MS Access MVP


Sean said:
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?

Duane Hookom said:
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
--

Sean said:
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?
Click to expand...
Click to expand...
Click to expand...
 
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


Duane Hookom said:
Provide some sample records and desired results.

--
Duane Hookom
MS Access MVP


Sean said:
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?

Duane Hookom said:
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?
Click to expand...
Click to expand...
Click to expand...
 
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


Duane Hookom said:
Provide some sample records and desired results.

--
Duane Hookom
MS Access MVP


Sean said:
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?

Duane Hookom said:
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?
Click to expand...
Click to expand...
Click to expand...
 
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.
 
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
 
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.
 
Back
Top