i know i am missing something...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

not sure what though. i have a report that i am trying to create. i get all
the numbers that i am supposed to except for one. this one number is
supposed to be a sum of a field withing a certain date range, for a part
number. i am getting an answer of 1292 when i should be getting an answer of
1502 (or 1292 + 210). the report is supposed to look like the following:

-Report Header--------------------------------------------------------------
Part Number: [partnumber] (this number is entered on a form)
Start Date: {get from same form} [txtTotalDefects] (hidden)
End Date: {get from same form} [txtTotalSort] (hidden)
-Page Header----------------------------------------------------------------
Defect Code Parts per Defect Code Percent Defect
-Detail------------------------------------------------------------------------
[DefCode] [Sum of DefQuantity] [txtDefectPercentage]
-Page Footer-----------------------------------------------------------------
[=Now()] {="Page " & [Page] & " of " & [Pages]}
-Report Footer--------------------------------------------------------------
Total Parts Sorted {[txtTotalSort]}
Total Defects Sorted {[txtTotalDefects]}

for the sample data that i am using, the report should look like

-Report Header--------------------------------------------------------------
Part Number: 40000 (this number is entered on a form)
Start Date: {1/1/05} [55] (hidden)
End Date: {1/20/05} [1502] (hidden)
-Page Header----------------------------------------------------------------
Defect Code Parts per Defect Code Percent Defect
-Detail------------------------------------------------------------------------
[20] [2] [3.64]
[24] [5] [9.09]
[16] [48] [87.27]
-Page Footer-----------------------------------------------------------------
[=Now()] {="Page " & [Page] & " of " & [Pages]}
-Report Footer--------------------------------------------------------------
Total Parts Sorted {1502}
Total Defects Sorted {55}

what i am getting in hte Total Parts Sorted is 1292 instead of the 1502.

for the sample data, there are 2 instances of the part number during the
date range. once with a total sort of 1292 and 2 different defect codes, and
the second time with a total sort of 210 and 2 different defect codes.
between the two instances, there are 3 unique defect codes.

the sql for my query is:
SELECT [TBL defect count].[Part Number], Sum(qryDefectsPerIDCode.[sum of
defquantity]) AS [Sum Of DefQuantity], Sum([TBL defect count].TotalSort) AS
SumOfTotalSort, qryDefectsPerIDCode.DefCode
FROM [TBL defect count] INNER JOIN qryDefectsPerIDCode ON [TBL defect
count].ID = qryDefectsPerIDCode.ID
WHERE ((([TBL defect count].Date) Between
[forms]![frmpartbydaterange]![txtStartDate] And
[forms]![frmpartbydaterange]![txtEndDate]))
GROUP BY [TBL defect count].[Part Number], qryDefectsPerIDCode.DefCode
HAVING ((([TBL defect count].[Part
Number])=[forms]![frmPartbydaterange]![cboPartNum].[value]));

and for my sub query:
SELECT DISTINCTROW tblDefects.ID, Sum(tblDefects.DefQuantity) AS [sum of
defquantity], tblDefects.DefCode
FROM tblDefects
GROUP BY tblDefects.ID, tblDefects.DefCode;

thanks in advance and hope somebody will be able to show me what i'm missing.
 
Is the "txttotalSort" to which you refer in the Footer a Field in the
RecordSource of the Report or are you trying to Refer to a Control on the
Report. If a Field in the RecordSource, should you not be using
Sum([txtTotalSort]), instead? If the latter, you can't sum over a particular
Control in that manner.

Larry Linson
Microsoft Access MVP

jkendrick75 said:
not sure what though. i have a report that i am trying to create. i get all
the numbers that i am supposed to except for one. this one number is
supposed to be a sum of a field withing a certain date range, for a part
number. i am getting an answer of 1292 when i should be getting an answer of
1502 (or 1292 + 210). the report is supposed to look like the following:

-Report Header--------------------------------------------------------------
Part Number: [partnumber] (this number is entered on a form)
Start Date: {get from same form} [txtTotalDefects] (hidden)
End Date: {get from same form} [txtTotalSort] (hidden)
-Page Header----------------------------------------------------------------
Defect Code Parts per Defect Code Percent Defect
-Detail------------------------------------------------------------------- -----
[DefCode] [Sum of DefQuantity] [txtDefectPercentage]
-Page Footer-----------------------------------------------------------------
[=Now()] {="Page " & [Page] & " of " & [Pages]}
-Report Footer--------------------------------------------------------------
Total Parts Sorted {[txtTotalSort]}
Total Defects Sorted {[txtTotalDefects]}

for the sample data that i am using, the report should look like

-Report Header--------------------------------------------------------------
Part Number: 40000 (this number is entered on a form)
Start Date: {1/1/05} [55] (hidden)
End Date: {1/20/05} [1502] (hidden)
-Page Header----------------------------------------------------------------
Defect Code Parts per Defect Code Percent Defect
-Detail------------------------------------------------------------------- -----
[20] [2] [3.64]
[24] [5] [9.09]
[16] [48] [87.27]
-Page Footer-----------------------------------------------------------------
[=Now()] {="Page " & [Page] & " of " & [Pages]}
-Report Footer--------------------------------------------------------------
Total Parts Sorted {1502}
Total Defects Sorted {55}

what i am getting in hte Total Parts Sorted is 1292 instead of the 1502.

