I have a query that takes all counts for an ID, then totals them together. The problem I cant seem to fix, is when a job is copied to create a revision, the query adds that in as well. So the base/original jobs counts keep going up every time the job is duplicated. I tried doing [forms]![jobquote]![jobid] to segregate it, but alas, that doesnt work.
The problem lies in that the ID is being reused and count maintained, but its assigned a new JobID and it wont seem to account for that. So even though I am trying to tell it to only count for this jobid, it is summing all counts for a type ID. regardless of the JobID.
Below is my SQL statement for the query.
The problem lies in that the ID is being reused and count maintained, but its assigned a new JobID and it wont seem to account for that. So even though I am trying to tell it to only count for this jobid, it is summing all counts for a type ID. regardless of the JobID.
Below is my SQL statement for the query.
Code:
SELECT Sum(tblDrawingFixtureType.Quantity) AS SumOfQuantity, tblFixtureTypes.TypeName, tblJobDetails.JobName, tblFixtureTypes.Notes
FROM tblFixtureTypes INNER JOIN ((tblJobDetails INNER JOIN tblDrawings ON tblJobDetails.JobID = tblDrawings.JobID) INNER JOIN tblDrawingFixtureType ON tblDrawings.DrawingID = tblDrawingFixtureType.DrawingID) ON (tblJobDetails.JobID = tblFixtureTypes.JobID) AND (tblFixtureTypes.TypeID = tblDrawingFixtureType.TypeID)
WHERE (((tblFixtureTypes.JobID)=[Forms]![JobQuote]![JobID]))
GROUP BY tblFixtureTypes.TypeName, tblJobDetails.JobName, tblFixtureTypes.Notes, tblDrawingFixtureType.TypeID
ORDER BY tblFixtureTypes.TypeName;