incorrect sums in report using 2 tables

Just create a new query based on the saved query from the sql I provided.
==qtotSortsAndDefects======
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];
New query as record source for report
SELECT [Part Number], Sum(TotalSort) as SumTotalSort,
Sum(Defects) as SumDefects
FROM qtotSortsAndDefects;


--
Duane Hookom
MS Access MVP


"jkendrick75" wrote in message
...
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.

Hey guys, how about trimming some of the quoted old messages
in this thread? It's starting to exceed my mail program's
message size limits.

You would probably be getting tired of it too if you weren't
top posting.

--
Marsh
MVP [MS Access]

will be starting a new thread using the "incorrect sums in report using 2
tables rethread" as the subject. i tried the new query setup and was getting
very oddball numbers. will add a screen shot to the website later today to
show you what i was getting.

"jkendrick75" wrote:

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.