Problem creating JOIN query

[mykeld]

i have joined a jobs table and payout table together and performed a query.
however i would like to perform SUM by the job_id but crew_id fields are duplicated.

(the payout table comes from a subform that doesnt differentiate crew type so my SUM totals all the payouts whereas i want to sum the payouts by job_id when the crew id are repeated.

TOTAL JOB_ID CREW_ID
$5000 2 2
$3290 2 2
$1000 2 7
$2500 2 7

need to return $8290 for one query (5000+3290)
then $3500 from another (1000+2500)

 

[Pat Hartman]

You need to remove CREW_ID from the query. It is preventing aggregating to the Job level.

 

[mykeld]

here's the actual query

but wont that just return the job_id. in my example job_id 2. i want to sum the rows where job_id=2 and crew_id=2 , then job_id=2 and crew_id=7

SELECT *
FROM [SELECT jobs.id, jobs.name, jobs.size, jobs.roof_crew_rate, jobs.size*jobs.roof_crew_rate AS roof_pays, SUM(crewpayouts.amount) AS payouts
FROM crewpayouts INNER JOIN jobs ON crewpayouts.job_id = jobs.id
GROUP BY jobs.id, jobs.name, jobs.size, jobs.roof_crew_rate, jobs.size*jobs.roof_crew_rate]. AS [%$##@_Alias5]
WHERE ((([%$##@_Alias5].roof_pays)>[payouts]));

the query returns the total SUM of crew payouts for the job because i use the same subform crewPayouts to collect the info from my job form. can't i reference the payout by crew_id some how. (like if crew_id = crew.id then return the sum of the corresponding payout form the crewPayouts table ?)