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

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

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

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

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

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

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

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

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

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