incorrect sums in report using 2 tables

i'll give it a try, as for the running sum, do the running sum for the
sumoftotalsort in the group header? if so, if the first number is total sort
number is correct, wouldn't the second totalsort number include the sum from
the first total sort number? as each one is being grouped by PartNum... just
trying to clarify. thanks for keeping with the posts....

"Duane Hookom" wrote:

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.

I didn't think you were grouping by part number. I thought your grouping
would be a combination of date and part number.

A running sum would display the cumulated sum.

--
Duane Hookom
MS Access MVP
--

"jkendrick75" wrote in message
...
i'll give it a try, as for the running sum, do the running sum for the
sumoftotalsort in the group header? if so, if the first number is total
sort
number is correct, wouldn't the second totalsort number include the sum
from
the first total sort number? as each one is being grouped by PartNum...
just
trying to clarify. thanks for keeping with the posts....

"Duane Hookom" wrote:

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.

ok, tried it, not sure if i was doing it like you were expecting, but the
numbers were the same as before, until i started doing a running sum for
sumoftotalsort. these numbers were in the 6 digit range when it should be
barely into 5 digits (just over 10,000 parts sorted for a particular part
number).

"jkendrick75" wrote:

i'll give it a try, as for the running sum, do the running sum for the
sumoftotalsort in the group header? if so, if the first number is total sort
number is correct, wouldn't the second totalsort number include the sum from
the first total sort number? as each one is being grouped by PartNum... just
trying to clarify. thanks for keeping with the posts....

"Duane Hookom" wrote:

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.

Please provide a sample output of what you are getting. Refer back to the
reply where I took the time to layout a display of data.

--
Duane Hookom
MS Access MVP


"jkendrick75" wrote in message
...
ok, tried it, not sure if i was doing it like you were expecting, but the
numbers were the same as before, until i started doing a running sum for
sumoftotalsort. these numbers were in the 6 digit range when it should be
barely into 5 digits (just over 10,000 parts sorted for a particular part
number).

"jkendrick75" wrote:

i'll give it a try, as for the running sum, do the running sum for the
sumoftotalsort in the group header? if so, if the first number is total
sort
number is correct, wouldn't the second totalsort number include the sum
from
the first total sort number? as each one is being grouped by PartNum...
just
trying to clarify. thanks for keeping with the posts....

"Duane Hookom" wrote:

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.

ok, here goes
=Page Header======================
Part Total Sorted/Part Total Defects/Part
=SumofTotalSort Header============
296,933
=Detail===========================
012007-017 324
=SumofTotalSort Header============
349,259
=Detail===========================
46-0426-09 275
=Page Footer======================
Page 1 of 3
=Report Footer====================
sum of sorted parts sum of total defects

The first amount in the SumofTotalSort header should be 10,261. the second
amount of 349,259 should be 26,619.

i am in the process of putting together a website where two report snapshots
have been made. 1 is what the report should look like, the other is what i
am getting now. also a copy of the full database will be available as a zip
file for download (241kb in size)

when i get this running i will post the link in my next reply
thanks for your patience and help in this matter.
"Duane Hookom" wrote:

Please provide a sample output of what you are getting. Refer back to the
reply where I took the time to layout a display of data.

--
Duane Hookom
MS Access MVP


"jkendrick75" wrote in message
...
ok, tried it, not sure if i was doing it like you were expecting, but the
numbers were the same as before, until i started doing a running sum for
sumoftotalsort. these numbers were in the 6 digit range when it should be
barely into 5 digits (just over 10,000 parts sorted for a particular part
number).

"jkendrick75" wrote:

i'll give it a try, as for the running sum, do the running sum for the
sumoftotalsort in the group header? if so, if the first number is total
sort
number is correct, wouldn't the second totalsort number include the sum
from
the first total sort number? as each one is being grouped by PartNum...
just
trying to clarify. thanks for keeping with the posts....

"Duane Hookom" wrote:

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.

What is the control source of the text box in SumofTotalSort? I expect that
this should not be a Sum().

--
Duane Hookom
MS Access MVP


"jkendrick75" wrote in message
...
ok, here goes
=Page Header======================
Part Total Sorted/Part Total Defects/Part
=SumofTotalSort Header============
296,933
=Detail===========================
012007-017 324
=SumofTotalSort Header============
349,259
=Detail===========================
46-0426-09 275
=Page Footer======================
Page 1 of 3
=Report Footer====================
sum of sorted parts sum of total defects

The first amount in the SumofTotalSort header should be 10,261. the
second
amount of 349,259 should be 26,619.

i am in the process of putting together a website where two report
snapshots
have been made. 1 is what the report should look like, the other is what
i
am getting now. also a copy of the full database will be available as a
zip
file for download (241kb in size)

