Total Query: Adding a field to a calculation.

  • Thread starter Thread starter Flopbot
  • Start date Start date
F

Flopbot

Hi,

I have an Access 03 query based on “Tbl Vol Hours Trackingâ€. In my query,
one of the fields uses the following to calculate the total number of hours
worked using three columns in the table. It works perfectly.

Total: (Sum((DateDiff("n",[Start Time],[End Time])/60)*[Number of People]))


However, the table also has a field called [Other Hours] logging hours from
a completely different source. I want to add these two types of hours
together for my query’s total. I’ve tried the following but it doesn’t work.

Total: (Sum((DateDiff("n",[Start Time],[End Time])/60)*[Number of
People]))+([Other Hours])


Does anyone have any idea of what my next step should be? I’m stuck.

Thank you.
 
A sum is a rollup of values in a record set. When you put +([Other Hours])
you are trying to add the value of a single record to the rollup.

You can not do that.

Try this --
Total: (Sum((DateDiff("n",[Start Time],[End Time])/60)*[Number of People]))+
Sum([Other Hours])
 
Thank you Karl. That worked perfectly and it makes a lot of sense the way
you described it. Thank you too for explaining your answers to help me
learn. In the end, I also added the “Nz†function to both sides in order for
it to work correctly.

Final equation:

Total: (Sum(Nz((DateDiff("n",[Start Time],[End Time])/60)*[Number of
People])))+Sum(Nz([Other Hours]))


By chance, is what you showed me called a Left Join?




KARL DEWEY said:
A sum is a rollup of values in a record set. When you put +([Other Hours])
you are trying to add the value of a single record to the rollup.

You can not do that.

Try this --
Total: (Sum((DateDiff("n",[Start Time],[End Time])/60)*[Number of People]))+
Sum([Other Hours])

--
Build a little, test a little.


Flopbot said:
Hi,

I have an Access 03 query based on “Tbl Vol Hours Trackingâ€. In my query,
one of the fields uses the following to calculate the total number of hours
worked using three columns in the table. It works perfectly.

Total: (Sum((DateDiff("n",[Start Time],[End Time])/60)*[Number of People]))


However, the table also has a field called [Other Hours] logging hours from
a completely different source. I want to add these two types of hours
together for my query’s total. I’ve tried the following but it doesn’t work.

Total: (Sum((DateDiff("n",[Start Time],[End Time])/60)*[Number of
People]))+([Other Hours])


Does anyone have any idea of what my next step should be? I’m stuck.

Thank you.
Click to expand...
Click to expand...
 
By chance, is what you showed me called a Left Join?
Nope. A join is connecting like fields of tables or queries.
Example --
FROM Table1 LEFT JOIN Table2 ON Table1.Field1 = Table2.FieldX

In design view you would have two tables in the open space above the grid.
By clicking on a field in the left table and dragging to a field in the right
table you create an INNER JOIN. If you then click on the connecting line
and then double click it opens another window. When you select the option to
view all records from left table and only those that match from the right
table you create a LEFT JOIN.


--
Build a little, test a little.


Flopbot said:
Thank you Karl. That worked perfectly and it makes a lot of sense the way
you described it. Thank you too for explaining your answers to help me
learn. In the end, I also added the “Nz†function to both sides in order for
it to work correctly.

Final equation:

Total: (Sum(Nz((DateDiff("n",[Start Time],[End Time])/60)*[Number of
People])))+Sum(Nz([Other Hours]))


By chance, is what you showed me called a Left Join?




KARL DEWEY said:
A sum is a rollup of values in a record set. When you put +([Other Hours])
you are trying to add the value of a single record to the rollup.

You can not do that.

Try this --
Total: (Sum((DateDiff("n",[Start Time],[End Time])/60)*[Number of People]))+
Sum([Other Hours])

--
Build a little, test a little.


Flopbot said:
Hi,

I have an Access 03 query based on “Tbl Vol Hours Trackingâ€. In my query,
one of the fields uses the following to calculate the total number of hours
worked using three columns in the table. It works perfectly.

Total: (Sum((DateDiff("n",[Start Time],[End Time])/60)*[Number of People]))


However, the table also has a field called [Other Hours] logging hours from
a completely different source. I want to add these two types of hours
together for my query’s total. I’ve tried the following but it doesn’t work.

Total: (Sum((DateDiff("n",[Start Time],[End Time])/60)*[Number of
People]))+([Other Hours])


Does anyone have any idea of what my next step should be? I’m stuck.

Thank you.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top