incorrect sums in report using 2 tables

i am trying to create a report that, using a start and end date that is given
on a form, groups by part number and sums the number of parts sorted per part
for the given date range. this part works fine as the date, part number, and
the values for the total sorted are all in one table (tblDefect Count). if i
try to get the total number of defects per part for the given date range, i
get the correct number of defects but an incorrect number for the total
sorted. the number of defects comes from a second table (tblDefects), that
is tied to tblDefect Count by using an autogenerated key field called ID in
tblDefect Count. for every ID in tblDefect Count, there could be any where
from 0 to 27 records in tblDefects (there are 27 different defect codes and
each code has its own amount for defects for that code and part number.)

any help with this would be greatly appreciated as i have been working on
this for about a month now. if you need any thing clarified, please let me
know and i will do what i can. if you want to see a copy of the database,
let me know.

There are a couple methods for doing this. One is to create a text box in
the PartNumber(?) group header:
Name: txtTotalSorted
Control Source: [Total Sorted]
Running Sum: Over All
Visible: No
Then, add a text box to your report footer
Control Source: =txtTotalSorted

You could also set up a totals query based on [tblDefect Count] that returns
only one record with the sum of [Total Sorted] for the particular date
range. You could then add this query to your report's record source.

--
Duane Hookom
MS Access MVP
--

"jkendrick75" wrote in message
...
i am trying to create a report that, using a start and end date that is
given
on a form, groups by part number and sums the number of parts sorted per
part
for the given date range. this part works fine as the date, part number,
and
the values for the total sorted are all in one table (tblDefect Count).
if i
try to get the total number of defects per part for the given date range,
i
get the correct number of defects but an incorrect number for the total
sorted. the number of defects comes from a second table (tblDefects),
that
is tied to tblDefect Count by using an autogenerated key field called ID
in
tblDefect Count. for every ID in tblDefect Count, there could be any where
from 0 to 27 records in tblDefects (there are 27 different defect codes
and
each code has its own amount for defects for that code and part number.)

any help with this would be greatly appreciated as i have been working on
this for about a month now. if you need any thing clarified, please let
me
know and i will do what i can. if you want to see a copy of the database,
let me know.