when i get this running i will post the link in my next reply
thanks for your patience and help in this matter.
"Duane Hookom" wrote:

Please provide a sample output of what you are getting. Refer back to
the
reply where I took the time to layout a display of data.

--
Duane Hookom
MS Access MVP


"jkendrick75" wrote in message
...
ok, tried it, not sure if i was doing it like you were expecting, but
the
numbers were the same as before, until i started doing a running sum
for
sumoftotalsort. these numbers were in the 6 digit range when it
should be
barely into 5 digits (just over 10,000 parts sorted for a particular
part
number).

"jkendrick75" wrote:

i'll give it a try, as for the running sum, do the running sum for
the
sumoftotalsort in the group header? if so, if the first number is
total
sort
number is correct, wouldn't the second totalsort number include the
sum
from
the first total sort number? as each one is being grouped by
PartNum...
just
trying to clarify. thanks for keeping with the posts....

"Duane Hookom" wrote:

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.

the control source for the SumofTotalSort in the SumofTotalSort Header is
SumofTotalSort. the textbox control in the SumofTotalSort Header is named
TotalSort.

"Duane Hookom" wrote:

What is the control source of the text box in SumofTotalSort? I expect that
this should not be a Sum().

--
Duane Hookom
MS Access MVP


"jkendrick75" wrote in message
...
ok, here goes
=Page Header======================
Part Total Sorted/Part Total Defects/Part
=SumofTotalSort Header============
296,933
=Detail===========================
012007-017 324
=SumofTotalSort Header============
349,259
=Detail===========================
46-0426-09 275
=Page Footer======================
Page 1 of 3
=Report Footer====================
sum of sorted parts sum of total defects

The first amount in the SumofTotalSort header should be 10,261. the
second
amount of 349,259 should be 26,619.

i am in the process of putting together a website where two report
snapshots
have been made. 1 is what the report should look like, the other is what
i
am getting now. also a copy of the full database will be available as a
zip
file for download (241kb in size)

when i get this running i will post the link in my next reply
thanks for your patience and help in this matter.
"Duane Hookom" wrote:

Please provide a sample output of what you are getting. Refer back to
the
reply where I took the time to layout a display of data.

--
Duane Hookom
MS Access MVP


"jkendrick75" wrote in message
...
ok, tried it, not sure if i was doing it like you were expecting, but
the
numbers were the same as before, until i started doing a running sum
for
sumoftotalsort. these numbers were in the 6 digit range when it
should be
barely into 5 digits (just over 10,000 parts sorted for a particular
part
number).

"jkendrick75" wrote:

i'll give it a try, as for the running sum, do the running sum for
the
sumoftotalsort in the group header? if so, if the first number is
total
sort
number is correct, wouldn't the second totalsort number include the
sum
from
the first total sort number? as each one is being grouped by
PartNum...
just
trying to clarify. thanks for keeping with the posts....

"Duane Hookom" wrote:

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.

i have posted a very simple website located at
http://mysite.verizon.net/jkendrick75
the site has a couple of screen caps to show what the current design of the
report looks like, the relationship screen between all tables, and a screen
cap of tbl Defect Count as a datasheet with a subtable opened up. the
subtable is from tblDefects. it also provides two different reports saved as
..snp files, downloaded as a zip file. a copy of the entire database is also
available to download as a zip file (265 Kb in size). if you have any other
questions or ideas, i am still listening and will do my best to answer your
questions. thank you for sticking with this thread for so long.

"jkendrick75" wrote:

the control source for the SumofTotalSort in the SumofTotalSort Header is
SumofTotalSort. the textbox control in the SumofTotalSort Header is named
TotalSort.

"Duane Hookom" wrote:

What is the control source of the text box in SumofTotalSort? I expect that
this should not be a Sum().

--
Duane Hookom
MS Access MVP


"jkendrick75" wrote in message
...
ok, here goes
=Page Header======================
Part Total Sorted/Part Total Defects/Part
=SumofTotalSort Header============
296,933
=Detail===========================
012007-017 324
=SumofTotalSort Header============
349,259
=Detail===========================
46-0426-09 275
=Page Footer======================
Page 1 of 3
=Report Footer====================
sum of sorted parts sum of total defects

The first amount in the SumofTotalSort header should be 10,261. the
second
amount of 349,259 should be 26,619.

i am in the process of putting together a website where two report
snapshots
have been made. 1 is what the report should look like, the other is what
i
am getting now. also a copy of the full database will be available as a
zip
file for download (241kb in size)

when i get this running i will post the link in my next reply
thanks for your patience and help in this matter.
"Duane Hookom" wrote:

Please provide a sample output of what you are getting. Refer back to
the
reply where I took the time to layout a display of data.

--
Duane Hookom
MS Access MVP