for the sample data, there are 2 instances of the part number during the
date range. once with a total sort of 1292 and 2 different defect codes, and
the second time with a total sort of 210 and 2 different defect codes.
between the two instances, there are 3 unique defect codes.

the sql for my query is:
SELECT [TBL defect count].[Part Number], Sum(qryDefectsPerIDCode.[sum of
defquantity]) AS [Sum Of DefQuantity], Sum([TBL defect count].TotalSort) AS
SumOfTotalSort, qryDefectsPerIDCode.DefCode
FROM [TBL defect count] INNER JOIN qryDefectsPerIDCode ON [TBL defect
count].ID = qryDefectsPerIDCode.ID
WHERE ((([TBL defect count].Date) Between
[forms]![frmpartbydaterange]![txtStartDate] And
[forms]![frmpartbydaterange]![txtEndDate]))
GROUP BY [TBL defect count].[Part Number], qryDefectsPerIDCode.DefCode
HAVING ((([TBL defect count].[Part
Number])=[forms]![frmPartbydaterange]![cboPartNum].[value]));

and for my sub query:
SELECT DISTINCTROW tblDefects.ID, Sum(tblDefects.DefQuantity) AS [sum of
defquantity], tblDefects.DefCode
FROM tblDefects
GROUP BY tblDefects.ID, tblDefects.DefCode;

thanks in advance and hope somebody will be able to show me what i'm
Click to expand...
missing.
 
the [txttotalsort] in the report footer is referencing the [txttotalsort] in
the report header. this is the same as the [txttotaldefects] in both the
header and the footer, however i am getting the correct numbers for the
txttotaldefects and not the txttotalsort. for what ever reason, this
summation is not including a record from the query, where there is no defects
for the record. in other words, it is only summing the records for total
sort if they have a defect also.

Larry Linson said:
Is the "txttotalSort" to which you refer in the Footer a Field in the
RecordSource of the Report or are you trying to Refer to a Control on the
Report. If a Field in the RecordSource, should you not be using
Sum([txtTotalSort]), instead? If the latter, you can't sum over a particular
Control in that manner.

Larry Linson
Microsoft Access MVP

jkendrick75 said:
not sure what though. i have a report that i am trying to create. i get all
the numbers that i am supposed to except for one. this one number is
supposed to be a sum of a field withing a certain date range, for a part
number. i am getting an answer of 1292 when i should be getting an answer of
1502 (or 1292 + 210). the report is supposed to look like the following:

-Report Header--------------------------------------------------------------
Part Number: [partnumber] (this number is entered on a form)
Start Date: {get from same form} [txtTotalDefects] (hidden)
End Date: {get from same form} [txtTotalSort] (hidden)
-Page Header----------------------------------------------------------------
Defect Code Parts per Defect Code Percent Defect
-Detail------------------------------------------------------------------- -----
[DefCode] [Sum of DefQuantity] [txtDefectPercentage]
-Page Footer-----------------------------------------------------------------
[=Now()] {="Page " & [Page] & " of " & [Pages]}
-Report Footer--------------------------------------------------------------
Total Parts Sorted {[txtTotalSort]}
Total Defects Sorted {[txtTotalDefects]}

for the sample data that i am using, the report should look like

-Report Header--------------------------------------------------------------
Part Number: 40000 (this number is entered on a form)
Start Date: {1/1/05} [55] (hidden)
End Date: {1/20/05} [1502] (hidden)
-Page Header----------------------------------------------------------------
Defect Code Parts per Defect Code Percent Defect
-Detail------------------------------------------------------------------- -----
[20] [2] [3.64]
[24] [5] [9.09]
[16] [48] [87.27]
-Page Footer-----------------------------------------------------------------
[=Now()] {="Page " & [Page] & " of " & [Pages]}
-Report Footer--------------------------------------------------------------
Total Parts Sorted {1502}
Total Defects Sorted {55}

what i am getting in hte Total Parts Sorted is 1292 instead of the 1502.

for the sample data, there are 2 instances of the part number during the
date range. once with a total sort of 1292 and 2 different defect codes, and
the second time with a total sort of 210 and 2 different defect codes.
between the two instances, there are 3 unique defect codes.

the sql for my query is:
SELECT [TBL defect count].[Part Number], Sum(qryDefectsPerIDCode.[sum of
defquantity]) AS [Sum Of DefQuantity], Sum([TBL defect count].TotalSort) AS
SumOfTotalSort, qryDefectsPerIDCode.DefCode
FROM [TBL defect count] INNER JOIN qryDefectsPerIDCode ON [TBL defect
count].ID = qryDefectsPerIDCode.ID
WHERE ((([TBL defect count].Date) Between
[forms]![frmpartbydaterange]![txtStartDate] And
[forms]![frmpartbydaterange]![txtEndDate]))
GROUP BY [TBL defect count].[Part Number], qryDefectsPerIDCode.DefCode
HAVING ((([TBL defect count].[Part
Number])=[forms]![frmPartbydaterange]![cboPartNum].[value]));

and for my sub query:
SELECT DISTINCTROW tblDefects.ID, Sum(tblDefects.DefQuantity) AS [sum of
defquantity], tblDefects.DefCode
FROM tblDefects
GROUP BY tblDefects.ID, tblDefects.DefCode;

thanks in advance and hope somebody will be able to show me what i'm
Click to expand...
missing.
Click to expand...
 
Back
Top