Summing Fields per record

[alex]

My query contains the following fields:
InvoiceID HoursWorked HourlyRate HourlyAmount

HourlyAmount is an entry in the query to perform a
caculation:
HourlyAmount:[HoursWorked]*[HourlyRate], which returns
the calculated value correctly in the form built from
this query.

There are sometimes several entries for hoursworked,
hourlyrate and hourlyamount per record [InvoiceID], which
is listed in the form I use to enter and view data.

What I need to do is sum the HourlyAmount per each unique
record containing the unique InvoiceID's. I have searched
for a way, but nothing is working. I have joined
ElementK online to learn more about Access, but could
not find an answer to my summing issue. Can someone
suggest a formula to perform this summing of fields per
record? Thanks

 

[Michel Walsh]

Hi,


SELECT InvoiceID, SUM( HoursWorked * HourlyRate)
FROM myTable
GROUP BY InvoiceID



should do. You can aggregate, SUM, on an expression, HoursWorked *
HourlyRate.



Hoping it may help,
Vanderghast, Access MVP

 

[Alexparks]

Thanks for the help. It worked!

-----Original Message-----
Hi,


SELECT InvoiceID, SUM( HoursWorked * HourlyRate)
FROM myTable
GROUP BY InvoiceID



should do. You can aggregate, SUM, on an expression, HoursWorked *
HourlyRate.



Hoping it may help,
Vanderghast, Access MVP

My query contains the following fields:
InvoiceID HoursWorked HourlyRate HourlyAmount

HourlyAmount is an entry in the query to perform a
caculation:
HourlyAmount:[HoursWorked]*[HourlyRate], which returns
the calculated value correctly in the form built from
this query.

There are sometimes several entries for hoursworked,
hourlyrate and hourlyamount per record [InvoiceID], which
is listed in the form I use to enter and view data.

What I need to do is sum the HourlyAmount per each unique
record containing the unique InvoiceID's. I have searched
for a way, but nothing is working. I have joined
ElementK online to learn more about Access, but could
not find an answer to my summing issue. Can someone
suggest a formula to perform this summing of fields per
record? Thanks

.

 

[Gary Walter]

"alex" > My query contains the following fields:

InvoiceID HoursWorked HourlyRate HourlyAmount

HourlyAmount is an entry in the query to perform a
caculation:
HourlyAmount:[HoursWorked]*[HourlyRate], which returns
the calculated value correctly in the form built from
this query.

There are sometimes several entries for hoursworked,
hourlyrate and hourlyamount per record [InvoiceID], which
is listed in the form I use to enter and view data.

What I need to do is sum the HourlyAmount per each unique
record containing the unique InvoiceID's. I have searched
for a way, but nothing is working. I have joined
ElementK online to learn more about Access, but could
not find an answer to my summing issue. Can someone
suggest a formula to perform this summing of fields per
record? Thanks

Hi Alex,

If I understand correctly...

In query designer for your query,
in an empty column, type the following
in the Field row (all on one line, replace
yourtable with actual name of table):

TotalAmt: DSum("[HoursWorked]*[HourlyRate]","yourtable","[InvoiceID]=" & InvoiceID)

or

TotalAmt: (SELECT SUM([HoursWorked]*[HourlyRate]) FROM yourtable As t
WHERE t.InvoiceID = yourtable.InvoiceID)

 

[Gary Walter]

sorry..my link to ng must be slow..
if had known was answered, I would
not have responded.

"alex" > My query contains the following fields:

InvoiceID HoursWorked HourlyRate HourlyAmount

HourlyAmount is an entry in the query to perform a
caculation:
HourlyAmount:[HoursWorked]*[HourlyRate], which returns
the calculated value correctly in the form built from
this query.

There are sometimes several entries for hoursworked,
hourlyrate and hourlyamount per record [InvoiceID], which
is listed in the form I use to enter and view data.

What I need to do is sum the HourlyAmount per each unique
record containing the unique InvoiceID's. I have searched
for a way, but nothing is working. I have joined
ElementK online to learn more about Access, but could
not find an answer to my summing issue. Can someone
suggest a formula to perform this summing of fields per
record? Thanks

Hi Alex,

If I understand correctly...

In query designer for your query,
in an empty column, type the following
in the Field row (all on one line, replace
yourtable with actual name of table):

TotalAmt: DSum("[HoursWorked]*[HourlyRate]","yourtable","[InvoiceID]=" & InvoiceID)

or

TotalAmt: (SELECT SUM([HoursWorked]*[HourlyRate]) FROM yourtable As t
WHERE t.InvoiceID = yourtable.InvoiceID)

 

[Michel Walsh]

Hi,

That happens often to me, I load the articles, and on idle time, I look
at messages, but in the interval, an answer may have been supplied, without
my download being aware of it... ;-) I just hope no one is ever offended if
that someone see a reply from me to a question that he/she has already
answered!


Vanderghast, Access MVP

sorry..my link to ng must be slow..
if had known was answered, I would
not have responded.

"alex" > My query contains the following fields:

InvoiceID HoursWorked HourlyRate HourlyAmount

HourlyAmount is an entry in the query to perform a
caculation:
HourlyAmount:[HoursWorked]*[HourlyRate], which returns
the calculated value correctly in the form built from
this query.

There are sometimes several entries for hoursworked,
hourlyrate and hourlyamount per record [InvoiceID], which
is listed in the form I use to enter and view data.

What I need to do is sum the HourlyAmount per each unique
record containing the unique InvoiceID's. I have searched
for a way, but nothing is working. I have joined
ElementK online to learn more about Access, but could
not find an answer to my summing issue. Can someone
suggest a formula to perform this summing of fields per
record? Thanks

Hi Alex,

If I understand correctly...

In query designer for your query,
in an empty column, type the following
in the Field row (all on one line, replace
yourtable with actual name of table):

TotalAmt: DSum("[HoursWorked]*[HourlyRate]","yourtable","[InvoiceID]=" & InvoiceID)

or

TotalAmt: (SELECT SUM([HoursWorked]*[HourlyRate]) FROM yourtable As t
WHERE t.InvoiceID = yourtable.InvoiceID)