Sum Query

[David M C]

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

 

[Michel Walsh]

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