that part works fine, just when i try to add in the total defects for that
part during the date range, then the total sorted number is more than what it
should be. what is happening is say for part 123, there were 100 parts
sorted (during a given date range). for that 100 parts sorted, there were 3
different defect codes (a with 2 defects, b with 3 defects, c with 4
defects). the total sorted should be 100 parts sorted (which i get if i
don't try to total the defects) with a total of 9 defects. what i am getting
is a total of 300 parts sorted and 9 defects. i have tried to write a query
for just the total sorted, then a query for just the total defects, and then
a query that uses the first 2 queries, but all that did was total all parts
sorted (grouped by the part number) in the entire database, along with all
defects for entire database. it was ignoring the date range and giving
totals for all parts, and then just did a one time total of all defects, and
assigned it to each part number.

thank you for responding so quickly, and any other ideas will be welcome.

"Duane Hookom" wrote:

There are a couple methods for doing this. One is to create a text box in
the PartNumber(?) group header:
Name: txtTotalSorted
Control Source: [Total Sorted]
Running Sum: Over All
Visible: No
Then, add a text box to your report footer
Control Source: =txtTotalSorted

You could also set up a totals query based on [tblDefect Count] that returns
only one record with the sum of [Total Sorted] for the particular date
range. You could then add this query to your report's record source.

--
Duane Hookom
MS Access MVP
--

"jkendrick75" wrote in message
...
i am trying to create a report that, using a start and end date that is
given
on a form, groups by part number and sums the number of parts sorted per
part
for the given date range. this part works fine as the date, part number,
and
the values for the total sorted are all in one table (tblDefect Count).
if i
try to get the total number of defects per part for the given date range,
i
get the correct number of defects but an incorrect number for the total
sorted. the number of defects comes from a second table (tblDefects),
that
is tied to tblDefect Count by using an autogenerated key field called ID
in
tblDefect Count. for every ID in tblDefect Count, there could be any where
from 0 to 27 records in tblDefects (there are 27 different defect codes
and
each code has its own amount for defects for that code and part number.)

any help with this would be greatly appreciated as i have been working on
this for about a month now. if you need any thing clarified, please let
me
know and i will do what i can. if you want to see a copy of the database,
let me know.

How about showing us what is happening and what you want with something like
this:

=Part Header============
Part Number:ABC
Total Sorted: 5

-Defects (detail)-------
Too Big
Too Small

=Part Header============
Part NumberEF
Total Sorted: 10

-Defects (detail)-------
Too Big
Too Small
Too Flat
Too Fat

#Report Footer############
Total Sorted 15 -Is this your issue?

--
Duane Hookom
MS Access MVP
--

"jkendrick75" wrote in message
...
that part works fine, just when i try to add in the total defects for that
part during the date range, then the total sorted number is more than what
it
should be. what is happening is say for part 123, there were 100 parts
sorted (during a given date range). for that 100 parts sorted, there were
3
different defect codes (a with 2 defects, b with 3 defects, c with 4
defects). the total sorted should be 100 parts sorted (which i get if i
don't try to total the defects) with a total of 9 defects. what i am
getting
is a total of 300 parts sorted and 9 defects. i have tried to write a
query
for just the total sorted, then a query for just the total defects, and
then
a query that uses the first 2 queries, but all that did was total all
parts
sorted (grouped by the part number) in the entire database, along with all
defects for entire database. it was ignoring the date range and giving
totals for all parts, and then just did a one time total of all defects,
and
assigned it to each part number.

thank you for responding so quickly, and any other ideas will be welcome.

"Duane Hookom" wrote:

There are a couple methods for doing this. One is to create a text box in
the PartNumber(?) group header:
Name: txtTotalSorted
Control Source: [Total Sorted]
Running Sum: Over All
Visible: No
Then, add a text box to your report footer
Control Source: =txtTotalSorted

You could also set up a totals query based on [tblDefect Count] that
returns
only one record with the sum of [Total Sorted] for the particular date
range. You could then add this query to your report's record source.

--
Duane Hookom
MS Access MVP
--

"jkendrick75" wrote in message
...
i am trying to create a report that, using a start and end date that is
given
on a form, groups by part number and sums the number of parts sorted
per
part
for the given date range. this part works fine as the date, part
number,
and
the values for the total sorted are all in one table (tblDefect Count).
if i
try to get the total number of defects per part for the given date
range,
i
get the correct number of defects but an incorrect number for the total
sorted. the number of defects comes from a second table (tblDefects),
that
is tied to tblDefect Count by using an autogenerated key field called
ID
in
tblDefect Count. for every ID in tblDefect Count, there could be any
where
from 0 to 27 records in tblDefects (there are 27 different defect codes
and
each code has its own amount for defects for that code and part
number.)

any help with this would be greatly appreciated as i have been working
on
this for about a month now. if you need any thing clarified, please
let
me
know and i will do what i can. if you want to see a copy of the
database,
let me know.

ok, i'll try.
currently my report kinda looks like this

page header:
Part Number Quantity Inspected Defect Quantity

Detail:
[PartNumber] [SumOfTotalSort] [SumofDefects]

page footer:
="Page " & [Page] & " & [Pages]

report footer:
Total Sorted: [=sum([sumoftotalsort]) Total Defects: [=Sum([sumofDefects])]

In the Detail section, it will show all part numbers that are in the
database within the selected date range, listed as separate rows, and each
part number's total sorted, as long as that is all i am asking for in the
query. if i try to include the total defects, then the total sorted is
inflated. such as

part number sorted defects
1 100 a(1), b(3), c(2)
2 200
3 50 a(2)
4 300 b(5), c(5)
1 300 b(2), c(5)
2 200 a(4), b(6)
3 400 c(10)


my total sorted should be 1550 and total defects should be 45. what i am
getting when i try to include the total defects in my query is total sorted
2550 and a total defects of 45. what it is doing is for each defect (as in
part number 1 has 3 different defects the first time and then 2 defects the
second time) it is adding the total sorted (for that instance) to itself for
each different defect. the query that i am using is as follows

SELECT [TBL defect count].[Part Number], Sum(tblDefects.DefQuantity) AS
sumofdefquantity
FROM [TBL defect count] LEFT JOIN tblDefects ON [TBL defect count].ID =
tblDefects.ID
WHERE ((([TBL defect count].Date) Between
[forms]![frmDateRangeSums]![StartDate] And
[forms]![frmDateRangeSums]![EndDate]))
GROUP BY [TBL defect count].[Part Number];


the frmDateRangeSums is the form that i use to ask for the start date and
end date of the date range.


"Duane Hookom" wrote:

How about showing us what is happening and what you want with something like
this:

=Part Header============
Part Number:ABC
Total Sorted: 5

-Defects (detail)-------
Too Big
Too Small

=Part Header============
Part NumberEF
Total Sorted: 10

-Defects (detail)-------
Too Big
Too Small
Too Flat
Too Fat

#Report Footer############
Total Sorted 15 -Is this your issue?

--
Duane Hookom
MS Access MVP
--

"jkendrick75" wrote in message
...
that part works fine, just when i try to add in the total defects for that
part during the date range, then the total sorted number is more than what
it
should be. what is happening is say for part 123, there were 100 parts
sorted (during a given date range). for that 100 parts sorted, there were
3
different defect codes (a with 2 defects, b with 3 defects, c with 4
defects). the total sorted should be 100 parts sorted (which i get if i
don't try to total the defects) with a total of 9 defects. what i am
getting
is a total of 300 parts sorted and 9 defects. i have tried to write a
query
for just the total sorted, then a query for just the total defects, and
then
a query that uses the first 2 queries, but all that did was total all
parts
sorted (grouped by the part number) in the entire database, along with all
defects for entire database. it was ignoring the date range and giving
totals for all parts, and then just did a one time total of all defects,
and
assigned it to each part number.

thank you for responding so quickly, and any other ideas will be welcome.

"Duane Hookom" wrote:

There are a couple methods for doing this. One is to create a text box in
the PartNumber(?) group header:
Name: txtTotalSorted
Control Source: [Total Sorted]
Running Sum: Over All
Visible: No
Then, add a text box to your report footer
Control Source: =txtTotalSorted

You could also set up a totals query based on [tblDefect Count] that
returns
only one record with the sum of [Total Sorted] for the particular date
range. You could then add this query to your report's record source.

--
Duane Hookom
MS Access MVP
--

"jkendrick75" wrote in message
...
i am trying to create a report that, using a start and end date that is
given
on a form, groups by part number and sums the number of parts sorted
per
part
for the given date range. this part works fine as the date, part
number,
and
the values for the total sorted are all in one table (tblDefect Count).
if i
try to get the total number of defects per part for the given date
range,
i
get the correct number of defects but an incorrect number for the total
sorted. the number of defects comes from a second table (tblDefects),
that
is tied to tblDefect Count by using an autogenerated key field called
ID
in
tblDefect Count. for every ID in tblDefect Count, there could be any
where
from 0 to 27 records in tblDefects (there are 27 different defect codes
and
each code has its own amount for defects for that code and part
number.)

any help with this would be greatly appreciated as i have been working
on
this for about a month now. if you need any thing clarified, please
let
me
know and i will do what i can. if you want to see a copy of the
database,
let me know.

Do your defects actually print across the page like:
a(1), b(3), c(2)
Why do you have Part Numbers repeated? Is there some column/field you are
providing?
Did you try creating a running sum on the SumOfTotalSort?

--
Duane Hookom
MS Access MVP
--

"jkendrick75" wrote in message
...
ok, i'll try.
currently my report kinda looks like this

page header:
Part Number Quantity Inspected Defect Quantity

Detail:
[PartNumber] [SumOfTotalSort] [SumofDefects]

page footer:
="Page " & [Page] & " & [Pages]

report footer:
Total Sorted: [=sum([sumoftotalsort]) Total Defects:
[=Sum([sumofDefects])]

In the Detail section, it will show all part numbers that are in the
database within the selected date range, listed as separate rows, and each
part number's total sorted, as long as that is all i am asking for in the
query. if i try to include the total defects, then the total sorted is
inflated. such as

part number sorted defects
1 100 a(1), b(3), c(2)
2 200
3 50 a(2)
4 300 b(5), c(5)
1 300 b(2), c(5)
2 200 a(4), b(6)
3 400 c(10)


my total sorted should be 1550 and total defects should be 45. what i am
getting when i try to include the total defects in my query is total
sorted
2550 and a total defects of 45. what it is doing is for each defect (as
in
part number 1 has 3 different defects the first time and then 2 defects
the
second time) it is adding the total sorted (for that instance) to itself
for
each different defect. the query that i am using is as follows

SELECT [TBL defect count].[Part Number], Sum(tblDefects.DefQuantity) AS
sumofdefquantity
FROM [TBL defect count] LEFT JOIN tblDefects ON [TBL defect count].ID =
tblDefects.ID
WHERE ((([TBL defect count].Date) Between
[forms]![frmDateRangeSums]![StartDate] And
[forms]![frmDateRangeSums]![EndDate]))
GROUP BY [TBL defect count].[Part Number];


the frmDateRangeSums is the form that i use to ask for the start date and
end date of the date range.


"Duane Hookom" wrote:

How about showing us what is happening and what you want with something
like
this:

=Part Header============
Part Number:ABC
Total Sorted: 5

-Defects (detail)-------
Too Big
Too Small

=Part Header============
Part NumberEF
Total Sorted: 10

-Defects (detail)-------
Too Big
Too Small
Too Flat
Too Fat

#Report Footer############
Total Sorted 15 -Is this your issue?

--
Duane Hookom
MS Access MVP
--

"jkendrick75" wrote in message
...
that part works fine, just when i try to add in the total defects for
that
part during the date range, then the total sorted number is more than
what
it
should be. what is happening is say for part 123, there were 100 parts
sorted (during a given date range). for that 100 parts sorted, there
were
3
different defect codes (a with 2 defects, b with 3 defects, c with 4
defects). the total sorted should be 100 parts sorted (which i get if
i
don't try to total the defects) with a total of 9 defects. what i am
getting
is a total of 300 parts sorted and 9 defects. i have tried to write a
query
for just the total sorted, then a query for just the total defects, and
then
a query that uses the first 2 queries, but all that did was total all
parts
sorted (grouped by the part number) in the entire database, along with
all
defects for entire database. it was ignoring the date range and giving
totals for all parts, and then just did a one time total of all
defects,
and
assigned it to each part number.

thank you for responding so quickly, and any other ideas will be
welcome.

"Duane Hookom" wrote:

There are a couple methods for doing this. One is to create a text box
in
the PartNumber(?) group header:
Name: txtTotalSorted
Control Source: [Total Sorted]
Running Sum: Over All
Visible: No
Then, add a text box to your report footer
Control Source: =txtTotalSorted

You could also set up a totals query based on [tblDefect Count] that
returns
only one record with the sum of [Total Sorted] for the particular date
range. You could then add this query to your report's record source.

--
Duane Hookom
MS Access MVP
--

"jkendrick75" wrote in message
...
i am trying to create a report that, using a start and end date that
is
given
on a form, groups by part number and sums the number of parts sorted
per
part
for the given date range. this part works fine as the date, part
number,
and
the values for the total sorted are all in one table (tblDefect
Count).
if i
try to get the total number of defects per part for the given date
range,
i
get the correct number of defects but an incorrect number for the
total
sorted. the number of defects comes from a second table
(tblDefects),
that
is tied to tblDefect Count by using an autogenerated key field
called
ID
in
tblDefect Count. for every ID in tblDefect Count, there could be any
where
from 0 to 27 records in tblDefects (there are 27 different defect
codes
and
each code has its own amount for defects for that code and part
number.)

any help with this would be greatly appreciated as i have been
working
on
this for about a month now. if you need any thing clarified, please
let
me
know and i will do what i can. if you want to see a copy of the
database,
let me know.

what i was doing with showing the defects like that and having the part
numbers repeat was to show an example of the data for a date range. say over
the course of a week, the same part may be inspected each day, but have a
different quantity of parts inspected each day. each day may have a
different set of defects and defect quantities. in my example, it was defect
code 'a' with a defect quantity of '1', code 'b' with a defect quantity of
'3', etc.
below are the two tables in question along with the relationship between them

Table1: "tbl Defect Count" Table2: "tblDefects"
ID - autogenerated key AutoID -
autogenerated number
Date - Date/Time (mm/dd/yy) ID - Number (tied to ID
field in tbl
Part Number - text Defect
Count)
SortTime - Number DefCode - text
TotalSort - Number DefQuantity - number
NCM_Num - text
Containment - text
PlantNum - text

as i stated earlier, for each record in tbl Defect Count, there can be any
where from 0 to 27 records (on any given day.)

the autoid field in tblDefects was due to a major change in database design.
the original database was not going to be very accurate, but my supervisor
didn't want to lose the 3 months of data in it. doesn't do anything other
than count total number of records.

as for creating a running sum on the sum of total sort, that gave a really
oddball answer. the totals in the report footer section are correct for what
is in the Details section. the problem is getting the [SumofTotalSort]
numbers (in the Details sections) to be correct when getting the
[SumofDefects] numbers also (Details section).

if you would like, i can run the documenter and send you the output for the
tables, queries, code, forms, and reports...
or i could send you the database as a zip file. will have to disable the
macros first, but the zip file is 231Kb in size.

thanks again for replying.

"Duane Hookom" wrote:

Do your defects actually print across the page like:
a(1), b(3), c(2)
Why do you have Part Numbers repeated? Is there some column/field you are
providing?
Did you try creating a running sum on the SumOfTotalSort?

--
Duane Hookom
MS Access MVP
--

"jkendrick75" wrote in message
...
ok, i'll try.
currently my report kinda looks like this

page header:
Part Number Quantity Inspected Defect Quantity

Detail:
[PartNumber] [SumOfTotalSort] [SumofDefects]

page footer:
="Page " & [Page] & " & [Pages]

report footer:
Total Sorted: [=sum([sumoftotalsort]) Total Defects:
[=Sum([sumofDefects])]

In the Detail section, it will show all part numbers that are in the
database within the selected date range, listed as separate rows, and each
part number's total sorted, as long as that is all i am asking for in the
query. if i try to include the total defects, then the total sorted is
inflated. such as

part number sorted defects
1 100 a(1), b(3), c(2)
2 200
3 50 a(2)
4 300 b(5), c(5)
1 300 b(2), c(5)
2 200 a(4), b(6)
3 400 c(10)


my total sorted should be 1550 and total defects should be 45. what i am
getting when i try to include the total defects in my query is total
sorted
2550 and a total defects of 45. what it is doing is for each defect (as
in
part number 1 has 3 different defects the first time and then 2 defects
the
second time) it is adding the total sorted (for that instance) to itself
for
each different defect. the query that i am using is as follows

SELECT [TBL defect count].[Part Number], Sum(tblDefects.DefQuantity) AS
sumofdefquantity
FROM [TBL defect count] LEFT JOIN tblDefects ON [TBL defect count].ID =
tblDefects.ID
WHERE ((([TBL defect count].Date) Between
[forms]![frmDateRangeSums]![StartDate] And
[forms]![frmDateRangeSums]![EndDate]))
GROUP BY [TBL defect count].[Part Number];


the frmDateRangeSums is the form that i use to ask for the start date and
end date of the date range.


"Duane Hookom" wrote:

How about showing us what is happening and what you want with something
like
this:

=Part Header============
Part Number:ABC
Total Sorted: 5

-Defects (detail)-------
Too Big
Too Small

=Part Header============
Part NumberEF
Total Sorted: 10

-Defects (detail)-------
Too Big
Too Small
Too Flat
Too Fat

#Report Footer############
Total Sorted 15 -Is this your issue?

--
Duane Hookom
MS Access MVP
--

"jkendrick75" wrote in message
...
that part works fine, just when i try to add in the total defects for
that
part during the date range, then the total sorted number is more than
what
it
should be. what is happening is say for part 123, there were 100 parts
sorted (during a given date range). for that 100 parts sorted, there
were
3
different defect codes (a with 2 defects, b with 3 defects, c with 4
defects). the total sorted should be 100 parts sorted (which i get if
i
don't try to total the defects) with a total of 9 defects. what i am
getting
is a total of 300 parts sorted and 9 defects. i have tried to write a
query
for just the total sorted, then a query for just the total defects, and
then
a query that uses the first 2 queries, but all that did was total all
parts
sorted (grouped by the part number) in the entire database, along with
all
defects for entire database. it was ignoring the date range and giving
totals for all parts, and then just did a one time total of all
defects,
and
assigned it to each part number.

thank you for responding so quickly, and any other ideas will be
welcome.

"Duane Hookom" wrote:

There are a couple methods for doing this. One is to create a text box
in
the PartNumber(?) group header:
Name: txtTotalSorted
Control Source: [Total Sorted]
Running Sum: Over All
Visible: No
Then, add a text box to your report footer
Control Source: =txtTotalSorted

You could also set up a totals query based on [tblDefect Count] that
returns
only one record with the sum of [Total Sorted] for the particular date
range. You could then add this query to your report's record source.

--
Duane Hookom
MS Access MVP
--

"jkendrick75" wrote in message
...
i am trying to create a report that, using a start and end date that
is
given
on a form, groups by part number and sums the number of parts sorted
per
part
for the given date range. this part works fine as the date, part
number,
and
the values for the total sorted are all in one table (tblDefect
Count).
if i
try to get the total number of defects per part for the given date
range,
i
get the correct number of defects but an incorrect number for the
total
sorted. the number of defects comes from a second table
(tblDefects),
that
is tied to tblDefect Count by using an autogenerated key field
called
ID
in
tblDefect Count. for every ID in tblDefect Count, there could be any
where
from 0 to 27 records in tblDefects (there are 27 different defect
codes
and
each code has its own amount for defects for that code and part
number.)

any help with this would be greatly appreciated as i have been
working
on
this for about a month now. if you need any thing clarified, please
let
me
know and i will do what i can. if you want to see a copy of the
database,
let me know.

Is the [SumofTotalSort] in a group header or in the details section? If it
is repeating in the detail section then why is it in the detail section and
not a group header?

Consider removing the tblDefects entirely and placing it in a subreport.

--
Duane Hookom
MS Access MVP
--

"jkendrick75" wrote in message
...
what i was doing with showing the defects like that and having the part
numbers repeat was to show an example of the data for a date range. say
over
the course of a week, the same part may be inspected each day, but have a
different quantity of parts inspected each day. each day may have a
different set of defects and defect quantities. in my example, it was
defect
code 'a' with a defect quantity of '1', code 'b' with a defect quantity of
'3', etc.
below are the two tables in question along with the relationship between
them

Table1: "tbl Defect Count" Table2:
"tblDefects"
ID - autogenerated key AutoID -
autogenerated number
Date - Date/Time (mm/dd/yy) ID - Number (tied to
ID
field in tbl
Part Number - text Defect
Count)
SortTime - Number DefCode - text
TotalSort - Number DefQuantity -
number
NCM_Num - text
Containment - text
PlantNum - text

as i stated earlier, for each record in tbl Defect Count, there can be any
where from 0 to 27 records (on any given day.)

the autoid field in tblDefects was due to a major change in database
design.
the original database was not going to be very accurate, but my supervisor
didn't want to lose the 3 months of data in it. doesn't do anything other
than count total number of records.

as for creating a running sum on the sum of total sort, that gave a really
oddball answer. the totals in the report footer section are correct for
what
is in the Details section. the problem is getting the [SumofTotalSort]
numbers (in the Details sections) to be correct when getting the
[SumofDefects] numbers also (Details section).

if you would like, i can run the documenter and send you the output for
the
tables, queries, code, forms, and reports...
or i could send you the database as a zip file. will have to disable the
macros first, but the zip file is 231Kb in size.

thanks again for replying.

"Duane Hookom" wrote:

Do your defects actually print across the page like:
a(1), b(3), c(2)
Why do you have Part Numbers repeated? Is there some column/field you are
providing?
Did you try creating a running sum on the SumOfTotalSort?

--
Duane Hookom
MS Access MVP
--

"jkendrick75" wrote in message
...
ok, i'll try.
currently my report kinda looks like this

page header:
Part Number Quantity Inspected Defect Quantity

Detail:
[PartNumber] [SumOfTotalSort] [SumofDefects]

page footer:
="Page " & [Page] & " & [Pages]

report footer:
Total Sorted: [=sum([sumoftotalsort]) Total Defects:
[=Sum([sumofDefects])]

In the Detail section, it will show all part numbers that are in the
database within the selected date range, listed as separate rows, and
each
part number's total sorted, as long as that is all i am asking for in
the
query. if i try to include the total defects, then the total sorted is
inflated. such as

part number sorted defects
1 100 a(1), b(3), c(2)
2 200
3 50 a(2)
4 300 b(5), c(5)
1 300 b(2), c(5)
2 200 a(4), b(6)
3 400 c(10)


my total sorted should be 1550 and total defects should be 45. what i
am
getting when i try to include the total defects in my query is total
sorted
2550 and a total defects of 45. what it is doing is for each defect
(as
in
part number 1 has 3 different defects the first time and then 2 defects
the
second time) it is adding the total sorted (for that instance) to
itself
for
each different defect. the query that i am using is as follows

SELECT [TBL defect count].[Part Number], Sum(tblDefects.DefQuantity) AS
sumofdefquantity
FROM [TBL defect count] LEFT JOIN tblDefects ON [TBL defect count].ID =
tblDefects.ID
WHERE ((([TBL defect count].Date) Between
[forms]![frmDateRangeSums]![StartDate] And
[forms]![frmDateRangeSums]![EndDate]))
GROUP BY [TBL defect count].[Part Number];


the frmDateRangeSums is the form that i use to ask for the start date
and
end date of the date range.


"Duane Hookom" wrote:

How about showing us what is happening and what you want with
something
like
this:

=Part Header============
Part Number:ABC
Total Sorted: 5

-Defects (detail)-------
Too Big
Too Small

=Part Header============
Part NumberEF
Total Sorted: 10

-Defects (detail)-------
Too Big
Too Small
Too Flat
Too Fat

#Report Footer############
Total Sorted 15 -Is this your issue?

--
Duane Hookom
MS Access MVP
--

"jkendrick75" wrote in message
...
that part works fine, just when i try to add in the total defects
for
that
part during the date range, then the total sorted number is more
than
what
it
should be. what is happening is say for part 123, there were 100
parts
sorted (during a given date range). for that 100 parts sorted,
there
were
3
different defect codes (a with 2 defects, b with 3 defects, c with 4
defects). the total sorted should be 100 parts sorted (which i get
if
i
don't try to total the defects) with a total of 9 defects. what i
am
getting
is a total of 300 parts sorted and 9 defects. i have tried to write
a
query
for just the total sorted, then a query for just the total defects,
and
then
a query that uses the first 2 queries, but all that did was total
all
parts
sorted (grouped by the part number) in the entire database, along
with
all
defects for entire database. it was ignoring the date range and
giving
totals for all parts, and then just did a one time total of all
defects,
and
assigned it to each part number.

thank you for responding so quickly, and any other ideas will be
welcome.

"Duane Hookom" wrote:

There are a couple methods for doing this. One is to create a text
box
in
the PartNumber(?) group header:
Name: txtTotalSorted
Control Source: [Total Sorted]
Running Sum: Over All
Visible: No
Then, add a text box to your report footer
Control Source: =txtTotalSorted

You could also set up a totals query based on [tblDefect Count]
that
returns
only one record with the sum of [Total Sorted] for the particular
date
range. You could then add this query to your report's record
source.

--
Duane Hookom
MS Access MVP
--

"jkendrick75" wrote in
message
...
i am trying to create a report that, using a start and end date
that
is
given
on a form, groups by part number and sums the number of parts
sorted
per
part
for the given date range. this part works fine as the date, part
number,
and
the values for the total sorted are all in one table (tblDefect
Count).
if i
try to get the total number of defects per part for the given
date
range,
i
get the correct number of defects but an incorrect number for the
total
sorted. the number of defects comes from a second table
(tblDefects),
that
is tied to tblDefect Count by using an autogenerated key field
called
ID
in
tblDefect Count. for every ID in tblDefect Count, there could be
any
where
from 0 to 27 records in tblDefects (there are 27 different defect
codes
and
each code has its own amount for defects for that code and part
number.)

any help with this would be greatly appreciated as i have been
working
on
this for about a month now. if you need any thing clarified,
please
let
me
know and i will do what i can. if you want to see a copy of the
database,
let me know.

the [SumofTotalSort] is repeating in the details section. i put it there to
conserve space, and to make a cleaner looking report. i have tried removing
the tblDefects and placing it in a subreport, but i apparently didn't do it
correctly or something, can you give me some instructions on how to do this.
like where to put what and so forth... thanks again.

"Duane Hookom" wrote:

Is the [SumofTotalSort] in a group header or in the details section? If it
is repeating in the detail section then why is it in the detail section and
not a group header?

Consider removing the tblDefects entirely and placing it in a subreport.

--
Duane Hookom
MS Access MVP
--

"jkendrick75" wrote in message
...
what i was doing with showing the defects like that and having the part
numbers repeat was to show an example of the data for a date range. say
over
the course of a week, the same part may be inspected each day, but have a
different quantity of parts inspected each day. each day may have a
different set of defects and defect quantities. in my example, it was
defect
code 'a' with a defect quantity of '1', code 'b' with a defect quantity of
'3', etc.
below are the two tables in question along with the relationship between
them

Table1: "tbl Defect Count" Table2:
"tblDefects"
ID - autogenerated key AutoID -
autogenerated number
Date - Date/Time (mm/dd/yy) ID - Number (tied to
ID
field in tbl
Part Number - text Defect
Count)
SortTime - Number DefCode - text
TotalSort - Number DefQuantity -
number
NCM_Num - text
Containment - text
PlantNum - text

as i stated earlier, for each record in tbl Defect Count, there can be any
where from 0 to 27 records (on any given day.)

the autoid field in tblDefects was due to a major change in database
design.
the original database was not going to be very accurate, but my supervisor
didn't want to lose the 3 months of data in it. doesn't do anything other
than count total number of records.

as for creating a running sum on the sum of total sort, that gave a really
oddball answer. the totals in the report footer section are correct for
what
is in the Details section. the problem is getting the [SumofTotalSort]
numbers (in the Details sections) to be correct when getting the
[SumofDefects] numbers also (Details section).

if you would like, i can run the documenter and send you the output for
the
tables, queries, code, forms, and reports...
or i could send you the database as a zip file. will have to disable the
macros first, but the zip file is 231Kb in size.

thanks again for replying.

"Duane Hookom" wrote:

Do your defects actually print across the page like:
a(1), b(3), c(2)
Why do you have Part Numbers repeated? Is there some column/field you are
providing?
Did you try creating a running sum on the SumOfTotalSort?

--
Duane Hookom
MS Access MVP
--

"jkendrick75" wrote in message
...
ok, i'll try.
currently my report kinda looks like this

page header:
Part Number Quantity Inspected Defect Quantity

Detail:
[PartNumber] [SumOfTotalSort] [SumofDefects]

page footer:
="Page " & [Page] & " & [Pages]

report footer:
Total Sorted: [=sum([sumoftotalsort]) Total Defects:
[=Sum([sumofDefects])]

In the Detail section, it will show all part numbers that are in the
database within the selected date range, listed as separate rows, and
each
part number's total sorted, as long as that is all i am asking for in
the
query. if i try to include the total defects, then the total sorted is
inflated. such as

part number sorted defects
1 100 a(1), b(3), c(2)
2 200
3 50 a(2)
4 300 b(5), c(5)
1 300 b(2), c(5)
2 200 a(4), b(6)
3 400 c(10)


my total sorted should be 1550 and total defects should be 45. what i
am
getting when i try to include the total defects in my query is total
sorted
2550 and a total defects of 45. what it is doing is for each defect
(as
in
part number 1 has 3 different defects the first time and then 2 defects
the
second time) it is adding the total sorted (for that instance) to
itself
for
each different defect. the query that i am using is as follows

SELECT [TBL defect count].[Part Number], Sum(tblDefects.DefQuantity) AS
sumofdefquantity
FROM [TBL defect count] LEFT JOIN tblDefects ON [TBL defect count].ID =
tblDefects.ID
WHERE ((([TBL defect count].Date) Between
[forms]![frmDateRangeSums]![StartDate] And
[forms]![frmDateRangeSums]![EndDate]))
GROUP BY [TBL defect count].[Part Number];


the frmDateRangeSums is the form that i use to ask for the start date
and
end date of the date range.


"Duane Hookom" wrote:

How about showing us what is happening and what you want with
something
like
this:

=Part Header============
Part Number:ABC
Total Sorted: 5

-Defects (detail)-------
Too Big
Too Small

=Part Header============
Part NumberEF
Total Sorted: 10

-Defects (detail)-------
Too Big
Too Small
Too Flat
Too Fat

#Report Footer############
Total Sorted 15 -Is this your issue?

--
Duane Hookom
MS Access MVP
--

"jkendrick75" wrote in message
...
that part works fine, just when i try to add in the total defects
for
that
part during the date range, then the total sorted number is more
than
what
it
should be. what is happening is say for part 123, there were 100
parts
sorted (during a given date range). for that 100 parts sorted,
there
were
3
different defect codes (a with 2 defects, b with 3 defects, c with 4
defects). the total sorted should be 100 parts sorted (which i get
if
i
don't try to total the defects) with a total of 9 defects. what i
am
getting
is a total of 300 parts sorted and 9 defects. i have tried to write
a
query
for just the total sorted, then a query for just the total defects,
and
then
a query that uses the first 2 queries, but all that did was total
all
parts
sorted (grouped by the part number) in the entire database, along
with
all
defects for entire database. it was ignoring the date range and
giving
totals for all parts, and then just did a one time total of all
defects,
and
assigned it to each part number.

thank you for responding so quickly, and any other ideas will be
welcome.

"Duane Hookom" wrote:

There are a couple methods for doing this. One is to create a text
box
in
the PartNumber(?) group header:
Name: txtTotalSorted
Control Source: [Total Sorted]
Running Sum: Over All
Visible: No
Then, add a text box to your report footer
Control Source: =txtTotalSorted

You could also set up a totals query based on [tblDefect Count]
that
returns
only one record with the sum of [Total Sorted] for the particular
date
range. You could then add this query to your report's record
source.

--
Duane Hookom
MS Access MVP
--

"jkendrick75" wrote in
message
...
i am trying to create a report that, using a start and end date
that
is
given
on a form, groups by part number and sums the number of parts
sorted
per
part
for the given date range. this part works fine as the date, part
number,
and
the values for the total sorted are all in one table (tblDefect
Count).
if i
try to get the total number of defects per part for the given
date
range,
i
get the correct number of defects but an incorrect number for the
total
sorted. the number of defects comes from a second table
(tblDefects),
that
is tied to tblDefect Count by using an autogenerated key field
called
ID
in
tblDefect Count. for every ID in tblDefect Count, there could be
any
where
from 0 to 27 records in tblDefects (there are 27 different defect
codes
and
each code has its own amount for defects for that code and part
number.)

any help with this would be greatly appreciated as i have been
working
on
this for about a month now. if you need any thing clarified,
please
let
me
know and i will do what i can. if you want to see a copy of the
database,
let me know.

This whole issue may go away if you just place SUmOfTOtalSort in a group
header and then use a running sum. Could you try this?

--
Duane Hookom
MS Access MVP


"jkendrick75" wrote in message
...
the [SumofTotalSort] is repeating in the details section. i put it there
to
conserve space, and to make a cleaner looking report. i have tried
removing
the tblDefects and placing it in a subreport, but i apparently didn't do
it
correctly or something, can you give me some instructions on how to do
this.
like where to put what and so forth... thanks again.

"Duane Hookom" wrote:

Is the [SumofTotalSort] in a group header or in the details section? If
it
is repeating in the detail section then why is it in the detail section
and
not a group header?

Consider removing the tblDefects entirely and placing it in a subreport.

--
Duane Hookom
MS Access MVP
--

"jkendrick75" wrote in message
...
what i was doing with showing the defects like that and having the
part
numbers repeat was to show an example of the data for a date range.
say
over
the course of a week, the same part may be inspected each day, but
have a
different quantity of parts inspected each day. each day may have a
different set of defects and defect quantities. in my example, it was
defect
code 'a' with a defect quantity of '1', code 'b' with a defect
quantity of
'3', etc.
below are the two tables in question along with the relationship
between
them

Table1: "tbl Defect Count" Table2:
"tblDefects"
ID - autogenerated key AutoID -
autogenerated number
Date - Date/Time (mm/dd/yy) ID - Number (tied
to
ID
field in tbl
Part Number - text
Defect
Count)
SortTime - Number DefCode -
text
TotalSort - Number
DefQuantity -
number
NCM_Num - text
Containment - text
PlantNum - text

as i stated earlier, for each record in tbl Defect Count, there can be
any
where from 0 to 27 records (on any given day.)

the autoid field in tblDefects was due to a major change in database
design.
the original database was not going to be very accurate, but my
supervisor
didn't want to lose the 3 months of data in it. doesn't do anything
other
than count total number of records.

as for creating a running sum on the sum of total sort, that gave a
really
oddball answer. the totals in the report footer section are correct
for
what
is in the Details section. the problem is getting the
[SumofTotalSort]
numbers (in the Details sections) to be correct when getting the
[SumofDefects] numbers also (Details section).

if you would like, i can run the documenter and send you the output
for
the
tables, queries, code, forms, and reports...
or i could send you the database as a zip file. will have to disable
the
macros first, but the zip file is 231Kb in size.

thanks again for replying.

"Duane Hookom" wrote:

Do your defects actually print across the page like:
a(1), b(3), c(2)
Why do you have Part Numbers repeated? Is there some column/field you
are
providing?
Did you try creating a running sum on the SumOfTotalSort?

--
Duane Hookom
MS Access MVP
--

"jkendrick75" wrote in
message
...
ok, i'll try.
currently my report kinda looks like this

page header:
Part Number Quantity Inspected Defect Quantity

Detail:
[PartNumber] [SumOfTotalSort] [SumofDefects]

page footer:
="Page " & [Page] & " & [Pages]

report footer:
Total Sorted: [=sum([sumoftotalsort]) Total Defects:
[=Sum([sumofDefects])]

In the Detail section, it will show all part numbers that are in
the
database within the selected date range, listed as separate rows,
and
each
part number's total sorted, as long as that is all i am asking for
in
the
query. if i try to include the total defects, then the total
sorted is
inflated. such as

part number sorted defects
1 100 a(1), b(3), c(2)
2 200
3 50 a(2)
4 300 b(5), c(5)
1 300 b(2), c(5)
2 200 a(4), b(6)
3 400 c(10)


my total sorted should be 1550 and total defects should be 45. what
i
am
getting when i try to include the total defects in my query is
total
sorted
2550 and a total defects of 45. what it is doing is for each
defect
(as
in
part number 1 has 3 different defects the first time and then 2
defects
the
second time) it is adding the total sorted (for that instance) to
itself
for
each different defect. the query that i am using is as follows

SELECT [TBL defect count].[Part Number],
Sum(tblDefects.DefQuantity) AS
sumofdefquantity
FROM [TBL defect count] LEFT JOIN tblDefects ON [TBL defect
count].ID =
tblDefects.ID
WHERE ((([TBL defect count].Date) Between
[forms]![frmDateRangeSums]![StartDate] And
[forms]![frmDateRangeSums]![EndDate]))
GROUP BY [TBL defect count].[Part Number];


the frmDateRangeSums is the form that i use to ask for the start
date
and
end date of the date range.


"Duane Hookom" wrote:

How about showing us what is happening and what you want with
something
like
this:

=Part Header============
Part Number:ABC
Total Sorted: 5

-Defects (detail)-------
Too Big
Too Small

=Part Header============
Part NumberEF
Total Sorted: 10

-Defects (detail)-------
Too Big
Too Small
Too Flat
Too Fat

#Report Footer############
Total Sorted 15 -Is this your issue?

--
Duane Hookom
MS Access MVP
--

"jkendrick75" wrote in
message
...
that part works fine, just when i try to add in the total
defects
for
that
part during the date range, then the total sorted number is more
than
what
it
should be. what is happening is say for part 123, there were
100
parts
sorted (during a given date range). for that 100 parts sorted,
there
were
3
different defect codes (a with 2 defects, b with 3 defects, c
with 4
defects). the total sorted should be 100 parts sorted (which i
get
if
i
don't try to total the defects) with a total of 9 defects. what
i
am
getting
is a total of 300 parts sorted and 9 defects. i have tried to
write
a
query
for just the total sorted, then a query for just the total
defects,
and
then
a query that uses the first 2 queries, but all that did was
total
all
parts
sorted (grouped by the part number) in the entire database,
along
with
all
defects for entire database. it was ignoring the date range and
giving
totals for all parts, and then just did a one time total of all
defects,
and
assigned it to each part number.

thank you for responding so quickly, and any other ideas will be
welcome.

"Duane Hookom" wrote:

There are a couple methods for doing this. One is to create a
text
box
in
the PartNumber(?) group header:
Name: txtTotalSorted
Control Source: [Total Sorted]
Running Sum: Over All
Visible: No
Then, add a text box to your report footer
Control Source: =txtTotalSorted

You could also set up a totals query based on [tblDefect Count]
that
returns
only one record with the sum of [Total Sorted] for the
particular
date
range. You could then add this query to your report's record
source.

--
Duane Hookom
MS Access MVP
--

"jkendrick75" wrote in
message
...
i am trying to create a report that, using a start and end
date
that
is
given
on a form, groups by part number and sums the number of parts
sorted
per
part
for the given date range. this part works fine as the date,
part
number,
and
the values for the total sorted are all in one table
(tblDefect
Count).
if i
try to get the total number of defects per part for the given
date
range,
i
get the correct number of defects but an incorrect number for
the
total
sorted. the number of defects comes from a second table
(tblDefects),
that
is tied to tblDefect Count by using an autogenerated key
field
called
ID
in
tblDefect Count. for every ID in tblDefect Count, there could
be
any
where
from 0 to 27 records in tblDefects (there are 27 different
defect
codes
and
each code has its own amount for defects for that code and
part
number.)

any help with this would be greatly appreciated as i have
been
working
on
this for about a month now. if you need any thing clarified,
please
let
me
know and i will do what i can. if you want to see a copy of
the
database,
let me know.