Sum Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

David M C - 12 Jul 2006 11:26 GMT

Here's my table structure:

tblGroup:

JobNumber
GroupID (pk)
GroupName

tblLocation:

GroupID (fk)
LocationID (pk)
LocationName

tblTask:

LocationID (fk)
TaskID (pk)
TaskName
Price

tblTransaction:

TaskID (fk)
TransactionID (pk)
AuthorisedPayment
...

Relationships:

tblGroup 1:m tblLocation 1:m tblTask 1:m tblTransaction

In my query I would like to show:

JobNumber
GroupName
LocationName
TaskName
Price
SumOfAuthorisedPayment

where SumOfAuthorisedPayment is a sum of all the transactions on a given
task. How would I do this without showing multiple instances of the same task?

Thanks,

Dave

Reply to this Message

Michel Walsh - 12 Jul 2006 23:29 GMT

Hi,

Make a total query? Click on the summation button, in the query editor, to
get the extra line: total. Bring the tables you need, make the joins, then,
bring the different fields in the grid, keeping the proposed GROUP BY,
except for the AuthorisedPayment field for which you will use SUM.

Alternatively, you can use MIN, MAX, FIRST, or LAST instead of GROUP BY, if
the primary key associate to that field is already involved in the GROUP BY.
That may make the query runs faster.

Hoping it may help,
Vanderghast, Access MVP

> Here's my table structure:
>
[quoted text clipped - 44 lines]
>
> Dave

Reply to this Message