incorrect sums in report using 2 tables rethread

  • Thread starter Thread starter Guest
  • Start date Start date
Why would you place a sample on the web that asks for two variables which
are due to errors in the report. There is no TotalSort field in your
report's record source. It should be SumOfTotalSort.
Your text30 control source should be:
=1

I can't imagine why your primary grouping and sorting would be on a sum from
the record source "SumOfTotalSort". What field do you really want to group
by?

You have Tool Number as a column heading but no field to match.

This code in your form causes a blank msgbox since your code runs right into
the error handling. There should be an Exit prior to the error handling.
Also, I don't think there is any reason to open the report since it is
already being opened.

Private Sub cmdDateSumQuery_Click()
On Error GoTo Err_cmdDateSumQuery_Click

Me.Visible = False

Dim stDocName As String

'stDocName = "rptDateRangeSums"
'DoCmd.OpenReport stDocName, acViewPreview

Err_cmdDateSumQuery_Click:
MsgBox Err.Description
'Resume Exit_cmdDateSumQuery_Click

End Sub


Did you try this query as a starting point for your report:
SELECT Date, [Part Number], SortTime, TotalSort,
NCM_Num, Containment, PlantNum,
Val(Nz((Select Sum(DefQuantity)
FROM tblDefects
WHERE tblDefects.ID = [tbl Defect Count].ID),0)) AS Defects
FROM [TBL defect count];
 
i have posted another snapshot of what the current report looks like on the
website http://mysite.verizon.net/jkendrick75

the snapshot is in a zip file that can be downloaded, located near the top
of the page on the website. also included a screen cap of what the query
looks like in design view and included the sql text. thank you for your help
 
None of the errors I mentioned were corrected (that I noticed) and I don't
see the sql that I suggested in any of the reports. Also, the report looks
nothing like the snapshot on your site.
 
the report is now showing the numbers that we expect and is in a design that
will work for us (Other than showing the tool numbers)... i have been moved
on to other things for now. There are still a few things to work out, such
as showing zeros in the fields when it is a null value, but my supervisor
doesn't consider that important right now. as for the queries, my supervisor
had actually come up with it, basing it on the ID field from both tables
shown below

SELECT [TBL defect count].[Part Number], Sum([TBL defect count].TotalSort)
AS SumOfTotalSort, Sum([Defects per ID #].[Sum Of DefQuantity]) AS [SumOfSum
Of DefQuantity], Sum([Defects per ID #].ID) AS [Defects per ID #_ID]
FROM [TBL defect count] LEFT JOIN [Defects per ID #] ON [TBL defect
count].ID = [Defects per ID #].ID
WHERE ((([TBL defect count].Date) Between
[forms]![frmdaterangesums]![StartDate] And
[forms]![frmdaterangesums]![EndDate]))
GROUP BY [TBL defect count].[Part Number]
ORDER BY Sum([TBL defect count].TotalSort) DESC;

thank you for your help and dedication to the thread... a wonderful holiday
season to you and yours.
 
Back
Top