If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
incorrect sums in report using 2 tables
i am trying to create a report that, using a start and end date that is given
on a form, groups by part number and sums the number of parts sorted per part for the given date range. this part works fine as the date, part number, and the values for the total sorted are all in one table (tblDefect Count). if i try to get the total number of defects per part for the given date range, i get the correct number of defects but an incorrect number for the total sorted. the number of defects comes from a second table (tblDefects), that is tied to tblDefect Count by using an autogenerated key field called ID in tblDefect Count. for every ID in tblDefect Count, there could be any where from 0 to 27 records in tblDefects (there are 27 different defect codes and each code has its own amount for defects for that code and part number.) any help with this would be greatly appreciated as i have been working on this for about a month now. if you need any thing clarified, please let me know and i will do what i can. if you want to see a copy of the database, let me know. |
#2
|
|||
|
|||
There are a couple methods for doing this. One is to create a text box in
the PartNumber(?) group header: Name: txtTotalSorted Control Source: [Total Sorted] Running Sum: Over All Visible: No Then, add a text box to your report footer Control Source: =txtTotalSorted You could also set up a totals query based on [tblDefect Count] that returns only one record with the sum of [Total Sorted] for the particular date range. You could then add this query to your report's record source. -- Duane Hookom MS Access MVP -- "jkendrick75" wrote in message ... i am trying to create a report that, using a start and end date that is given on a form, groups by part number and sums the number of parts sorted per part for the given date range. this part works fine as the date, part number, and the values for the total sorted are all in one table (tblDefect Count). if i try to get the total number of defects per part for the given date range, i get the correct number of defects but an incorrect number for the total sorted. the number of defects comes from a second table (tblDefects), that is tied to tblDefect Count by using an autogenerated key field called ID in tblDefect Count. for every ID in tblDefect Count, there could be any where from 0 to 27 records in tblDefects (there are 27 different defect codes and each code has its own amount for defects for that code and part number.) any help with this would be greatly appreciated as i have been working on this for about a month now. if you need any thing clarified, please let me know and i will do what i can. if you want to see a copy of the database, let me know. |
#3
|
|||
|
|||
that part works fine, just when i try to add in the total defects for that
part during the date range, then the total sorted number is more than what it should be. what is happening is say for part 123, there were 100 parts sorted (during a given date range). for that 100 parts sorted, there were 3 different defect codes (a with 2 defects, b with 3 defects, c with 4 defects). the total sorted should be 100 parts sorted (which i get if i don't try to total the defects) with a total of 9 defects. what i am getting is a total of 300 parts sorted and 9 defects. i have tried to write a query for just the total sorted, then a query for just the total defects, and then a query that uses the first 2 queries, but all that did was total all parts sorted (grouped by the part number) in the entire database, along with all defects for entire database. it was ignoring the date range and giving totals for all parts, and then just did a one time total of all defects, and assigned it to each part number. thank you for responding so quickly, and any other ideas will be welcome. "Duane Hookom" wrote: There are a couple methods for doing this. One is to create a text box in the PartNumber(?) group header: Name: txtTotalSorted Control Source: [Total Sorted] Running Sum: Over All Visible: No Then, add a text box to your report footer Control Source: =txtTotalSorted You could also set up a totals query based on [tblDefect Count] that returns only one record with the sum of [Total Sorted] for the particular date range. You could then add this query to your report's record source. -- Duane Hookom MS Access MVP -- "jkendrick75" wrote in message ... i am trying to create a report that, using a start and end date that is given on a form, groups by part number and sums the number of parts sorted per part for the given date range. this part works fine as the date, part number, and the values for the total sorted are all in one table (tblDefect Count). if i try to get the total number of defects per part for the given date range, i get the correct number of defects but an incorrect number for the total sorted. the number of defects comes from a second table (tblDefects), that is tied to tblDefect Count by using an autogenerated key field called ID in tblDefect Count. for every ID in tblDefect Count, there could be any where from 0 to 27 records in tblDefects (there are 27 different defect codes and each code has its own amount for defects for that code and part number.) any help with this would be greatly appreciated as i have been working on this for about a month now. if you need any thing clarified, please let me know and i will do what i can. if you want to see a copy of the database, let me know. |
#4
|
|||
|
|||
How about showing us what is happening and what you want with something like
this: =Part Header============ Part Number:ABC Total Sorted: 5 -Defects (detail)------- Too Big Too Small =Part Header============ Part NumberEF Total Sorted: 10 -Defects (detail)------- Too Big Too Small Too Flat Too Fat #Report Footer############ Total Sorted 15 -Is this your issue? -- Duane Hookom MS Access MVP -- "jkendrick75" wrote in message ... that part works fine, just when i try to add in the total defects for that part during the date range, then the total sorted number is more than what it should be. what is happening is say for part 123, there were 100 parts sorted (during a given date range). for that 100 parts sorted, there were 3 different defect codes (a with 2 defects, b with 3 defects, c with 4 defects). the total sorted should be 100 parts sorted (which i get if i don't try to total the defects) with a total of 9 defects. what i am getting is a total of 300 parts sorted and 9 defects. i have tried to write a query for just the total sorted, then a query for just the total defects, and then a query that uses the first 2 queries, but all that did was total all parts sorted (grouped by the part number) in the entire database, along with all defects for entire database. it was ignoring the date range and giving totals for all parts, and then just did a one time total of all defects, and assigned it to each part number. thank you for responding so quickly, and any other ideas will be welcome. "Duane Hookom" wrote: There are a couple methods for doing this. One is to create a text box in the PartNumber(?) group header: Name: txtTotalSorted Control Source: [Total Sorted] Running Sum: Over All Visible: No Then, add a text box to your report footer Control Source: =txtTotalSorted You could also set up a totals query based on [tblDefect Count] that returns only one record with the sum of [Total Sorted] for the particular date range. You could then add this query to your report's record source. -- Duane Hookom MS Access MVP -- "jkendrick75" wrote in message ... i am trying to create a report that, using a start and end date that is given on a form, groups by part number and sums the number of parts sorted per part for the given date range. this part works fine as the date, part number, and the values for the total sorted are all in one table (tblDefect Count). if i try to get the total number of defects per part for the given date range, i get the correct number of defects but an incorrect number for the total sorted. the number of defects comes from a second table (tblDefects), that is tied to tblDefect Count by using an autogenerated key field called ID in tblDefect Count. for every ID in tblDefect Count, there could be any where from 0 to 27 records in tblDefects (there are 27 different defect codes and each code has its own amount for defects for that code and part number.) any help with this would be greatly appreciated as i have been working on this for about a month now. if you need any thing clarified, please let me know and i will do what i can. if you want to see a copy of the database, let me know. |
#5
|
|||
|
|||
ok, i'll try.
currently my report kinda looks like this page header: Part Number Quantity Inspected Defect Quantity Detail: [PartNumber] [SumOfTotalSort] [SumofDefects] page footer: ="Page " & [Page] & " & [Pages] report footer: Total Sorted: [=sum([sumoftotalsort]) Total Defects: [=Sum([sumofDefects])] In the Detail section, it will show all part numbers that are in the database within the selected date range, listed as separate rows, and each part number's total sorted, as long as that is all i am asking for in the query. if i try to include the total defects, then the total sorted is inflated. such as part number sorted defects 1 100 a(1), b(3), c(2) 2 200 3 50 a(2) 4 300 b(5), c(5) 1 300 b(2), c(5) 2 200 a(4), b(6) 3 400 c(10) my total sorted should be 1550 and total defects should be 45. what i am getting when i try to include the total defects in my query is total sorted 2550 and a total defects of 45. what it is doing is for each defect (as in part number 1 has 3 different defects the first time and then 2 defects the second time) it is adding the total sorted (for that instance) to itself for each different defect. the query that i am using is as follows SELECT [TBL defect count].[Part Number], Sum(tblDefects.DefQuantity) AS sumofdefquantity FROM [TBL defect count] LEFT JOIN tblDefects ON [TBL defect count].ID = tblDefects.ID WHERE ((([TBL defect count].Date) Between [forms]![frmDateRangeSums]![StartDate] And [forms]![frmDateRangeSums]![EndDate])) GROUP BY [TBL defect count].[Part Number]; the frmDateRangeSums is the form that i use to ask for the start date and end date of the date range. "Duane Hookom" wrote: How about showing us what is happening and what you want with something like this: =Part Header============ Part Number:ABC Total Sorted: 5 -Defects (detail)------- Too Big Too Small =Part Header============ Part NumberEF Total Sorted: 10 -Defects (detail)------- Too Big Too Small Too Flat Too Fat #Report Footer############ Total Sorted 15 -Is this your issue? -- Duane Hookom MS Access MVP -- "jkendrick75" wrote in message ... that part works fine, just when i try to add in the total defects for that part during the date range, then the total sorted number is more than what it should be. what is happening is say for part 123, there were 100 parts sorted (during a given date range). for that 100 parts sorted, there were 3 different defect codes (a with 2 defects, b with 3 defects, c with 4 defects). the total sorted should be 100 parts sorted (which i get if i don't try to total the defects) with a total of 9 defects. what i am getting is a total of 300 parts sorted and 9 defects. i have tried to write a query for just the total sorted, then a query for just the total defects, and then a query that uses the first 2 queries, but all that did was total all parts sorted (grouped by the part number) in the entire database, along with all defects for entire database. it was ignoring the date range and giving totals for all parts, and then just did a one time total of all defects, and assigned it to each part number. thank you for responding so quickly, and any other ideas will be welcome. "Duane Hookom" wrote: There are a couple methods for doing this. One is to create a text box in the PartNumber(?) group header: Name: txtTotalSorted Control Source: [Total Sorted] Running Sum: Over All Visible: No Then, add a text box to your report footer Control Source: =txtTotalSorted You could also set up a totals query based on [tblDefect Count] that returns only one record with the sum of [Total Sorted] for the particular date range. You could then add this query to your report's record source. -- Duane Hookom MS Access MVP -- "jkendrick75" wrote in message ... i am trying to create a report that, using a start and end date that is given on a form, groups by part number and sums the number of parts sorted per part for the given date range. this part works fine as the date, part number, and the values for the total sorted are all in one table (tblDefect Count). if i try to get the total number of defects per part for the given date range, i get the correct number of defects but an incorrect number for the total sorted. the number of defects comes from a second table (tblDefects), that is tied to tblDefect Count by using an autogenerated key field called ID in tblDefect Count. for every ID in tblDefect Count, there could be any where from 0 to 27 records in tblDefects (there are 27 different defect codes and each code has its own amount for defects for that code and part number.) any help with this would be greatly appreciated as i have been working on this for about a month now. if you need any thing clarified, please let me know and i will do what i can. if you want to see a copy of the database, let me know. |
#6
|
|||
|
|||
Do your defects actually print across the page like:
a(1), b(3), c(2) Why do you have Part Numbers repeated? Is there some column/field you are providing? Did you try creating a running sum on the SumOfTotalSort? -- Duane Hookom MS Access MVP -- "jkendrick75" wrote in message ... ok, i'll try. currently my report kinda looks like this page header: Part Number Quantity Inspected Defect Quantity Detail: [PartNumber] [SumOfTotalSort] [SumofDefects] page footer: ="Page " & [Page] & " & [Pages] report footer: Total Sorted: [=sum([sumoftotalsort]) Total Defects: [=Sum([sumofDefects])] In the Detail section, it will show all part numbers that are in the database within the selected date range, listed as separate rows, and each part number's total sorted, as long as that is all i am asking for in the query. if i try to include the total defects, then the total sorted is inflated. such as part number sorted defects 1 100 a(1), b(3), c(2) 2 200 3 50 a(2) 4 300 b(5), c(5) 1 300 b(2), c(5) 2 200 a(4), b(6) 3 400 c(10) my total sorted should be 1550 and total defects should be 45. what i am getting when i try to include the total defects in my query is total sorted 2550 and a total defects of 45. what it is doing is for each defect (as in part number 1 has 3 different defects the first time and then 2 defects the second time) it is adding the total sorted (for that instance) to itself for each different defect. the query that i am using is as follows SELECT [TBL defect count].[Part Number], Sum(tblDefects.DefQuantity) AS sumofdefquantity FROM [TBL defect count] LEFT JOIN tblDefects ON [TBL defect count].ID = tblDefects.ID WHERE ((([TBL defect count].Date) Between [forms]![frmDateRangeSums]![StartDate] And [forms]![frmDateRangeSums]![EndDate])) GROUP BY [TBL defect count].[Part Number]; the frmDateRangeSums is the form that i use to ask for the start date and end date of the date range. "Duane Hookom" wrote: How about showing us what is happening and what you want with something like this: =Part Header============ Part Number:ABC Total Sorted: 5 -Defects (detail)------- Too Big Too Small =Part Header============ Part NumberEF Total Sorted: 10 -Defects (detail)------- Too Big Too Small Too Flat Too Fat #Report Footer############ Total Sorted 15 -Is this your issue? -- Duane Hookom MS Access MVP -- "jkendrick75" wrote in message ... that part works fine, just when i try to add in the total defects for that part during the date range, then the total sorted number is more than what it should be. what is happening is say for part 123, there were 100 parts sorted (during a given date range). for that 100 parts sorted, there were 3 different defect codes (a with 2 defects, b with 3 defects, c with 4 defects). the total sorted should be 100 parts sorted (which i get if i don't try to total the defects) with a total of 9 defects. what i am getting is a total of 300 parts sorted and 9 defects. i have tried to write a query for just the total sorted, then a query for just the total defects, and then a query that uses the first 2 queries, but all that did was total all parts sorted (grouped by the part number) in the entire database, along with all defects for entire database. it was ignoring the date range and giving totals for all parts, and then just did a one time total of all defects, and assigned it to each part number. thank you for responding so quickly, and any other ideas will be welcome. "Duane Hookom" wrote: There are a couple methods for doing this. One is to create a text box in the PartNumber(?) group header: Name: txtTotalSorted Control Source: [Total Sorted] Running Sum: Over All Visible: No Then, add a text box to your report footer Control Source: =txtTotalSorted You could also set up a totals query based on [tblDefect Count] that returns only one record with the sum of [Total Sorted] for the particular date range. You could then add this query to your report's record source. -- Duane Hookom MS Access MVP -- "jkendrick75" wrote in message ... i am trying to create a report that, using a start and end date that is given on a form, groups by part number and sums the number of parts sorted per part for the given date range. this part works fine as the date, part number, and the values for the total sorted are all in one table (tblDefect Count). if i try to get the total number of defects per part for the given date range, i get the correct number of defects but an incorrect number for the total sorted. the number of defects comes from a second table (tblDefects), that is tied to tblDefect Count by using an autogenerated key field called ID in tblDefect Count. for every ID in tblDefect Count, there could be any where from 0 to 27 records in tblDefects (there are 27 different defect codes and each code has its own amount for defects for that code and part number.) any help with this would be greatly appreciated as i have been working on this for about a month now. if you need any thing clarified, please let me know and i will do what i can. if you want to see a copy of the database, let me know. |
#7
|
|||
|
|||
what i was doing with showing the defects like that and having the part
numbers repeat was to show an example of the data for a date range. say over the course of a week, the same part may be inspected each day, but have a different quantity of parts inspected each day. each day may have a different set of defects and defect quantities. in my example, it was defect code 'a' with a defect quantity of '1', code 'b' with a defect quantity of '3', etc. below are the two tables in question along with the relationship between them Table1: "tbl Defect Count" Table2: "tblDefects" ID - autogenerated key AutoID - autogenerated number Date - Date/Time (mm/dd/yy) ID - Number (tied to ID field in tbl Part Number - text Defect Count) SortTime - Number DefCode - text TotalSort - Number DefQuantity - number NCM_Num - text Containment - text PlantNum - text as i stated earlier, for each record in tbl Defect Count, there can be any where from 0 to 27 records (on any given day.) the autoid field in tblDefects was due to a major change in database design. the original database was not going to be very accurate, but my supervisor didn't want to lose the 3 months of data in it. doesn't do anything other than count total number of records. as for creating a running sum on the sum of total sort, that gave a really oddball answer. the totals in the report footer section are correct for what is in the Details section. the problem is getting the [SumofTotalSort] numbers (in the Details sections) to be correct when getting the [SumofDefects] numbers also (Details section). if you would like, i can run the documenter and send you the output for the tables, queries, code, forms, and reports... or i could send you the database as a zip file. will have to disable the macros first, but the zip file is 231Kb in size. thanks again for replying. "Duane Hookom" wrote: Do your defects actually print across the page like: a(1), b(3), c(2) Why do you have Part Numbers repeated? Is there some column/field you are providing? Did you try creating a running sum on the SumOfTotalSort? -- Duane Hookom MS Access MVP -- "jkendrick75" wrote in message ... ok, i'll try. currently my report kinda looks like this page header: Part Number Quantity Inspected Defect Quantity Detail: [PartNumber] [SumOfTotalSort] [SumofDefects] page footer: ="Page " & [Page] & " & [Pages] report footer: Total Sorted: [=sum([sumoftotalsort]) Total Defects: [=Sum([sumofDefects])] In the Detail section, it will show all part numbers that are in the database within the selected date range, listed as separate rows, and each part number's total sorted, as long as that is all i am asking for in the query. if i try to include the total defects, then the total sorted is inflated. such as part number sorted defects 1 100 a(1), b(3), c(2) 2 200 3 50 a(2) 4 300 b(5), c(5) 1 300 b(2), c(5) 2 200 a(4), b(6) 3 400 c(10) my total sorted should be 1550 and total defects should be 45. what i am getting when i try to include the total defects in my query is total sorted 2550 and a total defects of 45. what it is doing is for each defect (as in part number 1 has 3 different defects the first time and then 2 defects the second time) it is adding the total sorted (for that instance) to itself for each different defect. the query that i am using is as follows SELECT [TBL defect count].[Part Number], Sum(tblDefects.DefQuantity) AS sumofdefquantity FROM [TBL defect count] LEFT JOIN tblDefects ON [TBL defect count].ID = tblDefects.ID WHERE ((([TBL defect count].Date) Between [forms]![frmDateRangeSums]![StartDate] And [forms]![frmDateRangeSums]![EndDate])) GROUP BY [TBL defect count].[Part Number]; the frmDateRangeSums is the form that i use to ask for the start date and end date of the date range. "Duane Hookom" wrote: How about showing us what is happening and what you want with something like this: =Part Header============ Part Number:ABC Total Sorted: 5 -Defects (detail)------- Too Big Too Small =Part Header============ Part NumberEF Total Sorted: 10 -Defects (detail)------- Too Big Too Small Too Flat Too Fat #Report Footer############ Total Sorted 15 -Is this your issue? -- Duane Hookom MS Access MVP -- "jkendrick75" wrote in message ... that part works fine, just when i try to add in the total defects for that part during the date range, then the total sorted number is more than what it should be. what is happening is say for part 123, there were 100 parts sorted (during a given date range). for that 100 parts sorted, there were 3 different defect codes (a with 2 defects, b with 3 defects, c with 4 defects). the total sorted should be 100 parts sorted (which i get if i don't try to total the defects) with a total of 9 defects. what i am getting is a total of 300 parts sorted and 9 defects. i have tried to write a query for just the total sorted, then a query for just the total defects, and then a query that uses the first 2 queries, but all that did was total all parts sorted (grouped by the part number) in the entire database, along with all defects for entire database. it was ignoring the date range and giving totals for all parts, and then just did a one time total of all defects, and assigned it to each part number. thank you for responding so quickly, and any other ideas will be welcome. "Duane Hookom" wrote: There are a couple methods for doing this. One is to create a text box in the PartNumber(?) group header: Name: txtTotalSorted Control Source: [Total Sorted] Running Sum: Over All Visible: No Then, add a text box to your report footer Control Source: =txtTotalSorted You could also set up a totals query based on [tblDefect Count] that returns only one record with the sum of [Total Sorted] for the particular date range. You could then add this query to your report's record source. -- Duane Hookom MS Access MVP -- "jkendrick75" wrote in message ... i am trying to create a report that, using a start and end date that is given on a form, groups by part number and sums the number of parts sorted per part for the given date range. this part works fine as the date, part number, and the values for the total sorted are all in one table (tblDefect Count). if i try to get the total number of defects per part for the given date range, i get the correct number of defects but an incorrect number for the total sorted. the number of defects comes from a second table (tblDefects), that is tied to tblDefect Count by using an autogenerated key field called ID in tblDefect Count. for every ID in tblDefect Count, there could be any where from 0 to 27 records in tblDefects (there are 27 different defect codes and each code has its own amount for defects for that code and part number.) any help with this would be greatly appreciated as i have been working on this for about a month now. if you need any thing clarified, please let me know and i will do what i can. if you want to see a copy of the database, let me know. |
#8
|
|||
|
|||
Is the [SumofTotalSort] in a group header or in the details section? If it
is repeating in the detail section then why is it in the detail section and not a group header? Consider removing the tblDefects entirely and placing it in a subreport. -- Duane Hookom MS Access MVP -- "jkendrick75" wrote in message ... what i was doing with showing the defects like that and having the part numbers repeat was to show an example of the data for a date range. say over the course of a week, the same part may be inspected each day, but have a different quantity of parts inspected each day. each day may have a different set of defects and defect quantities. in my example, it was defect code 'a' with a defect quantity of '1', code 'b' with a defect quantity of '3', etc. below are the two tables in question along with the relationship between them Table1: "tbl Defect Count" Table2: "tblDefects" ID - autogenerated key AutoID - autogenerated number Date - Date/Time (mm/dd/yy) ID - Number (tied to ID field in tbl Part Number - text Defect Count) SortTime - Number DefCode - text TotalSort - Number DefQuantity - number NCM_Num - text Containment - text PlantNum - text as i stated earlier, for each record in tbl Defect Count, there can be any where from 0 to 27 records (on any given day.) the autoid field in tblDefects was due to a major change in database design. the original database was not going to be very accurate, but my supervisor didn't want to lose the 3 months of data in it. doesn't do anything other than count total number of records. as for creating a running sum on the sum of total sort, that gave a really oddball answer. the totals in the report footer section are correct for what is in the Details section. the problem is getting the [SumofTotalSort] numbers (in the Details sections) to be correct when getting the [SumofDefects] numbers also (Details section). if you would like, i can run the documenter and send you the output for the tables, queries, code, forms, and reports... or i could send you the database as a zip file. will have to disable the macros first, but the zip file is 231Kb in size. thanks again for replying. "Duane Hookom" wrote: Do your defects actually print across the page like: a(1), b(3), c(2) Why do you have Part Numbers repeated? Is there some column/field you are providing? Did you try creating a running sum on the SumOfTotalSort? -- Duane Hookom MS Access MVP -- "jkendrick75" wrote in message ... ok, i'll try. currently my report kinda looks like this page header: Part Number Quantity Inspected Defect Quantity Detail: [PartNumber] [SumOfTotalSort] [SumofDefects] page footer: ="Page " & [Page] & " & [Pages] report footer: Total Sorted: [=sum([sumoftotalsort]) Total Defects: [=Sum([sumofDefects])] In the Detail section, it will show all part numbers that are in the database within the selected date range, listed as separate rows, and each part number's total sorted, as long as that is all i am asking for in the query. if i try to include the total defects, then the total sorted is inflated. such as part number sorted defects 1 100 a(1), b(3), c(2) 2 200 3 50 a(2) 4 300 b(5), c(5) 1 300 b(2), c(5) 2 200 a(4), b(6) 3 400 c(10) my total sorted should be 1550 and total defects should be 45. what i am getting when i try to include the total defects in my query is total sorted 2550 and a total defects of 45. what it is doing is for each defect (as in part number 1 has 3 different defects the first time and then 2 defects the second time) it is adding the total sorted (for that instance) to itself for each different defect. the query that i am using is as follows SELECT [TBL defect count].[Part Number], Sum(tblDefects.DefQuantity) AS sumofdefquantity FROM [TBL defect count] LEFT JOIN tblDefects ON [TBL defect count].ID = tblDefects.ID WHERE ((([TBL defect count].Date) Between [forms]![frmDateRangeSums]![StartDate] And [forms]![frmDateRangeSums]![EndDate])) GROUP BY [TBL defect count].[Part Number]; the frmDateRangeSums is the form that i use to ask for the start date and end date of the date range. "Duane Hookom" wrote: How about showing us what is happening and what you want with something like this: =Part Header============ Part Number:ABC Total Sorted: 5 -Defects (detail)------- Too Big Too Small =Part Header============ Part NumberEF Total Sorted: 10 -Defects (detail)------- Too Big Too Small Too Flat Too Fat #Report Footer############ Total Sorted 15 -Is this your issue? -- Duane Hookom MS Access MVP -- "jkendrick75" wrote in message ... that part works fine, just when i try to add in the total defects for that part during the date range, then the total sorted number is more than what it should be. what is happening is say for part 123, there were 100 parts sorted (during a given date range). for that 100 parts sorted, there were 3 different defect codes (a with 2 defects, b with 3 defects, c with 4 defects). the total sorted should be 100 parts sorted (which i get if i don't try to total the defects) with a total of 9 defects. what i am getting is a total of 300 parts sorted and 9 defects. i have tried to write a query for just the total sorted, then a query for just the total defects, and then a query that uses the first 2 queries, but all that did was total all parts sorted (grouped by the part number) in the entire database, along with all defects for entire database. it was ignoring the date range and giving totals for all parts, and then just did a one time total of all defects, and assigned it to each part number. thank you for responding so quickly, and any other ideas will be welcome. "Duane Hookom" wrote: There are a couple methods for doing this. One is to create a text box in the PartNumber(?) group header: Name: txtTotalSorted Control Source: [Total Sorted] Running Sum: Over All Visible: No Then, add a text box to your report footer Control Source: =txtTotalSorted You could also set up a totals query based on [tblDefect Count] that returns only one record with the sum of [Total Sorted] for the particular date range. You could then add this query to your report's record source. -- Duane Hookom MS Access MVP -- "jkendrick75" wrote in message ... i am trying to create a report that, using a start and end date that is given on a form, groups by part number and sums the number of parts sorted per part for the given date range. this part works fine as the date, part number, and the values for the total sorted are all in one table (tblDefect Count). if i try to get the total number of defects per part for the given date range, i get the correct number of defects but an incorrect number for the total sorted. the number of defects comes from a second table (tblDefects), that is tied to tblDefect Count by using an autogenerated key field called ID in tblDefect Count. for every ID in tblDefect Count, there could be any where from 0 to 27 records in tblDefects (there are 27 different defect codes and each code has its own amount for defects for that code and part number.) any help with this would be greatly appreciated as i have been working on this for about a month now. if you need any thing clarified, please let me know and i will do what i can. if you want to see a copy of the database, let me know. |
#9
|
|||
|
|||
the [SumofTotalSort] is repeating in the details section. i put it there to
conserve space, and to make a cleaner looking report. i have tried removing the tblDefects and placing it in a subreport, but i apparently didn't do it correctly or something, can you give me some instructions on how to do this. like where to put what and so forth... thanks again. "Duane Hookom" wrote: Is the [SumofTotalSort] in a group header or in the details section? If it is repeating in the detail section then why is it in the detail section and not a group header? Consider removing the tblDefects entirely and placing it in a subreport. -- Duane Hookom MS Access MVP -- "jkendrick75" wrote in message ... what i was doing with showing the defects like that and having the part numbers repeat was to show an example of the data for a date range. say over the course of a week, the same part may be inspected each day, but have a different quantity of parts inspected each day. each day may have a different set of defects and defect quantities. in my example, it was defect code 'a' with a defect quantity of '1', code 'b' with a defect quantity of '3', etc. below are the two tables in question along with the relationship between them Table1: "tbl Defect Count" Table2: "tblDefects" ID - autogenerated key AutoID - autogenerated number Date - Date/Time (mm/dd/yy) ID - Number (tied to ID field in tbl Part Number - text Defect Count) SortTime - Number DefCode - text TotalSort - Number DefQuantity - number NCM_Num - text Containment - text PlantNum - text as i stated earlier, for each record in tbl Defect Count, there can be any where from 0 to 27 records (on any given day.) the autoid field in tblDefects was due to a major change in database design. the original database was not going to be very accurate, but my supervisor didn't want to lose the 3 months of data in it. doesn't do anything other than count total number of records. as for creating a running sum on the sum of total sort, that gave a really oddball answer. the totals in the report footer section are correct for what is in the Details section. the problem is getting the [SumofTotalSort] numbers (in the Details sections) to be correct when getting the [SumofDefects] numbers also (Details section). if you would like, i can run the documenter and send you the output for the tables, queries, code, forms, and reports... or i could send you the database as a zip file. will have to disable the macros first, but the zip file is 231Kb in size. thanks again for replying. "Duane Hookom" wrote: Do your defects actually print across the page like: a(1), b(3), c(2) Why do you have Part Numbers repeated? Is there some column/field you are providing? Did you try creating a running sum on the SumOfTotalSort? -- Duane Hookom MS Access MVP -- "jkendrick75" wrote in message ... ok, i'll try. currently my report kinda looks like this page header: Part Number Quantity Inspected Defect Quantity Detail: [PartNumber] [SumOfTotalSort] [SumofDefects] page footer: ="Page " & [Page] & " & [Pages] report footer: Total Sorted: [=sum([sumoftotalsort]) Total Defects: [=Sum([sumofDefects])] In the Detail section, it will show all part numbers that are in the database within the selected date range, listed as separate rows, and each part number's total sorted, as long as that is all i am asking for in the query. if i try to include the total defects, then the total sorted is inflated. such as part number sorted defects 1 100 a(1), b(3), c(2) 2 200 3 50 a(2) 4 300 b(5), c(5) 1 300 b(2), c(5) 2 200 a(4), b(6) 3 400 c(10) my total sorted should be 1550 and total defects should be 45. what i am getting when i try to include the total defects in my query is total sorted 2550 and a total defects of 45. what it is doing is for each defect (as in part number 1 has 3 different defects the first time and then 2 defects the second time) it is adding the total sorted (for that instance) to itself for each different defect. the query that i am using is as follows SELECT [TBL defect count].[Part Number], Sum(tblDefects.DefQuantity) AS sumofdefquantity FROM [TBL defect count] LEFT JOIN tblDefects ON [TBL defect count].ID = tblDefects.ID WHERE ((([TBL defect count].Date) Between [forms]![frmDateRangeSums]![StartDate] And [forms]![frmDateRangeSums]![EndDate])) GROUP BY [TBL defect count].[Part Number]; the frmDateRangeSums is the form that i use to ask for the start date and end date of the date range. "Duane Hookom" wrote: How about showing us what is happening and what you want with something like this: =Part Header============ Part Number:ABC Total Sorted: 5 -Defects (detail)------- Too Big Too Small =Part Header============ Part NumberEF Total Sorted: 10 -Defects (detail)------- Too Big Too Small Too Flat Too Fat #Report Footer############ Total Sorted 15 -Is this your issue? -- Duane Hookom MS Access MVP -- "jkendrick75" wrote in message ... that part works fine, just when i try to add in the total defects for that part during the date range, then the total sorted number is more than what it should be. what is happening is say for part 123, there were 100 parts sorted (during a given date range). for that 100 parts sorted, there were 3 different defect codes (a with 2 defects, b with 3 defects, c with 4 defects). the total sorted should be 100 parts sorted (which i get if i don't try to total the defects) with a total of 9 defects. what i am getting is a total of 300 parts sorted and 9 defects. i have tried to write a query for just the total sorted, then a query for just the total defects, and then a query that uses the first 2 queries, but all that did was total all parts sorted (grouped by the part number) in the entire database, along with all defects for entire database. it was ignoring the date range and giving totals for all parts, and then just did a one time total of all defects, and assigned it to each part number. thank you for responding so quickly, and any other ideas will be welcome. "Duane Hookom" wrote: There are a couple methods for doing this. One is to create a text box in the PartNumber(?) group header: Name: txtTotalSorted Control Source: [Total Sorted] Running Sum: Over All Visible: No Then, add a text box to your report footer Control Source: =txtTotalSorted You could also set up a totals query based on [tblDefect Count] that returns only one record with the sum of [Total Sorted] for the particular date range. You could then add this query to your report's record source. -- Duane Hookom MS Access MVP -- "jkendrick75" wrote in message ... i am trying to create a report that, using a start and end date that is given on a form, groups by part number and sums the number of parts sorted per part for the given date range. this part works fine as the date, part number, and the values for the total sorted are all in one table (tblDefect Count). if i try to get the total number of defects per part for the given date range, i get the correct number of defects but an incorrect number for the total sorted. the number of defects comes from a second table (tblDefects), that is tied to tblDefect Count by using an autogenerated key field called ID in tblDefect Count. for every ID in tblDefect Count, there could be any where from 0 to 27 records in tblDefects (there are 27 different defect codes and each code has its own amount for defects for that code and part number.) any help with this would be greatly appreciated as i have been working on this for about a month now. if you need any thing clarified, please let me know and i will do what i can. if you want to see a copy of the database, let me know. |
#10
|
|||
|
|||
This whole issue may go away if you just place SUmOfTOtalSort in a group
header and then use a running sum. Could you try this? -- Duane Hookom MS Access MVP "jkendrick75" wrote in message ... the [SumofTotalSort] is repeating in the details section. i put it there to conserve space, and to make a cleaner looking report. i have tried removing the tblDefects and placing it in a subreport, but i apparently didn't do it correctly or something, can you give me some instructions on how to do this. like where to put what and so forth... thanks again. "Duane Hookom" wrote: Is the [SumofTotalSort] in a group header or in the details section? If it is repeating in the detail section then why is it in the detail section and not a group header? Consider removing the tblDefects entirely and placing it in a subreport. -- Duane Hookom MS Access MVP -- "jkendrick75" wrote in message ... what i was doing with showing the defects like that and having the part numbers repeat was to show an example of the data for a date range. say over the course of a week, the same part may be inspected each day, but have a different quantity of parts inspected each day. each day may have a different set of defects and defect quantities. in my example, it was defect code 'a' with a defect quantity of '1', code 'b' with a defect quantity of '3', etc. below are the two tables in question along with the relationship between them Table1: "tbl Defect Count" Table2: "tblDefects" ID - autogenerated key AutoID - autogenerated number Date - Date/Time (mm/dd/yy) ID - Number (tied to ID field in tbl Part Number - text Defect Count) SortTime - Number DefCode - text TotalSort - Number DefQuantity - number NCM_Num - text Containment - text PlantNum - text as i stated earlier, for each record in tbl Defect Count, there can be any where from 0 to 27 records (on any given day.) the autoid field in tblDefects was due to a major change in database design. the original database was not going to be very accurate, but my supervisor didn't want to lose the 3 months of data in it. doesn't do anything other than count total number of records. as for creating a running sum on the sum of total sort, that gave a really oddball answer. the totals in the report footer section are correct for what is in the Details section. the problem is getting the [SumofTotalSort] numbers (in the Details sections) to be correct when getting the [SumofDefects] numbers also (Details section). if you would like, i can run the documenter and send you the output for the tables, queries, code, forms, and reports... or i could send you the database as a zip file. will have to disable the macros first, but the zip file is 231Kb in size. thanks again for replying. "Duane Hookom" wrote: Do your defects actually print across the page like: a(1), b(3), c(2) Why do you have Part Numbers repeated? Is there some column/field you are providing? Did you try creating a running sum on the SumOfTotalSort? -- Duane Hookom MS Access MVP -- "jkendrick75" wrote in message ... ok, i'll try. currently my report kinda looks like this page header: Part Number Quantity Inspected Defect Quantity Detail: [PartNumber] [SumOfTotalSort] [SumofDefects] page footer: ="Page " & [Page] & " & [Pages] report footer: Total Sorted: [=sum([sumoftotalsort]) Total Defects: [=Sum([sumofDefects])] In the Detail section, it will show all part numbers that are in the database within the selected date range, listed as separate rows, and each part number's total sorted, as long as that is all i am asking for in the query. if i try to include the total defects, then the total sorted is inflated. such as part number sorted defects 1 100 a(1), b(3), c(2) 2 200 3 50 a(2) 4 300 b(5), c(5) 1 300 b(2), c(5) 2 200 a(4), b(6) 3 400 c(10) my total sorted should be 1550 and total defects should be 45. what i am getting when i try to include the total defects in my query is total sorted 2550 and a total defects of 45. what it is doing is for each defect (as in part number 1 has 3 different defects the first time and then 2 defects the second time) it is adding the total sorted (for that instance) to itself for each different defect. the query that i am using is as follows SELECT [TBL defect count].[Part Number], Sum(tblDefects.DefQuantity) AS sumofdefquantity FROM [TBL defect count] LEFT JOIN tblDefects ON [TBL defect count].ID = tblDefects.ID WHERE ((([TBL defect count].Date) Between [forms]![frmDateRangeSums]![StartDate] And [forms]![frmDateRangeSums]![EndDate])) GROUP BY [TBL defect count].[Part Number]; the frmDateRangeSums is the form that i use to ask for the start date and end date of the date range. "Duane Hookom" wrote: How about showing us what is happening and what you want with something like this: =Part Header============ Part Number:ABC Total Sorted: 5 -Defects (detail)------- Too Big Too Small =Part Header============ Part NumberEF Total Sorted: 10 -Defects (detail)------- Too Big Too Small Too Flat Too Fat #Report Footer############ Total Sorted 15 -Is this your issue? -- Duane Hookom MS Access MVP -- "jkendrick75" wrote in message ... that part works fine, just when i try to add in the total defects for that part during the date range, then the total sorted number is more than what it should be. what is happening is say for part 123, there were 100 parts sorted (during a given date range). for that 100 parts sorted, there were 3 different defect codes (a with 2 defects, b with 3 defects, c with 4 defects). the total sorted should be 100 parts sorted (which i get if i don't try to total the defects) with a total of 9 defects. what i am getting is a total of 300 parts sorted and 9 defects. i have tried to write a query for just the total sorted, then a query for just the total defects, and then a query that uses the first 2 queries, but all that did was total all parts sorted (grouped by the part number) in the entire database, along with all defects for entire database. it was ignoring the date range and giving totals for all parts, and then just did a one time total of all defects, and assigned it to each part number. thank you for responding so quickly, and any other ideas will be welcome. "Duane Hookom" wrote: There are a couple methods for doing this. One is to create a text box in the PartNumber(?) group header: Name: txtTotalSorted Control Source: [Total Sorted] Running Sum: Over All Visible: No Then, add a text box to your report footer Control Source: =txtTotalSorted You could also set up a totals query based on [tblDefect Count] that returns only one record with the sum of [Total Sorted] for the particular date range. You could then add this query to your report's record source. -- Duane Hookom MS Access MVP -- "jkendrick75" wrote in message ... i am trying to create a report that, using a start and end date that is given on a form, groups by part number and sums the number of parts sorted per part for the given date range. this part works fine as the date, part number, and the values for the total sorted are all in one table (tblDefect Count). if i try to get the total number of defects per part for the given date range, i get the correct number of defects but an incorrect number for the total sorted. the number of defects comes from a second table (tblDefects), that is tied to tblDefect Count by using an autogenerated key field called ID in tblDefect Count. for every ID in tblDefect Count, there could be any where from 0 to 27 records in tblDefects (there are 27 different defect codes and each code has its own amount for defects for that code and part number.) any help with this would be greatly appreciated as i have been working on this for about a month now. if you need any thing clarified, please let me know and i will do what i can. if you want to see a copy of the database, let me know. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
report group subtotals incorrect | ivan | Running & Setting Up Queries | 6 | October 4th, 2004 01:04 PM |
report subtotals incorrect | ivan | Setting Up & Running Reports | 2 | October 4th, 2004 05:04 AM |
Creating Report Based on 2 Tables (Cases [parent] and Invoice) | JoanOC | Running & Setting Up Queries | 1 | September 12th, 2004 01:35 AM |
Display Parameter from Form on Report | sara | Setting Up & Running Reports | 10 | July 19th, 2004 04:54 PM |
linking separate tables into one table and one report | Timothy Hollis | Setting Up & Running Reports | 1 | July 16th, 2004 08:10 PM |