"jkendrick75" wrote in message
...
ok, tried it, not sure if i was doing it like you were expecting, but
the
numbers were the same as before, until i started doing a running sum
for
sumoftotalsort. these numbers were in the 6 digit range when it
should be
barely into 5 digits (just over 10,000 parts sorted for a particular
part
number).

"jkendrick75" wrote:

i'll give it a try, as for the running sum, do the running sum for
the
sumoftotalsort in the group header? if so, if the first number is
total
sort
number is correct, wouldn't the second totalsort number include the
sum
from
the first total sort number? as each one is being grouped by
PartNum...
just
trying to clarify. thanks for keeping with the posts....

"Duane Hookom" wrote:

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.

Did you notice your query that is the record source is not providing an
accurate SumOfTotalSort? If you ran the same Part multiple times, the value
will be multiple times higher.

I tested by creating a simple query of 6/22/2004 to see what the total
number of
SELECT [Date], [Part Number], Sum(TotalSort) AS SumOfTotalSort,
Count(ID) AS CountOfID
FROM [TBL defect count]
GROUP BY [Date], [Part Number]
HAVING [Date]=#6/22/2004#;

One record from this query is
Query1
Date PartNum SumOfTotalSort CountOfID
6/22/2004 324017A 420 1

Add the other table to the same query and you will see:
6/22/2004 324017A 840 2

Note the SumOfTotalSort is doubled.

Consider using this as your starting point for your report's record source
SELECT Date, [Part Number], SortTime, TotalSort,
NCM_Num, Containment, PlantNum,
Val(Nz((Select Sum(DefQuantity)
FROM tblDefects
WHERE tblDefects.ID = [tbl Defect Count].ID),0)) AS Defects
FROM [TBL defect count];

--
Duane Hookom
MS Access MVP
--

"jkendrick75" wrote in message
...
i have posted a very simple website located at
http://mysite.verizon.net/jkendrick75
the site has a couple of screen caps to show what the current design of
the
report looks like, the relationship screen between all tables, and a
screen
cap of tbl Defect Count as a datasheet with a subtable opened up. the
subtable is from tblDefects. it also provides two different reports saved
as
.snp files, downloaded as a zip file. a copy of the entire database is
also
available to download as a zip file (265 Kb in size). if you have any
other
questions or ideas, i am still listening and will do my best to answer
your
questions. thank you for sticking with this thread for so long.

"jkendrick75" wrote:

the control source for the SumofTotalSort in the SumofTotalSort Header is
SumofTotalSort. the textbox control in the SumofTotalSort Header is
named
TotalSort.

"Duane Hookom" wrote:

What is the control source of the text box in SumofTotalSort? I expect
that
this should not be a Sum().

--
Duane Hookom
MS Access MVP


"jkendrick75" wrote in message
...
ok, here goes
=Page Header======================
Part Total Sorted/Part Total Defects/Part
=SumofTotalSort Header============
296,933
=Detail===========================
012007-017 324
=SumofTotalSort Header============
349,259
=Detail===========================
46-0426-09 275
=Page Footer======================
Page 1 of 3
=Report Footer====================
sum of sorted parts sum of total defects

The first amount in the SumofTotalSort header should be 10,261. the
second
amount of 349,259 should be 26,619.

i am in the process of putting together a website where two report
snapshots
have been made. 1 is what the report should look like, the other is
what
i
am getting now. also a copy of the full database will be available as
a
zip
file for download (241kb in size)

when i get this running i will post the link in my next reply
thanks for your patience and help in this matter.
"Duane Hookom" wrote:

Please provide a sample output of what you are getting. Refer back
to
the
reply where I took the time to layout a display of data.

--
Duane Hookom
MS Access MVP


"jkendrick75" wrote in
message
...
ok, tried it, not sure if i was doing it like you were expecting,
but
the
numbers were the same as before, until i started doing a running
sum
for
sumoftotalsort. these numbers were in the 6 digit range when it
should be
barely into 5 digits (just over 10,000 parts sorted for a
particular
part
number).

"jkendrick75" wrote:

i'll give it a try, as for the running sum, do the running sum
for
the
sumoftotalsort in the group header? if so, if the first number
is
total
sort
number is correct, wouldn't the second totalsort number include
the
sum
from
the first total sort number? as each one is being grouped by
PartNum...
just
trying to clarify. thanks for keeping with the posts....

"Duane Hookom" wrote:

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.

yeah, that's been the problem i've been trying to solve. i used the
suggestion that you gave in your last post but i removed alot of the extra
stuff like the ncm_num and such so now the sql of the query looks like this

