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.
:
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
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).
:
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....
:
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
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.
:
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
--
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.
:
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
--
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.
:
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 Number
EF
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
--
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.
:
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" <
[email protected]>
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.
