Group By Query Summary Problem

[OrWolf]

For this issue I wasn't even sure what to search for to look for a previously posted answer. I'm doing a select query with a group by to summarize some values. The group by is by job and then billable and not billable items. Everything was working fine till I realized that for some jobs there is no billable items however I need to include a job fee. Before I discovered the problem I mutliply the job fee by the billable flag so it was either zero if not billable or the job fee for the billable items group. Any suggestions on how I can get a job fee value for jobs with no billable items? Remember that job fee is in the Job table and the items are from the linked JobItems table. The table below is how the final data should appear. My problem is that either sample job 124 doesn't list the job fee or sample job 123 and 126 list the job fee twice. Clear as mud?


Job Billable Job Fee Items Item Total Total
123 1 50 5 25 75
123 0 0 3 0 0
124 0 50 2 0 50
125 1 75 6 75 150
125 0 0 4 0 0

 

[Pattycake245]

Show some rows from each table to help come up with a proper solution. And perhaps show the query you have so far.

Tim

 

[jaspaul]

Understood the problem 70%, but without seeing the actual data, can't help you much.
As Pattycake245 said, post relevant data from the tables - "Job" & "JobItems".
& If you post your query, it would be even better.

Jas.