On the Total row, select SUM for the Qty field.
Your problem is that you are joining to the Job Cost Log by status. You are going to return one record for each time that status appears in Job Cost Log, no matter what the Job is. Resulting in multiplicate results.
What should be linked then?
I would have to understand more about your data to answer that. With just a cursory glance, you have an awful lot going on in that table on the left side, I'm not sure your data is well normalized. I'm sorry to be so vague, but a database is like a building structure in that if you don't have a solid foundation laid out, nothing will work right.
Why is Status even in both of these tables? Makes no sense to link on these two fields.
Should be linking on Project primary and foreign key identifiers. Is Number the primary key in projmast? Is it saved as the foreign key in JBCSTLOG table PROJECT field?
Don't really even need projmast table in the query except for the NAME field (BTW, name and number are reserved words and should not use reserved words as names, better would be ProjName, ProjNum).
SELECT [Name], PROJECT, TRANSTYPE, STATUS, Sum(Cost) AS SumCost FROM JBCSTLOG INNER JOIN JBCSTLOG.PROJECT ON projmast.Number GROUP BY [Name], PROJECT, TRANSTYPE, STATUS;
Or build a report and use its Sorting & Grouping features with aggregate calcs in group and report footers. This will allow display of detail records as well as summary calcs.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Attachment 29536Attachment 29537
Everything in this standalone Query2 is absolutely correct; it must be a linking issue with the one above
You need to aggregate the multiple entries in a subquery first.
@Preston
How do I do that? If you dont mind me asking
Your attachment links generate 'invalid attachment' error.
In what way is Sum incorrect? I am not seeing summarization by project. There must be a field included that is causing the records to be unique.
If you want to retain detail info in the output did you even consider the suggestion of a report to accomplish the aggregate calcs?
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
It must be a linking issue because once I start adding more fields and links to the Actual query it messes up
Yes, so why are you adding more fields and links?
Preston is suggesting you do a query that aggregates data of the 'many' side of relationship then join that query to other datasource (a table or another query).
To better advise, I would have to examine db to learn its structure, get familiar with data, and run queries.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.