Issue with sum query after copying records (1 Viewer)

tmyers

Active member
Local time
Today, 07:10
Joined
Sep 8, 2020
Messages
687
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.
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;
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 12:10
Joined
Jul 9, 2003
Messages
12,474
It appears that your post slipped through the net. It's seems likely to me that it hasn't been seen by the right people, hence I'm bumping it up the list for a second look...
 

tmyers

Active member
Local time
Today, 07:10
Joined
Sep 8, 2020
Messages
687
I had actually completely forgotten I had posted it myself!
 

Users who are viewing this thread

Top Bottom