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
|
|||
|
|||
help needed
I am needing a query to power a report that my boss wants done by this
friday. i have been working on this report for a couple of weeks now, without getting anywhere. this is what i have for my tables. Parent table: [tbl Defect Count] ID (autonumber; PK) Date Part Number SortTime TotalSort NCM_Num Containment PlantNum Child Table: [tblDefects] AutoID (autonumber; PK) ID (linked to [tbl Defect Count].[ID]) DefCode DefQuantity the relation ship between these two tables is a 1 to many relationship. for 1 record in [tbl Defect Count] there could be anywhere from 1 to 27 records in [tblDefects], or no record at all. what my problem is currently is if there are more records (for a given part within a given date range) in [tbl Defect Count] than there are records (records that correspond to an ID in [tbl Defect Count]) in [tblDefects] i need to have the totals box in the footer of the report set for a running sum. however, if there are more records (for a given part within a given date range) in [tblDefects] than there are records (records that correspond to an ID in [tbl Defect Count]) in [tbl Defect Count] i need to have the totals box in the footer of the report set for a non-running sum. example: [tbl Defect Count] has 2 records (or 2 ID numbers) of a part within a 1 month date range. however [tblDefects] has a total of 3 records between the two records in [tbl Defect Count]. i need to have my totals box in the report footer set as a non running sum, otherwise my total is twice than what it should be. example 2: [tbl Defect Count] has 8 records (or 8 ID numbers) for a part within a 1 month date range. [tblDefects] has a total of 6 records between the 8 ID numbers in [tbl Defect Count] for the date range. I need to have my totals box in the report footer set as a running sum to count all records and give me a correct total. any help in this matter will be greatly appreciated. thanks in advance |
#2
|
|||
|
|||
You gave us the two "quizzes" but forgot the answer key! Why would you not
tell us in each example what you calculate the total to be? How about some sample records? -- Duane Hookom MS Access MVP "jkendrick75" wrote in message ... I am needing a query to power a report that my boss wants done by this friday. i have been working on this report for a couple of weeks now, without getting anywhere. this is what i have for my tables. Parent table: [tbl Defect Count] ID (autonumber; PK) Date Part Number SortTime TotalSort NCM_Num Containment PlantNum Child Table: [tblDefects] AutoID (autonumber; PK) ID (linked to [tbl Defect Count].[ID]) DefCode DefQuantity the relation ship between these two tables is a 1 to many relationship. for 1 record in [tbl Defect Count] there could be anywhere from 1 to 27 records in [tblDefects], or no record at all. what my problem is currently is if there are more records (for a given part within a given date range) in [tbl Defect Count] than there are records (records that correspond to an ID in [tbl Defect Count]) in [tblDefects] i need to have the totals box in the footer of the report set for a running sum. however, if there are more records (for a given part within a given date range) in [tblDefects] than there are records (records that correspond to an ID in [tbl Defect Count]) in [tbl Defect Count] i need to have the totals box in the footer of the report set for a non-running sum. example: [tbl Defect Count] has 2 records (or 2 ID numbers) of a part within a 1 month date range. however [tblDefects] has a total of 3 records between the two records in [tbl Defect Count]. i need to have my totals box in the report footer set as a non running sum, otherwise my total is twice than what it should be. example 2: [tbl Defect Count] has 8 records (or 8 ID numbers) for a part within a 1 month date range. [tblDefects] has a total of 6 records between the 8 ID numbers in [tbl Defect Count] for the date range. I need to have my totals box in the report footer set as a running sum to count all records and give me a correct total. any help in this matter will be greatly appreciated. thanks in advance |
#3
|
|||
|
|||
ok no prob. the following will be samples of data that i am using in
particular. Sample 1 [tbl Defect Count] ID Date PartNum SortTime TotalSort NCM_Num Cont PlantNum 2713 1/4/2005 40000 5.66 1292 Press Side 1 2722 1/5/2005 40000 0.5 210 Press Side 1 [tblDefects] AutoID ID DefCode DefQuantity 6660 2713 20 2 6661 2713 16 47 6673 2722 16 1 6674 2722 24 5 Sample2 [tbl Defect Count] ID Date PartNum SortTime TotalSort NCM_Num Containment PlantNum 2704 1/4/2005 AW060352 1.66 396 Press Side 1 2705 1/4/2005 AW060352 1.66 660 Press Side 1 2733 1/5/2005 AW060352 4 531 GP 12 1 2756 1/6/2005 AW060352 1.5 100 Press Side 1 2807 1/10/2005 AW060352 2.66 333 Press Side 1 2856 1/10/2005 AW060352 3 330 GP 12 1 2857 1/10/2005 AW060352 3 660 GP 12 1 2858 1/10/2005 AW060352 3 660 GP 12 1 2859 1/11/2005 AW060352 1.5 165 GP 12 1 2860 1/10/2005 AW060352 4 663 GP 12 1 2893 1/12/2005 AW060352 0.58 594 Press Side 1 2903 1/12/2005 AW060352 4 666 Press Side 1 2904 1/14/2005 AW060352 4.58 330 Press Side 1 2921 1/13/2005 AW060352 4 669 Press Side 1 2940 1/17/2005 AW060352 6.83 666 GP 12 1 2941 1/17/2005 AW060352 1 234 GP 12 1 2984 1/19/2005 AW060352 0.17 33 GP 12 1 3011 1/20/2005 AW060352 2 528 GP 12 1 3012 1/20/2005 AW060352 2.08 660 GP 12 1 [tblDefects] AutoID ID DefCode DefQuantity 6693 2733 26 1 6694 2733 24 2 6726 2756 24 1 6727 2756 25 1 6728 2756 26 1 6772 2807 7 1 6773 2807 24 1 6774 2807 26 1 6866 2859 24 3 6867 2860 26 1 6868 2860 7 1 6869 2860 24 1 6924 2903 24 1 6925 2903 15 3 6926 2903 20 2 6927 2904 20 13 6951 2921 26 9 6977 2940 7 1 6978 2940 26 6 6979 2941 15 2 6980 2941 20 1 7034 2984 7 1 when the query that i have now is run, the start date, end date, and part number are asked for (for Sample 1) and i get the following answer for the date range of 1/1/05 - 1/20/05 and part number 40000. PartNum SumOfTotalSort SumOfSum Of DefQuantity DefCode 40000 1502 48 16 40000 1292 2 20 40000 210 5 24 On the report i am getting a total of SumOfTotalSort of 3004 if i use a running sum and a total of 1502 if i don't use a running sum. the totals for the sumofsum of DefQuantity of 55 is correct. for sample 2 i get (for same date range but part number AW060352) PartNum SumOfTotalSort SumOfSum Of DefQuantity DefCode AW060352 4488 AW060352 2962 19 26 AW060352 2458 9 24 AW060352 1695 4 7 AW060352 1230 16 20 AW060352 900 5 15 AW060352 100 1 25 on the report i am getting the correct answer for the total sorted of 13,833 only when i am running it as a =Sum([SumOfTotalSort]). if i am not running as a =Sum([SumOfTotalSort]), then i get an answer of 4,488. which is the same amount as the amount with null values for defcode and defquantity. hope this helps. "Duane Hookom" wrote: You gave us the two "quizzes" but forgot the answer key! Why would you not tell us in each example what you calculate the total to be? How about some sample records? -- Duane Hookom MS Access MVP "jkendrick75" wrote in message ... I am needing a query to power a report that my boss wants done by this friday. i have been working on this report for a couple of weeks now, without getting anywhere. this is what i have for my tables. Parent table: [tbl Defect Count] ID (autonumber; PK) Date Part Number SortTime TotalSort NCM_Num Containment PlantNum Child Table: [tblDefects] AutoID (autonumber; PK) ID (linked to [tbl Defect Count].[ID]) DefCode DefQuantity the relation ship between these two tables is a 1 to many relationship. for 1 record in [tbl Defect Count] there could be anywhere from 1 to 27 records in [tblDefects], or no record at all. what my problem is currently is if there are more records (for a given part within a given date range) in [tbl Defect Count] than there are records (records that correspond to an ID in [tbl Defect Count]) in [tblDefects] i need to have the totals box in the footer of the report set for a running sum. however, if there are more records (for a given part within a given date range) in [tblDefects] than there are records (records that correspond to an ID in [tbl Defect Count]) in [tbl Defect Count] i need to have the totals box in the footer of the report set for a non-running sum. example: [tbl Defect Count] has 2 records (or 2 ID numbers) of a part within a 1 month date range. however [tblDefects] has a total of 3 records between the two records in [tbl Defect Count]. i need to have my totals box in the report footer set as a non running sum, otherwise my total is twice than what it should be. example 2: [tbl Defect Count] has 8 records (or 8 ID numbers) for a part within a 1 month date range. [tblDefects] has a total of 6 records between the 8 ID numbers in [tbl Defect Count] for the date range. I need to have my totals box in the report footer set as a running sum to count all records and give me a correct total. any help in this matter will be greatly appreciated. thanks in advance |
#4
|
|||
|
|||
sorry, i noticed a mistake that i made for the second sample ouput. the
total should be 8,878 instead of the 13,833 "jkendrick75" wrote: ok no prob. the following will be samples of data that i am using in particular. Sample 1 [tbl Defect Count] ID Date PartNum SortTime TotalSort NCM_Num Cont PlantNum 2713 1/4/2005 40000 5.66 1292 Press Side 1 2722 1/5/2005 40000 0.5 210 Press Side 1 [tblDefects] AutoID ID DefCode DefQuantity 6660 2713 20 2 6661 2713 16 47 6673 2722 16 1 6674 2722 24 5 Sample2 [tbl Defect Count] ID Date PartNum SortTime TotalSort NCM_Num Containment PlantNum 2704 1/4/2005 AW060352 1.66 396 Press Side 1 2705 1/4/2005 AW060352 1.66 660 Press Side 1 2733 1/5/2005 AW060352 4 531 GP 12 1 2756 1/6/2005 AW060352 1.5 100 Press Side 1 2807 1/10/2005 AW060352 2.66 333 Press Side 1 2856 1/10/2005 AW060352 3 330 GP 12 1 2857 1/10/2005 AW060352 3 660 GP 12 1 2858 1/10/2005 AW060352 3 660 GP 12 1 2859 1/11/2005 AW060352 1.5 165 GP 12 1 2860 1/10/2005 AW060352 4 663 GP 12 1 2893 1/12/2005 AW060352 0.58 594 Press Side 1 2903 1/12/2005 AW060352 4 666 Press Side 1 2904 1/14/2005 AW060352 4.58 330 Press Side 1 2921 1/13/2005 AW060352 4 669 Press Side 1 2940 1/17/2005 AW060352 6.83 666 GP 12 1 2941 1/17/2005 AW060352 1 234 GP 12 1 2984 1/19/2005 AW060352 0.17 33 GP 12 1 3011 1/20/2005 AW060352 2 528 GP 12 1 3012 1/20/2005 AW060352 2.08 660 GP 12 1 [tblDefects] AutoID ID DefCode DefQuantity 6693 2733 26 1 6694 2733 24 2 6726 2756 24 1 6727 2756 25 1 6728 2756 26 1 6772 2807 7 1 6773 2807 24 1 6774 2807 26 1 6866 2859 24 3 6867 2860 26 1 6868 2860 7 1 6869 2860 24 1 6924 2903 24 1 6925 2903 15 3 6926 2903 20 2 6927 2904 20 13 6951 2921 26 9 6977 2940 7 1 6978 2940 26 6 6979 2941 15 2 6980 2941 20 1 7034 2984 7 1 when the query that i have now is run, the start date, end date, and part number are asked for (for Sample 1) and i get the following answer for the date range of 1/1/05 - 1/20/05 and part number 40000. PartNum SumOfTotalSort SumOfSum Of DefQuantity DefCode 40000 1502 48 16 40000 1292 2 20 40000 210 5 24 On the report i am getting a total of SumOfTotalSort of 3004 if i use a running sum and a total of 1502 if i don't use a running sum. the totals for the sumofsum of DefQuantity of 55 is correct. for sample 2 i get (for same date range but part number AW060352) PartNum SumOfTotalSort SumOfSum Of DefQuantity DefCode AW060352 4488 AW060352 2962 19 26 AW060352 2458 9 24 AW060352 1695 4 7 AW060352 1230 16 20 AW060352 900 5 15 AW060352 100 1 25 on the report i am getting the correct answer for the total sorted of 13,833 only when i am running it as a =Sum([SumOfTotalSort]). if i am not running as a =Sum([SumOfTotalSort]), then i get an answer of 4,488. which is the same amount as the amount with null values for defcode and defquantity. hope this helps. "Duane Hookom" wrote: You gave us the two "quizzes" but forgot the answer key! Why would you not tell us in each example what you calculate the total to be? How about some sample records? -- Duane Hookom MS Access MVP "jkendrick75" wrote in message ... I am needing a query to power a report that my boss wants done by this friday. i have been working on this report for a couple of weeks now, without getting anywhere. this is what i have for my tables. Parent table: [tbl Defect Count] ID (autonumber; PK) Date Part Number SortTime TotalSort NCM_Num Containment PlantNum Child Table: [tblDefects] AutoID (autonumber; PK) ID (linked to [tbl Defect Count].[ID]) DefCode DefQuantity the relation ship between these two tables is a 1 to many relationship. for 1 record in [tbl Defect Count] there could be anywhere from 1 to 27 records in [tblDefects], or no record at all. what my problem is currently is if there are more records (for a given part within a given date range) in [tbl Defect Count] than there are records (records that correspond to an ID in [tbl Defect Count]) in [tblDefects] i need to have the totals box in the footer of the report set for a running sum. however, if there are more records (for a given part within a given date range) in [tblDefects] than there are records (records that correspond to an ID in [tbl Defect Count]) in [tbl Defect Count] i need to have the totals box in the footer of the report set for a non-running sum. example: [tbl Defect Count] has 2 records (or 2 ID numbers) of a part within a 1 month date range. however [tblDefects] has a total of 3 records between the two records in [tbl Defect Count]. i need to have my totals box in the report footer set as a non running sum, otherwise my total is twice than what it should be. example 2: [tbl Defect Count] has 8 records (or 8 ID numbers) for a part within a 1 month date range. [tblDefects] has a total of 6 records between the 8 ID numbers in [tbl Defect Count] for the date range. I need to have my totals box in the report footer set as a running sum to count all records and give me a correct total. any help in this matter will be greatly appreciated. thanks in advance |
#5
|
|||
|
|||
I won't even try to read, understand, copy to tables, reformat,... your long
examples. I doubt that SortTime and some other fields have any bearing on your issue. Please try to provide only a couple main records and no more than 10 detailed records and be accurate so we don't have to look back and forth between two emails. I have put a lot of time into attempting to do your work in the past (as have many others). Please don't make it so darned hard. You should be able to describe and illustrate you needs in much less complexity. -- Duane Hookom MS Access MVP "jkendrick75" wrote in message ... ok no prob. the following will be samples of data that i am using in particular. Sample 1 [tbl Defect Count] ID Date PartNum SortTime TotalSort NCM_Num Cont PlantNum 2713 1/4/2005 40000 5.66 1292 Press Side 1 2722 1/5/2005 40000 0.5 210 Press Side 1 [tblDefects] AutoID ID DefCode DefQuantity 6660 2713 20 2 6661 2713 16 47 6673 2722 16 1 6674 2722 24 5 Sample2 [tbl Defect Count] ID Date PartNum SortTime TotalSort NCM_Num Containment PlantNum 2704 1/4/2005 AW060352 1.66 396 Press Side 1 2705 1/4/2005 AW060352 1.66 660 Press Side 1 2733 1/5/2005 AW060352 4 531 GP 12 1 2756 1/6/2005 AW060352 1.5 100 Press Side 1 2807 1/10/2005 AW060352 2.66 333 Press Side 1 2856 1/10/2005 AW060352 3 330 GP 12 1 2857 1/10/2005 AW060352 3 660 GP 12 1 2858 1/10/2005 AW060352 3 660 GP 12 1 2859 1/11/2005 AW060352 1.5 165 GP 12 1 2860 1/10/2005 AW060352 4 663 GP 12 1 2893 1/12/2005 AW060352 0.58 594 Press Side 1 2903 1/12/2005 AW060352 4 666 Press Side 1 2904 1/14/2005 AW060352 4.58 330 Press Side 1 2921 1/13/2005 AW060352 4 669 Press Side 1 2940 1/17/2005 AW060352 6.83 666 GP 12 1 2941 1/17/2005 AW060352 1 234 GP 12 1 2984 1/19/2005 AW060352 0.17 33 GP 12 1 3011 1/20/2005 AW060352 2 528 GP 12 1 3012 1/20/2005 AW060352 2.08 660 GP 12 1 [tblDefects] AutoID ID DefCode DefQuantity 6693 2733 26 1 6694 2733 24 2 6726 2756 24 1 6727 2756 25 1 6728 2756 26 1 6772 2807 7 1 6773 2807 24 1 6774 2807 26 1 6866 2859 24 3 6867 2860 26 1 6868 2860 7 1 6869 2860 24 1 6924 2903 24 1 6925 2903 15 3 6926 2903 20 2 6927 2904 20 13 6951 2921 26 9 6977 2940 7 1 6978 2940 26 6 6979 2941 15 2 6980 2941 20 1 7034 2984 7 1 when the query that i have now is run, the start date, end date, and part number are asked for (for Sample 1) and i get the following answer for the date range of 1/1/05 - 1/20/05 and part number 40000. PartNum SumOfTotalSort SumOfSum Of DefQuantity DefCode 40000 1502 48 16 40000 1292 2 20 40000 210 5 24 On the report i am getting a total of SumOfTotalSort of 3004 if i use a running sum and a total of 1502 if i don't use a running sum. the totals for the sumofsum of DefQuantity of 55 is correct. for sample 2 i get (for same date range but part number AW060352) PartNum SumOfTotalSort SumOfSum Of DefQuantity DefCode AW060352 4488 AW060352 2962 19 26 AW060352 2458 9 24 AW060352 1695 4 7 AW060352 1230 16 20 AW060352 900 5 15 AW060352 100 1 25 on the report i am getting the correct answer for the total sorted of 13,833 only when i am running it as a =Sum([SumOfTotalSort]). if i am not running as a =Sum([SumOfTotalSort]), then i get an answer of 4,488. which is the same amount as the amount with null values for defcode and defquantity. hope this helps. "Duane Hookom" wrote: You gave us the two "quizzes" but forgot the answer key! Why would you not tell us in each example what you calculate the total to be? How about some sample records? -- Duane Hookom MS Access MVP "jkendrick75" wrote in message ... I am needing a query to power a report that my boss wants done by this friday. i have been working on this report for a couple of weeks now, without getting anywhere. this is what i have for my tables. Parent table: [tbl Defect Count] ID (autonumber; PK) Date Part Number SortTime TotalSort NCM_Num Containment PlantNum Child Table: [tblDefects] AutoID (autonumber; PK) ID (linked to [tbl Defect Count].[ID]) DefCode DefQuantity the relation ship between these two tables is a 1 to many relationship. for 1 record in [tbl Defect Count] there could be anywhere from 1 to 27 records in [tblDefects], or no record at all. what my problem is currently is if there are more records (for a given part within a given date range) in [tbl Defect Count] than there are records (records that correspond to an ID in [tbl Defect Count]) in [tblDefects] i need to have the totals box in the footer of the report set for a running sum. however, if there are more records (for a given part within a given date range) in [tblDefects] than there are records (records that correspond to an ID in [tbl Defect Count]) in [tbl Defect Count] i need to have the totals box in the footer of the report set for a non-running sum. example: [tbl Defect Count] has 2 records (or 2 ID numbers) of a part within a 1 month date range. however [tblDefects] has a total of 3 records between the two records in [tbl Defect Count]. i need to have my totals box in the report footer set as a non running sum, otherwise my total is twice than what it should be. example 2: [tbl Defect Count] has 8 records (or 8 ID numbers) for a part within a 1 month date range. [tblDefects] has a total of 6 records between the 8 ID numbers in [tbl Defect Count] for the date range. I need to have my totals box in the report footer set as a running sum to count all records and give me a correct total. any help in this matter will be greatly appreciated. thanks in advance |
#6
|
|||
|
|||
ok, i apologize for such long posts. just trying to give you actual data
within my tables. thought that perhaps with actual data from the tables and actual results from the reports, it would help you get a better idea of what i am trying to do and what is actually happening. anyways. Sample 1 [tbl Defect Count] ID Date PartNum TotalSort 2713 1/4/2005 40000 1292 2722 1/5/2005 40000 210 [tblDefects] AutoID ID DefCode DefQuantity 6660 2713 20 2 6661 2713 16 47 6673 2722 16 1 6674 2722 24 5 when the report that i have now is run, the start date, end date, and part number are asked for (for Sample 1) and i get the following answer for the date range of 1/4/05 - 1/5/05 and part number 40000. i am getting a total of SumOfTotalSort of 3004 if i use a running sum and a total of 1502 if i don't use a running sum. the totals for the sumofsum of DefQuantity of 55 is correct. Sample2 [tbl Defect Count] ID Date PartNum TotalSort 2704 1/4/2005 AW060352 396 2705 1/4/2005 AW060352 660 2733 1/5/2005 AW060352 531 2756 1/6/2005 AW060352 100 [tblDefects] AutoID ID DefCode DefQuantity 6693 2733 26 1 6694 2733 24 2 6726 2756 24 1 6727 2756 25 1 6728 2756 26 1 when the report that i have now is run, the start date, end date, and part number are asked for (for Sample 2) and i get the following answer for the date range of 1/4/05 - 1/5/05 and part number AW060352. i am getting a total of SumOfTotalSort of 2,418 if i use a running sum and a total of 1,056 if i don't use a running sum. when manually adding up the numbers i am getting a total 1,687. the total for the sumofsum of DefQuantity of 6 is correct. hope all this makes sense to you. "Duane Hookom" wrote: I won't even try to read, understand, copy to tables, reformat,... your long examples. I doubt that SortTime and some other fields have any bearing on your issue. Please try to provide only a couple main records and no more than 10 detailed records and be accurate so we don't have to look back and forth between two emails. I have put a lot of time into attempting to do your work in the past (as have many others). Please don't make it so darned hard. You should be able to describe and illustrate you needs in much less complexity. -- Duane Hookom MS Access MVP "jkendrick75" wrote in message ... ok no prob. the following will be samples of data that i am using in particular. Sample 1 [tbl Defect Count] ID Date PartNum SortTime TotalSort NCM_Num Cont PlantNum 2713 1/4/2005 40000 5.66 1292 Press Side 1 2722 1/5/2005 40000 0.5 210 Press Side 1 [tblDefects] AutoID ID DefCode DefQuantity 6660 2713 20 2 6661 2713 16 47 6673 2722 16 1 6674 2722 24 5 Sample2 [tbl Defect Count] ID Date PartNum SortTime TotalSort NCM_Num Containment PlantNum 2704 1/4/2005 AW060352 1.66 396 Press Side 1 2705 1/4/2005 AW060352 1.66 660 Press Side 1 2733 1/5/2005 AW060352 4 531 GP 12 1 2756 1/6/2005 AW060352 1.5 100 Press Side 1 2807 1/10/2005 AW060352 2.66 333 Press Side 1 2856 1/10/2005 AW060352 3 330 GP 12 1 2857 1/10/2005 AW060352 3 660 GP 12 1 2858 1/10/2005 AW060352 3 660 GP 12 1 2859 1/11/2005 AW060352 1.5 165 GP 12 1 2860 1/10/2005 AW060352 4 663 GP 12 1 2893 1/12/2005 AW060352 0.58 594 Press Side 1 2903 1/12/2005 AW060352 4 666 Press Side 1 2904 1/14/2005 AW060352 4.58 330 Press Side 1 2921 1/13/2005 AW060352 4 669 Press Side 1 2940 1/17/2005 AW060352 6.83 666 GP 12 1 2941 1/17/2005 AW060352 1 234 GP 12 1 2984 1/19/2005 AW060352 0.17 33 GP 12 1 3011 1/20/2005 AW060352 2 528 GP 12 1 3012 1/20/2005 AW060352 2.08 660 GP 12 1 [tblDefects] AutoID ID DefCode DefQuantity 6693 2733 26 1 6694 2733 24 2 6726 2756 24 1 6727 2756 25 1 6728 2756 26 1 6772 2807 7 1 6773 2807 24 1 6774 2807 26 1 6866 2859 24 3 6867 2860 26 1 6868 2860 7 1 6869 2860 24 1 6924 2903 24 1 6925 2903 15 3 6926 2903 20 2 6927 2904 20 13 6951 2921 26 9 6977 2940 7 1 6978 2940 26 6 6979 2941 15 2 6980 2941 20 1 7034 2984 7 1 when the query that i have now is run, the start date, end date, and part number are asked for (for Sample 1) and i get the following answer for the date range of 1/1/05 - 1/20/05 and part number 40000. PartNum SumOfTotalSort SumOfSum Of DefQuantity DefCode 40000 1502 48 16 40000 1292 2 20 40000 210 5 24 On the report i am getting a total of SumOfTotalSort of 3004 if i use a running sum and a total of 1502 if i don't use a running sum. the totals for the sumofsum of DefQuantity of 55 is correct. for sample 2 i get (for same date range but part number AW060352) PartNum SumOfTotalSort SumOfSum Of DefQuantity DefCode AW060352 4488 AW060352 2962 19 26 AW060352 2458 9 24 AW060352 1695 4 7 AW060352 1230 16 20 AW060352 900 5 15 AW060352 100 1 25 on the report i am getting the correct answer for the total sorted of 13,833 only when i am running it as a =Sum([SumOfTotalSort]). if i am not running as a =Sum([SumOfTotalSort]), then i get an answer of 4,488. which is the same amount as the amount with null values for defcode and defquantity. hope this helps. "Duane Hookom" wrote: You gave us the two "quizzes" but forgot the answer key! Why would you not tell us in each example what you calculate the total to be? How about some sample records? -- Duane Hookom MS Access MVP "jkendrick75" wrote in message ... I am needing a query to power a report that my boss wants done by this friday. i have been working on this report for a couple of weeks now, without getting anywhere. this is what i have for my tables. Parent table: [tbl Defect Count] ID (autonumber; PK) Date Part Number SortTime TotalSort NCM_Num Containment PlantNum Child Table: [tblDefects] AutoID (autonumber; PK) ID (linked to [tbl Defect Count].[ID]) DefCode DefQuantity the relation ship between these two tables is a 1 to many relationship. for 1 record in [tbl Defect Count] there could be anywhere from 1 to 27 records in [tblDefects], or no record at all. what my problem is currently is if there are more records (for a given part within a given date range) in [tbl Defect Count] than there are records (records that correspond to an ID in [tbl Defect Count]) in [tblDefects] i need to have the totals box in the footer of the report set for a running sum. however, if there are more records (for a given part within a given date range) in [tblDefects] than there are records (records that correspond to an ID in [tbl Defect Count]) in [tbl Defect Count] i need to have the totals box in the footer of the report set for a non-running sum. example: [tbl Defect Count] has 2 records (or 2 ID numbers) of a part within a 1 month date range. however [tblDefects] has a total of 3 records between the two records in [tbl Defect Count]. i need to have my totals box in the report footer set as a non running sum, otherwise my total is twice than what it should be. example 2: [tbl Defect Count] has 8 records (or 8 ID numbers) for a part within a 1 month date range. [tblDefects] has a total of 6 records between the 8 ID numbers in [tbl Defect Count] for the date range. I need to have my totals box in the report footer set as a running sum to count all records and give me a correct total. any help in this matter will be greatly appreciated. thanks in advance |
#7
|
|||
|
|||
When you do your data entry, are you entering data into a form based on a
query of both tables or of a Main Form based on tbl Defect Count and a subform based on tblDefects? I would expect a main form and subform. The same model should be used in your report. Make a main report based on tblDefectCount and a subreport based on tblDefects.If you want, you can base your main report on a totals query like: SELECT tblDefectCount.ID, tblDefectCount.Date, tblDefectCount.PartNum, tblDefectCount.TotalSort, Sum(tblDefects.DefQuantity) AS SumOfDefQuantity FROM tblDefectCount INNER JOIN tblDefects ON tblDefectCount.ID = tblDefects.ID GROUP BY tblDefectCount.ID, tblDefectCount.Date, tblDefectCount.PartNum, tblDefectCount.TotalSort; -- Duane Hookom MS Access MVP "jkendrick75" wrote in message ... ok, i apologize for such long posts. just trying to give you actual data within my tables. thought that perhaps with actual data from the tables and actual results from the reports, it would help you get a better idea of what i am trying to do and what is actually happening. anyways. Sample 1 [tbl Defect Count] ID Date PartNum TotalSort 2713 1/4/2005 40000 1292 2722 1/5/2005 40000 210 [tblDefects] AutoID ID DefCode DefQuantity 6660 2713 20 2 6661 2713 16 47 6673 2722 16 1 6674 2722 24 5 when the report that i have now is run, the start date, end date, and part number are asked for (for Sample 1) and i get the following answer for the date range of 1/4/05 - 1/5/05 and part number 40000. i am getting a total of SumOfTotalSort of 3004 if i use a running sum and a total of 1502 if i don't use a running sum. the totals for the sumofsum of DefQuantity of 55 is correct. Sample2 [tbl Defect Count] ID Date PartNum TotalSort 2704 1/4/2005 AW060352 396 2705 1/4/2005 AW060352 660 2733 1/5/2005 AW060352 531 2756 1/6/2005 AW060352 100 [tblDefects] AutoID ID DefCode DefQuantity 6693 2733 26 1 6694 2733 24 2 6726 2756 24 1 6727 2756 25 1 6728 2756 26 1 when the report that i have now is run, the start date, end date, and part number are asked for (for Sample 2) and i get the following answer for the date range of 1/4/05 - 1/5/05 and part number AW060352. i am getting a total of SumOfTotalSort of 2,418 if i use a running sum and a total of 1,056 if i don't use a running sum. when manually adding up the numbers i am getting a total 1,687. the total for the sumofsum of DefQuantity of 6 is correct. hope all this makes sense to you. "Duane Hookom" wrote: I won't even try to read, understand, copy to tables, reformat,... your long examples. I doubt that SortTime and some other fields have any bearing on your issue. Please try to provide only a couple main records and no more than 10 detailed records and be accurate so we don't have to look back and forth between two emails. I have put a lot of time into attempting to do your work in the past (as have many others). Please don't make it so darned hard. You should be able to describe and illustrate you needs in much less complexity. -- Duane Hookom MS Access MVP "jkendrick75" wrote in message ... ok no prob. the following will be samples of data that i am using in particular. Sample 1 [tbl Defect Count] ID Date PartNum SortTime TotalSort NCM_Num Cont PlantNum 2713 1/4/2005 40000 5.66 1292 Press Side 1 2722 1/5/2005 40000 0.5 210 Press Side 1 [tblDefects] AutoID ID DefCode DefQuantity 6660 2713 20 2 6661 2713 16 47 6673 2722 16 1 6674 2722 24 5 Sample2 [tbl Defect Count] ID Date PartNum SortTime TotalSort NCM_Num Containment PlantNum 2704 1/4/2005 AW060352 1.66 396 Press Side 1 2705 1/4/2005 AW060352 1.66 660 Press Side 1 2733 1/5/2005 AW060352 4 531 GP 12 1 2756 1/6/2005 AW060352 1.5 100 Press Side 1 2807 1/10/2005 AW060352 2.66 333 Press Side 1 2856 1/10/2005 AW060352 3 330 GP 12 1 2857 1/10/2005 AW060352 3 660 GP 12 1 2858 1/10/2005 AW060352 3 660 GP 12 1 2859 1/11/2005 AW060352 1.5 165 GP 12 1 2860 1/10/2005 AW060352 4 663 GP 12 1 2893 1/12/2005 AW060352 0.58 594 Press Side 1 2903 1/12/2005 AW060352 4 666 Press Side 1 2904 1/14/2005 AW060352 4.58 330 Press Side 1 2921 1/13/2005 AW060352 4 669 Press Side 1 2940 1/17/2005 AW060352 6.83 666 GP 12 1 2941 1/17/2005 AW060352 1 234 GP 12 1 2984 1/19/2005 AW060352 0.17 33 GP 12 1 3011 1/20/2005 AW060352 2 528 GP 12 1 3012 1/20/2005 AW060352 2.08 660 GP 12 1 [tblDefects] AutoID ID DefCode DefQuantity 6693 2733 26 1 6694 2733 24 2 6726 2756 24 1 6727 2756 25 1 6728 2756 26 1 6772 2807 7 1 6773 2807 24 1 6774 2807 26 1 6866 2859 24 3 6867 2860 26 1 6868 2860 7 1 6869 2860 24 1 6924 2903 24 1 6925 2903 15 3 6926 2903 20 2 6927 2904 20 13 6951 2921 26 9 6977 2940 7 1 6978 2940 26 6 6979 2941 15 2 6980 2941 20 1 7034 2984 7 1 when the query that i have now is run, the start date, end date, and part number are asked for (for Sample 1) and i get the following answer for the date range of 1/1/05 - 1/20/05 and part number 40000. PartNum SumOfTotalSort SumOfSum Of DefQuantity DefCode 40000 1502 48 16 40000 1292 2 20 40000 210 5 24 On the report i am getting a total of SumOfTotalSort of 3004 if i use a running sum and a total of 1502 if i don't use a running sum. the totals for the sumofsum of DefQuantity of 55 is correct. for sample 2 i get (for same date range but part number AW060352) PartNum SumOfTotalSort SumOfSum Of DefQuantity DefCode AW060352 4488 AW060352 2962 19 26 AW060352 2458 9 24 AW060352 1695 4 7 AW060352 1230 16 20 AW060352 900 5 15 AW060352 100 1 25 on the report i am getting the correct answer for the total sorted of 13,833 only when i am running it as a =Sum([SumOfTotalSort]). if i am not running as a =Sum([SumOfTotalSort]), then i get an answer of 4,488. which is the same amount as the amount with null values for defcode and defquantity. hope this helps. "Duane Hookom" wrote: You gave us the two "quizzes" but forgot the answer key! Why would you not tell us in each example what you calculate the total to be? How about some sample records? -- Duane Hookom MS Access MVP "jkendrick75" wrote in message ... I am needing a query to power a report that my boss wants done by this friday. i have been working on this report for a couple of weeks now, without getting anywhere. this is what i have for my tables. Parent table: [tbl Defect Count] ID (autonumber; PK) Date Part Number SortTime TotalSort NCM_Num Containment PlantNum Child Table: [tblDefects] AutoID (autonumber; PK) ID (linked to [tbl Defect Count].[ID]) DefCode DefQuantity the relation ship between these two tables is a 1 to many relationship. for 1 record in [tbl Defect Count] there could be anywhere from 1 to 27 records in [tblDefects], or no record at all. what my problem is currently is if there are more records (for a given part within a given date range) in [tbl Defect Count] than there are records (records that correspond to an ID in [tbl Defect Count]) in [tblDefects] i need to have the totals box in the footer of the report set for a running sum. however, if there are more records (for a given part within a given date range) in [tblDefects] than there are records (records that correspond to an ID in [tbl Defect Count]) in [tbl Defect Count] i need to have the totals box in the footer of the report set for a non-running sum. example: [tbl Defect Count] has 2 records (or 2 ID numbers) of a part within a 1 month date range. however [tblDefects] has a total of 3 records between the two records in [tbl Defect Count]. i need to have my totals box in the report footer set as a non running sum, otherwise my total is twice than what it should be. example 2: [tbl Defect Count] has 8 records (or 8 ID numbers) for a part within a 1 month date range. [tblDefects] has a total of 6 records between the 8 ID numbers in [tbl Defect Count] for the date range. I need to have my totals box in the report footer set as a running sum to count all records and give me a correct total. any help in this matter will be greatly appreciated. thanks in advance |
#8
|
|||
|
|||
ok, have put that in now. but now for the summary portion of the report, i
am needing to have a total sorted number, and a total defects number. i am getting the total sorted number without a problem, but i am having trouble getting the total defects number. also, in the details section of the report, i am wanting it to show all of the same defect codes as 1 record. Part number of 40000, total sorted for each instance is 1292 and 210. for the 1292 instance there are two codes (code 16 with 47 defects, code 20 with 2 defects) and the 210 instance there are two codes (code 16 with 1 defect, code 24 with 5 defects) the details section should look like this 24 5 20 2 16 48 with a total defects of 55. right now my total defects box is in the main report with a data source of [Reports]![subqryDefTotal subreport]![sumofdefquantity] i did have a '=Sum( )' setup around the datasource at one time, but could not get this to work. thank you for your help so far. "Duane Hookom" wrote: When you do your data entry, are you entering data into a form based on a query of both tables or of a Main Form based on tbl Defect Count and a subform based on tblDefects? I would expect a main form and subform. The same model should be used in your report. Make a main report based on tblDefectCount and a subreport based on tblDefects.If you want, you can base your main report on a totals query like: SELECT tblDefectCount.ID, tblDefectCount.Date, tblDefectCount.PartNum, tblDefectCount.TotalSort, Sum(tblDefects.DefQuantity) AS SumOfDefQuantity FROM tblDefectCount INNER JOIN tblDefects ON tblDefectCount.ID = tblDefects.ID GROUP BY tblDefectCount.ID, tblDefectCount.Date, tblDefectCount.PartNum, tblDefectCount.TotalSort; -- Duane Hookom MS Access MVP "jkendrick75" wrote in message ... ok, i apologize for such long posts. just trying to give you actual data within my tables. thought that perhaps with actual data from the tables and actual results from the reports, it would help you get a better idea of what i am trying to do and what is actually happening. anyways. Sample 1 [tbl Defect Count] ID Date PartNum TotalSort 2713 1/4/2005 40000 1292 2722 1/5/2005 40000 210 [tblDefects] AutoID ID DefCode DefQuantity 6660 2713 20 2 6661 2713 16 47 6673 2722 16 1 6674 2722 24 5 when the report that i have now is run, the start date, end date, and part number are asked for (for Sample 1) and i get the following answer for the date range of 1/4/05 - 1/5/05 and part number 40000. i am getting a total of SumOfTotalSort of 3004 if i use a running sum and a total of 1502 if i don't use a running sum. the totals for the sumofsum of DefQuantity of 55 is correct. Sample2 [tbl Defect Count] ID Date PartNum TotalSort 2704 1/4/2005 AW060352 396 2705 1/4/2005 AW060352 660 2733 1/5/2005 AW060352 531 2756 1/6/2005 AW060352 100 [tblDefects] AutoID ID DefCode DefQuantity 6693 2733 26 1 6694 2733 24 2 6726 2756 24 1 6727 2756 25 1 6728 2756 26 1 when the report that i have now is run, the start date, end date, and part number are asked for (for Sample 2) and i get the following answer for the date range of 1/4/05 - 1/5/05 and part number AW060352. i am getting a total of SumOfTotalSort of 2,418 if i use a running sum and a total of 1,056 if i don't use a running sum. when manually adding up the numbers i am getting a total 1,687. the total for the sumofsum of DefQuantity of 6 is correct. hope all this makes sense to you. "Duane Hookom" wrote: I won't even try to read, understand, copy to tables, reformat,... your long examples. I doubt that SortTime and some other fields have any bearing on your issue. Please try to provide only a couple main records and no more than 10 detailed records and be accurate so we don't have to look back and forth between two emails. I have put a lot of time into attempting to do your work in the past (as have many others). Please don't make it so darned hard. You should be able to describe and illustrate you needs in much less complexity. -- Duane Hookom MS Access MVP "jkendrick75" wrote in message ... ok no prob. the following will be samples of data that i am using in particular. Sample 1 [tbl Defect Count] ID Date PartNum SortTime TotalSort NCM_Num Cont PlantNum 2713 1/4/2005 40000 5.66 1292 Press Side 1 2722 1/5/2005 40000 0.5 210 Press Side 1 [tblDefects] AutoID ID DefCode DefQuantity 6660 2713 20 2 6661 2713 16 47 6673 2722 16 1 6674 2722 24 5 Sample2 [tbl Defect Count] ID Date PartNum SortTime TotalSort NCM_Num Containment PlantNum 2704 1/4/2005 AW060352 1.66 396 Press Side 1 2705 1/4/2005 AW060352 1.66 660 Press Side 1 2733 1/5/2005 AW060352 4 531 GP 12 1 2756 1/6/2005 AW060352 1.5 100 Press Side 1 2807 1/10/2005 AW060352 2.66 333 Press Side 1 2856 1/10/2005 AW060352 3 330 GP 12 1 2857 1/10/2005 AW060352 3 660 GP 12 1 2858 1/10/2005 AW060352 3 660 GP 12 1 2859 1/11/2005 AW060352 1.5 165 GP 12 1 2860 1/10/2005 AW060352 4 663 GP 12 1 2893 1/12/2005 AW060352 0.58 594 Press Side 1 2903 1/12/2005 AW060352 4 666 Press Side 1 2904 1/14/2005 AW060352 4.58 330 Press Side 1 2921 1/13/2005 AW060352 4 669 Press Side 1 2940 1/17/2005 AW060352 6.83 666 GP 12 1 2941 1/17/2005 AW060352 1 234 GP 12 1 2984 1/19/2005 AW060352 0.17 33 GP 12 1 3011 1/20/2005 AW060352 2 528 GP 12 1 3012 1/20/2005 AW060352 2.08 660 GP 12 1 [tblDefects] AutoID ID DefCode DefQuantity 6693 2733 26 1 6694 2733 24 2 6726 2756 24 1 6727 2756 25 1 6728 2756 26 1 6772 2807 7 1 6773 2807 24 1 6774 2807 26 1 6866 2859 24 3 6867 2860 26 1 6868 2860 7 1 6869 2860 24 1 6924 2903 24 1 6925 2903 15 3 6926 2903 20 2 6927 2904 20 13 6951 2921 26 9 6977 2940 7 1 6978 2940 26 6 6979 2941 15 2 6980 2941 20 1 7034 2984 7 1 when the query that i have now is run, the start date, end date, and part number are asked for (for Sample 1) and i get the following answer for the date range of 1/1/05 - 1/20/05 and part number 40000. PartNum SumOfTotalSort SumOfSum Of DefQuantity DefCode 40000 1502 48 16 40000 1292 2 20 40000 210 5 24 On the report i am getting a total of SumOfTotalSort of 3004 if i use a running sum and a total of 1502 if i don't use a running sum. the totals for the sumofsum of DefQuantity of 55 is correct. for sample 2 i get (for same date range but part number AW060352) PartNum SumOfTotalSort SumOfSum Of DefQuantity DefCode AW060352 4488 AW060352 2962 19 26 AW060352 2458 9 24 AW060352 1695 4 7 AW060352 1230 16 20 AW060352 900 5 15 AW060352 100 1 25 on the report i am getting the correct answer for the total sorted of 13,833 only when i am running it as a =Sum([SumOfTotalSort]). if i am not running as a =Sum([SumOfTotalSort]), then i get an answer of 4,488. which is the same amount as the amount with null values for defcode and defquantity. hope this helps. "Duane Hookom" wrote: You gave us the two "quizzes" but forgot the answer key! Why would you not tell us in each example what you calculate the total to be? How about some sample records? -- Duane Hookom MS Access MVP "jkendrick75" wrote in message ... I am needing a query to power a report that my boss wants done by this friday. i have been working on this report for a couple of weeks now, without getting anywhere. this is what i have for my tables. Parent table: [tbl Defect Count] ID (autonumber; PK) Date Part Number SortTime TotalSort NCM_Num Containment PlantNum Child Table: [tblDefects] AutoID (autonumber; PK) ID (linked to [tbl Defect Count].[ID]) DefCode DefQuantity the relation ship between these two tables is a 1 to many relationship. for 1 record in [tbl Defect Count] there could be anywhere from 1 to 27 records in [tblDefects], or no record at all. what my problem is currently is if there are more records (for a given part within a given date range) in [tbl Defect Count] than there are records (records that correspond to an ID in [tbl Defect Count]) in [tblDefects] i need to have the totals box in the footer of the report set for a running sum. however, if there are more records (for a given part within a given date range) in [tblDefects] than there are records (records that correspond to an ID in [tbl Defect Count]) in [tbl Defect Count] i need to have the totals box in the footer of the report set for a non-running sum. example: [tbl Defect Count] has 2 records (or 2 ID numbers) of a part within a 1 month date range. however [tblDefects] has a total of 3 records between the two records in [tbl Defect Count]. i need to have my totals box in the report footer set as a non running sum, otherwise my total is twice than what it should be. example 2: [tbl Defect Count] has 8 records (or 8 ID numbers) for a part within a 1 month date range. [tblDefects] has a total of 6 records between the 8 ID numbers in [tbl Defect Count] for the date range. I need to have my totals box in the report footer set as a running sum to count all records and give me a correct total. any help in this matter will be greatly appreciated. thanks in advance |
#9
|
|||
|
|||
Did you try what I suggested in my last paragraph and SQL? This would place
the total defects number in the record source of the main report where summing would be a piece of cake. If you want to combine all the codes for a part number together, then open the footer for Partnum and move your subreport to the footer. Change the record source of the subreport to: SELECT tblDefectCount.PartNum, tblDefects.DefCode, Sum(tblDefects.DefQuantity) AS SumOfDefQuantity FROM tblDefectCount INNER JOIN tblDefects ON tblDefectCount.ID = tblDefects.ID GROUP BY tblDefectCount.PartNum, tblDefects.DefCode; Change controls on your subreport to match your fields. Then set your link master/child properties of the subreport to PartNum. -- Duane Hookom MS Access MVP "jkendrick75" wrote in message ... ok, have put that in now. but now for the summary portion of the report, i am needing to have a total sorted number, and a total defects number. i am getting the total sorted number without a problem, but i am having trouble getting the total defects number. also, in the details section of the report, i am wanting it to show all of the same defect codes as 1 record. Part number of 40000, total sorted for each instance is 1292 and 210. for the 1292 instance there are two codes (code 16 with 47 defects, code 20 with 2 defects) and the 210 instance there are two codes (code 16 with 1 defect, code 24 with 5 defects) the details section should look like this 24 5 20 2 16 48 with a total defects of 55. right now my total defects box is in the main report with a data source of [Reports]![subqryDefTotal subreport]![sumofdefquantity] i did have a '=Sum( )' setup around the datasource at one time, but could not get this to work. thank you for your help so far. "Duane Hookom" wrote: When you do your data entry, are you entering data into a form based on a query of both tables or of a Main Form based on tbl Defect Count and a subform based on tblDefects? I would expect a main form and subform. The same model should be used in your report. Make a main report based on tblDefectCount and a subreport based on tblDefects.If you want, you can base your main report on a totals query like: SELECT tblDefectCount.ID, tblDefectCount.Date, tblDefectCount.PartNum, tblDefectCount.TotalSort, Sum(tblDefects.DefQuantity) AS SumOfDefQuantity FROM tblDefectCount INNER JOIN tblDefects ON tblDefectCount.ID = tblDefects.ID GROUP BY tblDefectCount.ID, tblDefectCount.Date, tblDefectCount.PartNum, tblDefectCount.TotalSort; -- Duane Hookom MS Access MVP "jkendrick75" wrote in message ... ok, i apologize for such long posts. just trying to give you actual data within my tables. thought that perhaps with actual data from the tables and actual results from the reports, it would help you get a better idea of what i am trying to do and what is actually happening. anyways. Sample 1 [tbl Defect Count] ID Date PartNum TotalSort 2713 1/4/2005 40000 1292 2722 1/5/2005 40000 210 [tblDefects] AutoID ID DefCode DefQuantity 6660 2713 20 2 6661 2713 16 47 6673 2722 16 1 6674 2722 24 5 when the report that i have now is run, the start date, end date, and part number are asked for (for Sample 1) and i get the following answer for the date range of 1/4/05 - 1/5/05 and part number 40000. i am getting a total of SumOfTotalSort of 3004 if i use a running sum and a total of 1502 if i don't use a running sum. the totals for the sumofsum of DefQuantity of 55 is correct. Sample2 [tbl Defect Count] ID Date PartNum TotalSort 2704 1/4/2005 AW060352 396 2705 1/4/2005 AW060352 660 2733 1/5/2005 AW060352 531 2756 1/6/2005 AW060352 100 [tblDefects] AutoID ID DefCode DefQuantity 6693 2733 26 1 6694 2733 24 2 6726 2756 24 1 6727 2756 25 1 6728 2756 26 1 when the report that i have now is run, the start date, end date, and part number are asked for (for Sample 2) and i get the following answer for the date range of 1/4/05 - 1/5/05 and part number AW060352. i am getting a total of SumOfTotalSort of 2,418 if i use a running sum and a total of 1,056 if i don't use a running sum. when manually adding up the numbers i am getting a total 1,687. the total for the sumofsum of DefQuantity of 6 is correct. hope all this makes sense to you. "Duane Hookom" wrote: I won't even try to read, understand, copy to tables, reformat,... your long examples. I doubt that SortTime and some other fields have any bearing on your issue. Please try to provide only a couple main records and no more than 10 detailed records and be accurate so we don't have to look back and forth between two emails. I have put a lot of time into attempting to do your work in the past (as have many others). Please don't make it so darned hard. You should be able to describe and illustrate you needs in much less complexity. -- Duane Hookom MS Access MVP "jkendrick75" wrote in message ... ok no prob. the following will be samples of data that i am using in particular. Sample 1 [tbl Defect Count] ID Date PartNum SortTime TotalSort NCM_Num Cont PlantNum 2713 1/4/2005 40000 5.66 1292 Press Side 1 2722 1/5/2005 40000 0.5 210 Press Side 1 [tblDefects] AutoID ID DefCode DefQuantity 6660 2713 20 2 6661 2713 16 47 6673 2722 16 1 6674 2722 24 5 Sample2 [tbl Defect Count] ID Date PartNum SortTime TotalSort NCM_Num Containment PlantNum 2704 1/4/2005 AW060352 1.66 396 Press Side 1 2705 1/4/2005 AW060352 1.66 660 Press Side 1 2733 1/5/2005 AW060352 4 531 GP 12 1 2756 1/6/2005 AW060352 1.5 100 Press Side 1 2807 1/10/2005 AW060352 2.66 333 Press Side 1 2856 1/10/2005 AW060352 3 330 GP 12 1 2857 1/10/2005 AW060352 3 660 GP 12 1 2858 1/10/2005 AW060352 3 660 GP 12 1 2859 1/11/2005 AW060352 1.5 165 GP 12 1 2860 1/10/2005 AW060352 4 663 GP 12 1 2893 1/12/2005 AW060352 0.58 594 Press Side 1 2903 1/12/2005 AW060352 4 666 Press Side 1 2904 1/14/2005 AW060352 4.58 330 Press Side 1 2921 1/13/2005 AW060352 4 669 Press Side 1 2940 1/17/2005 AW060352 6.83 666 GP 12 1 2941 1/17/2005 AW060352 1 234 GP 12 1 2984 1/19/2005 AW060352 0.17 33 GP 12 1 3011 1/20/2005 AW060352 2 528 GP 12 1 3012 1/20/2005 AW060352 2.08 660 GP 12 1 [tblDefects] AutoID ID DefCode DefQuantity 6693 2733 26 1 6694 2733 24 2 6726 2756 24 1 6727 2756 25 1 6728 2756 26 1 6772 2807 7 1 6773 2807 24 1 6774 2807 26 1 6866 2859 24 3 6867 2860 26 1 6868 2860 7 1 6869 2860 24 1 6924 2903 24 1 6925 2903 15 3 6926 2903 20 2 6927 2904 20 13 6951 2921 26 9 6977 2940 7 1 6978 2940 26 6 6979 2941 15 2 6980 2941 20 1 7034 2984 7 1 when the query that i have now is run, the start date, end date, and part number are asked for (for Sample 1) and i get the following answer for the date range of 1/1/05 - 1/20/05 and part number 40000. PartNum SumOfTotalSort SumOfSum Of DefQuantity DefCode 40000 1502 48 16 40000 1292 2 20 40000 210 5 24 On the report i am getting a total of SumOfTotalSort of 3004 if i use a running sum and a total of 1502 if i don't use a running sum. the totals for the sumofsum of DefQuantity of 55 is correct. for sample 2 i get (for same date range but part number AW060352) PartNum SumOfTotalSort SumOfSum Of DefQuantity DefCode AW060352 4488 AW060352 2962 19 26 AW060352 2458 9 24 AW060352 1695 4 7 AW060352 1230 16 20 AW060352 900 5 15 AW060352 100 1 25 on the report i am getting the correct answer for the total sorted of 13,833 only when i am running it as a =Sum([SumOfTotalSort]). if i am not running as a =Sum([SumOfTotalSort]), then i get an answer of 4,488. which is the same amount as the amount with null values for defcode and defquantity. hope this helps. "Duane Hookom" wrote: You gave us the two "quizzes" but forgot the answer key! Why would you not tell us in each example what you calculate the total to be? How about some sample records? -- Duane Hookom MS Access MVP "jkendrick75" wrote in message ... I am needing a query to power a report that my boss wants done by this friday. i have been working on this report for a couple of weeks now, without getting anywhere. this is what i have for my tables. Parent table: [tbl Defect Count] ID (autonumber; PK) Date Part Number SortTime TotalSort NCM_Num Containment PlantNum Child Table: [tblDefects] AutoID (autonumber; PK) ID (linked to [tbl Defect Count].[ID]) DefCode DefQuantity the relation ship between these two tables is a 1 to many relationship. for 1 record in [tbl Defect Count] there could be anywhere from 1 to 27 records in [tblDefects], or no record at all. what my problem is currently is if there are more records (for a given part within a given date range) in [tbl Defect Count] than there are records (records that correspond to an ID in [tbl Defect Count]) in [tblDefects] i need to have the totals box in the footer of the report set for a running sum. however, if there are more records (for a given part within a given date range) in [tblDefects] than there are records (records that correspond to an ID in [tbl Defect Count]) in [tbl Defect Count] i need to have the totals box in the footer of the report set for a non-running sum. example: [tbl Defect Count] has 2 records (or 2 ID numbers) of a part within a 1 month date range. however [tblDefects] has a total of 3 records between the two records in [tbl Defect Count]. i need to have my totals box in the report footer set as a non running sum, otherwise my total is twice than what it should be. example 2: [tbl Defect Count] has 8 records (or 8 ID numbers) for a part within a 1 month date range. [tblDefects] has a total of 6 records between the 8 ID numbers in [tbl Defect Count] for the date range. I need to have my totals box in the report footer set as a running sum to count all records and give me a correct total. any help in this matter will be greatly appreciated. thanks in advance |
#10
|
|||
|
|||
yes, i did try what you had suggested. after incorporating your last post,
this is what i get. Defect Code Defect Quantity 16 1 24 5 16 47 20 2 Total Parts Ran: 210 Total Defects/Part: 6 i have even tried to do a running sum for the two totals. i get the same answer. if i move the subreport to the footer section of the report, i get several pop up boxes asking for the part number. as before, not sure where to go from here. "Duane Hookom" wrote: Did you try what I suggested in my last paragraph and SQL? This would place the total defects number in the record source of the main report where summing would be a piece of cake. If you want to combine all the codes for a part number together, then open the footer for Partnum and move your subreport to the footer. Change the record source of the subreport to: SELECT tblDefectCount.PartNum, tblDefects.DefCode, Sum(tblDefects.DefQuantity) AS SumOfDefQuantity FROM tblDefectCount INNER JOIN tblDefects ON tblDefectCount.ID = tblDefects.ID GROUP BY tblDefectCount.PartNum, tblDefects.DefCode; Change controls on your subreport to match your fields. Then set your link master/child properties of the subreport to PartNum. -- Duane Hookom MS Access MVP "jkendrick75" wrote in message ... ok, have put that in now. but now for the summary portion of the report, i am needing to have a total sorted number, and a total defects number. i am getting the total sorted number without a problem, but i am having trouble getting the total defects number. also, in the details section of the report, i am wanting it to show all of the same defect codes as 1 record. Part number of 40000, total sorted for each instance is 1292 and 210. for the 1292 instance there are two codes (code 16 with 47 defects, code 20 with 2 defects) and the 210 instance there are two codes (code 16 with 1 defect, code 24 with 5 defects) the details section should look like this 24 5 20 2 16 48 with a total defects of 55. right now my total defects box is in the main report with a data source of [Reports]![subqryDefTotal subreport]![sumofdefquantity] i did have a '=Sum( )' setup around the datasource at one time, but could not get this to work. thank you for your help so far. "Duane Hookom" wrote: When you do your data entry, are you entering data into a form based on a query of both tables or of a Main Form based on tbl Defect Count and a subform based on tblDefects? I would expect a main form and subform. The same model should be used in your report. Make a main report based on tblDefectCount and a subreport based on tblDefects.If you want, you can base your main report on a totals query like: SELECT tblDefectCount.ID, tblDefectCount.Date, tblDefectCount.PartNum, tblDefectCount.TotalSort, Sum(tblDefects.DefQuantity) AS SumOfDefQuantity FROM tblDefectCount INNER JOIN tblDefects ON tblDefectCount.ID = tblDefects.ID GROUP BY tblDefectCount.ID, tblDefectCount.Date, tblDefectCount.PartNum, tblDefectCount.TotalSort; -- Duane Hookom MS Access MVP "jkendrick75" wrote in message ... ok, i apologize for such long posts. just trying to give you actual data within my tables. thought that perhaps with actual data from the tables and actual results from the reports, it would help you get a better idea of what i am trying to do and what is actually happening. anyways. Sample 1 [tbl Defect Count] ID Date PartNum TotalSort 2713 1/4/2005 40000 1292 2722 1/5/2005 40000 210 [tblDefects] AutoID ID DefCode DefQuantity 6660 2713 20 2 6661 2713 16 47 6673 2722 16 1 6674 2722 24 5 when the report that i have now is run, the start date, end date, and part number are asked for (for Sample 1) and i get the following answer for the date range of 1/4/05 - 1/5/05 and part number 40000. i am getting a total of SumOfTotalSort of 3004 if i use a running sum and a total of 1502 if i don't use a running sum. the totals for the sumofsum of DefQuantity of 55 is correct. Sample2 [tbl Defect Count] ID Date PartNum TotalSort 2704 1/4/2005 AW060352 396 2705 1/4/2005 AW060352 660 2733 1/5/2005 AW060352 531 2756 1/6/2005 AW060352 100 [tblDefects] AutoID ID DefCode DefQuantity 6693 2733 26 1 6694 2733 24 2 6726 2756 24 1 6727 2756 25 1 6728 2756 26 1 when the report that i have now is run, the start date, end date, and part number are asked for (for Sample 2) and i get the following answer for the date range of 1/4/05 - 1/5/05 and part number AW060352. i am getting a total of SumOfTotalSort of 2,418 if i use a running sum and a total of 1,056 if i don't use a running sum. when manually adding up the numbers i am getting a total 1,687. the total for the sumofsum of DefQuantity of 6 is correct. hope all this makes sense to you. "Duane Hookom" wrote: I won't even try to read, understand, copy to tables, reformat,... your long examples. I doubt that SortTime and some other fields have any bearing on your issue. Please try to provide only a couple main records and no more than 10 detailed records and be accurate so we don't have to look back and forth between two emails. I have put a lot of time into attempting to do your work in the past (as have many others). Please don't make it so darned hard. You should be able to describe and illustrate you needs in much less complexity. -- Duane Hookom MS Access MVP "jkendrick75" wrote in message ... ok no prob. the following will be samples of data that i am using in particular. Sample 1 [tbl Defect Count] ID Date PartNum SortTime TotalSort NCM_Num Cont PlantNum 2713 1/4/2005 40000 5.66 1292 Press Side 1 2722 1/5/2005 40000 0.5 210 Press Side 1 [tblDefects] AutoID ID DefCode DefQuantity 6660 2713 20 2 6661 2713 16 47 6673 2722 16 1 6674 2722 24 5 Sample2 [tbl Defect Count] ID Date PartNum SortTime TotalSort NCM_Num Containment PlantNum 2704 1/4/2005 AW060352 1.66 396 Press Side 1 2705 1/4/2005 AW060352 1.66 660 Press Side 1 2733 1/5/2005 AW060352 4 531 GP 12 1 2756 1/6/2005 AW060352 1.5 100 Press Side 1 2807 1/10/2005 AW060352 2.66 333 Press Side 1 2856 1/10/2005 AW060352 3 330 GP 12 1 2857 1/10/2005 AW060352 3 660 GP 12 1 2858 1/10/2005 AW060352 3 660 GP 12 1 2859 1/11/2005 AW060352 1.5 165 GP 12 1 2860 1/10/2005 AW060352 4 663 GP 12 1 2893 1/12/2005 AW060352 0.58 594 Press Side 1 2903 1/12/2005 AW060352 4 666 Press Side 1 2904 1/14/2005 AW060352 4.58 330 Press Side 1 2921 1/13/2005 AW060352 4 669 Press Side 1 2940 1/17/2005 AW060352 6.83 666 GP 12 1 2941 1/17/2005 AW060352 1 234 GP 12 1 2984 1/19/2005 AW060352 0.17 33 GP 12 1 3011 1/20/2005 AW060352 2 528 GP 12 1 3012 1/20/2005 AW060352 2.08 660 GP 12 1 [tblDefects] AutoID ID DefCode DefQuantity 6693 2733 26 1 6694 2733 24 2 6726 2756 24 1 6727 2756 25 1 6728 2756 26 1 6772 2807 7 1 6773 2807 24 1 6774 2807 26 1 6866 2859 24 3 6867 2860 26 1 6868 2860 7 1 6869 2860 24 1 6924 2903 24 1 6925 2903 15 3 6926 2903 20 2 6927 2904 20 13 6951 2921 26 9 6977 2940 7 1 6978 2940 26 6 6979 2941 15 2 6980 2941 20 1 7034 2984 7 1 when the query that i have now is run, the start date, end date, and part number are asked for (for Sample 1) and i get the following answer for the date range of 1/1/05 - 1/20/05 and part number 40000. PartNum SumOfTotalSort SumOfSum Of DefQuantity DefCode 40000 1502 48 16 40000 1292 2 20 40000 210 5 24 On the report i am getting a total of SumOfTotalSort of 3004 if i use a running sum and a total of 1502 if i don't use a running sum. the totals for the sumofsum of DefQuantity of 55 is correct. for sample 2 i get (for same date range but part number AW060352) PartNum SumOfTotalSort SumOfSum Of DefQuantity DefCode AW060352 4488 AW060352 2962 19 26 AW060352 2458 9 24 AW060352 1695 4 7 AW060352 1230 16 20 AW060352 900 5 15 AW060352 100 1 25 on the report i am getting the correct answer for the total sorted of 13,833 only when i am running it as a =Sum([SumOfTotalSort]). if i am not running as a =Sum([SumOfTotalSort]), then i get an answer of 4,488. which is the same amount as the amount with null values for defcode and defquantity. hope this helps. "Duane Hookom" wrote: You gave us the two "quizzes" but forgot the answer key! Why would you not tell us in each example what you calculate the total to be? How about some sample records? -- Duane Hookom MS Access MVP "jkendrick75" wrote in message ... I am needing a query to power a report that my boss wants done by this friday. i have been working on this report for a couple of weeks now, without getting anywhere. this is what i have for my tables. Parent table: [tbl Defect Count] ID (autonumber; PK) Date Part Number SortTime TotalSort NCM_Num Containment PlantNum Child Table: [tblDefects] AutoID (autonumber; PK) ID (linked to [tbl Defect Count].[ID]) DefCode DefQuantity the relation ship between these two tables is a 1 to many relationship. for 1 record in [tbl Defect Count] there could be anywhere from 1 to 27 records in [tblDefects], or no record at all. what my problem is currently is if there are more records (for a given part within a given date range) in [tbl Defect Count] than there are records (records that correspond to an ID in [tbl Defect Count]) in [tblDefects] i need to have the totals box in the footer of the report set for a running sum. however, if there are more records (for a given part within a given date range) in [tblDefects] than there are records (records that correspond to an ID in [tbl Defect Count]) in [tbl Defect Count] i need to have the totals box in the footer of the report set for a non-running sum. example: [tbl Defect Count] has 2 records (or 2 ID numbers) of a part within a 1 month date range. however [tblDefects] has a total of 3 records between the two records in [tbl Defect Count]. i need to have my totals box in the report footer set as a non running sum, otherwise my total is twice than what it should be. example 2: [tbl Defect Count] has 8 records (or 8 ID numbers) for a part within a 1 month date range. [tblDefects] has a total of 6 records between the 8 ID numbers in [tbl Defect Count] for the date range. I need to have my totals box in the report footer set as a running sum to count all records and give me a correct total. any help in this matter will be greatly appreciated. thanks in advance |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Syntax needed to get needed reports | Frank Lueder | New Users | 15 | January 6th, 2005 08:39 AM |
Microsoft Office 2003, Addons, other 16,000 more CDs, [ no dongles, no activations, etc ... needed ! ] | athens.gr. | General Discussions | 1 | September 3rd, 2004 02:43 AM |
Rule when primary key is needed? | Roland Bengtsson | New Users | 7 | August 16th, 2004 09:15 PM |
Expanding Data As Needed | MT | General Discussion | 2 | July 1st, 2004 12:52 AM |
Keeping a Function Cell Blank Until It's Needed | barry a | Worksheet Functions | 1 | September 26th, 2003 06:41 PM |