report not including records

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

Guest

the report that i am having trouble with is based on a query that works off
of a table and another query. the first table, TBL defect count, has a 1
to many relationship with the second table, tblDefects. the child query adds
together several records from tblDefects. for some reason that i cannot
figure out, if there is no corresponding record in tblDefects, then the
record in TBL defect count is not being used in the report. i think i have a
work around but the workaround is going to be alot of work as it will require
me to add a corresponding record to tblDefects with zero values for the two
input fields and there are about 3 thousand records in
TBL defect count, with about 1800 of those records that do not have a
corresponding record in tblDefects.

any help is appreciated and welcome.
 
Does the query that the report is based on retrieve the correct data? If so,
the code for the query may be helpful to see...
 
the code for the query (qryPartbyDateRange) that the the report works off of
is :

SELECT Sum([TBL defect count].SortTime) AS SumOfSortTime, Sum([TBL defect
count].TotalSort) AS SumOfTotalSort, qryDefectCount.DefCode,
Sum(qryDefectCount.DefQuantity) AS SumOfDefQuantity, [TBL defect count].[Part
Number]
FROM [TBL defect count] LEFT JOIN qryDefectCount ON [TBL defect count].ID =
qryDefectCount.ID
WHERE ((([TBL defect count].[Part
Number])=[forms]![frmPartbyDateRange]![cboPartNum].[value]) AND (([TBL defect
count].Date) Between [forms]![frmpartbydaterange]![txtStartDate] And
[forms]![frmpartbydaterange]![txtEndDate]))
GROUP BY qryDefectCount.DefCode, [TBL defect count].[Part Number]
HAVING ((([TBL defect count].[Part
Number])=[forms]![frmPartbyDateRange]![cboPartNum].[value]));

the code for the query (qryDefectCount) that the above query works off of is:

SELECT [ID], [DefCode], [DefQuantity]
FROM tblDefects;

when going through the numbers, i find that the records from TBL defect
count are not being added if there is no corresponding ID in the table
tblDefects.
the following is the defectcodes and quantities per code (for the given date
range).

DefCode DefQty
26 19
24 9
25 1
7 4
15 5
20 16
[54](total defects)

the total parts sorted should be 12,645 but i am getting 9,345, meanwhile
the total defects is correct.
 
Back
Top