SELECT [TBL defect count].[Part Number], Sum([TBL defect count].TotalSort)
AS SumOfTotalSort, Sum(Val(Nz((Select Sum(DefQuantity) FROM tblDefects
WHERE tblDefects.ID = [tbl Defect Count].ID),0))) AS Defects
FROM [TBL defect count]
WHERE ((([TBL defect count].Date) Between
[Forms]![frmDateRangesums]![StartDate] And
[Forms]![frmDateRangesums]![EndDate]))
GROUP BY [TBL defect count].[Part Number], [TBL defect count].ID;

now the report groups by the part number for each instance of the part
number during the date range. what the report is for is to show the parts
that were contained for a given date range (whether it is 1 day to several
months). now the report is showing the total sorted and total defects for
each time it was sorted. e.g.:
part 012007-017 was sorted 5 times during a 1 week period. the report will
show
part number total sorted defect quantity
012007-017 10 5
012007-017 15 6
012007-017 25 9
012007-017 35 10
012007-017 80 5

i want the report to show this as one instance of
012007-017 165 35

it is looking like you are on the right track, however when i remove the id
field in the query i get an error of
"You tried to execute a query that does not include the specified expression
'ID' as part of an aggregate function."

i have no clue where to go from here...

"Duane Hookom" wrote:

Did you notice your query that is the record source is not providing an
accurate SumOfTotalSort? If you ran the same Part multiple times, the value
will be multiple times higher.

I tested by creating a simple query of 6/22/2004 to see what the total
number of
SELECT [Date], [Part Number], Sum(TotalSort) AS SumOfTotalSort,
Count(ID) AS CountOfID
FROM [TBL defect count]
GROUP BY [Date], [Part Number]
HAVING [Date]=#6/22/2004#;

One record from this query is
Query1
Date PartNum SumOfTotalSort CountOfID
6/22/2004 324017A 420 1

Add the other table to the same query and you will see:
6/22/2004 324017A 840 2

Note the SumOfTotalSort is doubled.

Consider using this as your starting point for your report's record source
SELECT Date, [Part Number], SortTime, TotalSort,
NCM_Num, Containment, PlantNum,
Val(Nz((Select Sum(DefQuantity)
FROM tblDefects
WHERE tblDefects.ID = [tbl Defect Count].ID),0)) AS Defects
FROM [TBL defect count];

--
Duane Hookom
MS Access MVP
--

"jkendrick75" wrote in message
...
i have posted a very simple website located at
http://mysite.verizon.net/jkendrick75
the site has a couple of screen caps to show what the current design of
the
report looks like, the relationship screen between all tables, and a
screen
cap of tbl Defect Count as a datasheet with a subtable opened up. the
subtable is from tblDefects. it also provides two different reports saved
as
.snp files, downloaded as a zip file. a copy of the entire database is
also
available to download as a zip file (265 Kb in size). if you have any
other
questions or ideas, i am still listening and will do my best to answer
your
questions. thank you for sticking with this thread for so long.

"jkendrick75" wrote:

the control source for the SumofTotalSort in the SumofTotalSort Header is
SumofTotalSort. the textbox control in the SumofTotalSort Header is
named
TotalSort.

"Duane Hookom" wrote:

What is the control source of the text box in SumofTotalSort? I expect
that
this should not be a Sum().

--
Duane Hookom
MS Access MVP


"jkendrick75" wrote in message
...
ok, here goes
=Page Header======================
Part Total Sorted/Part Total Defects/Part
=SumofTotalSort Header============
296,933
=Detail===========================
012007-017 324
=SumofTotalSort Header============
349,259
=Detail===========================
46-0426-09 275
=Page Footer======================
Page 1 of 3
=Report Footer====================
sum of sorted parts sum of total defects

The first amount in the SumofTotalSort header should be 10,261. the
second
amount of 349,259 should be 26,619.

i am in the process of putting together a website where two report
snapshots
have been made. 1 is what the report should look like, the other is
what
i
am getting now. also a copy of the full database will be available as
a
zip
file for download (241kb in size)

when i get this running i will post the link in my next reply
thanks for your patience and help in this matter.
"Duane Hookom" wrote:

Please provide a sample output of what you are getting. Refer back
to
the
reply where I took the time to layout a display of data.

--
Duane Hookom
MS Access MVP


"jkendrick75" wrote in
message
...
ok, tried it, not sure if i was doing it like you were expecting,
but
the
numbers were the same as before, until i started doing a running
sum
for
sumoftotalsort. these numbers were in the 6 digit range when it
should be
barely into 5 digits (just over 10,000 parts sorted for a
particular
part
number).

"jkendrick75" wrote:

i'll give it a try, as for the running sum, do the running sum
for
the
sumoftotalsort in the group header? if so, if the first number
is
total
sort
number is correct, wouldn't the second totalsort number include
the
sum
from
the first total sort number? as each one is being grouped by
PartNum...
just
trying to clarify. thanks for keeping with the posts....

"Duane Hookom" wrote:

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.