What is the record source of your main report?
qryPartbyDateRange is the recordsource. sql for it is:
SELECT [tbl Defect Count].ID, [tbl Defect Count].TotalSort,
Sum(tblDefects.DefQuantity) AS SumOfDefQuantity
FROM [tbl Defect Count] INNER JOIN tblDefects ON [tbl Defect Count].ID
=
tblDefects.ID
WHERE ((([tbl Defect Count].Date) Between
[forms]![frmpartbydaterange]![startdate] And
[forms]![frmpartbydaterange]![enddate]) AND (([tbl Defect Count].[Part
Number])=[forms]![frmpartbydaterange].[partnum]))
GROUP BY [tbl Defect Count].ID, [tbl Defect Count].TotalSort;
What is the record source of your subreport?
report.subqryDefTotal is the recordsource. It's SQL is
SELECT [tblDefects].[DefCode], Sum([tblDefects].[DefQuantity]) AS
SumOfDefQuantity
FROM [tbl Defect Count] INNER JOIN tblDefects ON [tbl Defect
Count].[ID]=[tblDefects].[ID]
GROUP BY [tblDefects].[DefCode], [tbl Defect Count].partnum;
Where on your main report did you place your subreport?
Currently it is in the details section. as i said in previous post was
getting several pop ups asking for part number.
What are the Link Master/Child property values?
they are both linked on the ID field from each table. i tried to get
them
to match on the part number field, but the sub query wasn't showing the
part
number as a possible link.
by going through what you are suggesting, even making changes to
account
for
a date range and a specific part number, the queries are adding up all
defects for each of the 27 differect defect codes, by defect code. the
report ends up being anywhere from 2 to 19 pages, asking for part
number
multiple times.
:
What is the record source of your main report?
What is the record source of your subreport?
Where on your main report did you place your subreport?
What are the Link Master/Child property values?
--
Duane Hookom
MS Access MVP
yes, i did try what you had suggested. after incorporating your
last
post,
this is what i get.
Defect Code Defect Quantity
16 1
24 5
16 47
20 2
Total Parts Ran: 210 Total Defects/Part: 6
i have even tried to do a running sum for the two totals. i get the
same
answer.
if i move the subreport to the footer section of the report, i get
several
pop up boxes asking for the part number.
as before, not sure where to go from here.
:
Did you try what I suggested in my last paragraph and SQL? This
would
place
the total defects number in the record source of the main report
where
summing would be a piece of cake.
If you want to combine all the codes for a part number together,
then
open
the footer for Partnum and move your subreport to the footer.
Change
the
record source of the subreport to:
SELECT tblDefectCount.PartNum, tblDefects.DefCode,
Sum(tblDefects.DefQuantity) AS SumOfDefQuantity
FROM tblDefectCount INNER JOIN tblDefects ON tblDefectCount.ID =
tblDefects.ID
GROUP BY tblDefectCount.PartNum, tblDefects.DefCode;
Change controls on your subreport to match your fields. Then set
your
link
master/child properties of the subreport to PartNum.
--
Duane Hookom
MS Access MVP
message
ok, have put that in now. but now for the summary portion of the
report,
i
am needing to have a total sorted number, and a total defects
number.
i
am
getting the total sorted number without a problem, but i am
having
trouble
getting the total defects number. also, in the details section
of
the
report, i am wanting it to show all of the same defect codes as 1
record.
Part number of 40000, total sorted for each instance is 1292 and
210.
for
the 1292 instance there are two codes (code 16 with 47 defects,
code
20
with
2 defects) and the 210 instance there are two codes (code 16 with
1
defect,
code 24 with 5 defects) the details section should look like
this
24 5
20 2
16 48
with a total defects of 55.
right now my total defects box is in the main report with a data
source
of
[Reports]![subqryDefTotal subreport]![sumofdefquantity]
i did have a '=Sum( )' setup around the datasource at one time,
but
could
not get this to work.
thank you for your help so far.
:
When you do your data entry, are you entering data into a form
based
on a
query of both tables or of a Main Form based on tbl Defect Count
and a
subform based on tblDefects? I would expect a main form and
subform.
The same model should be used in your report. Make a main report
based
on
tblDefectCount and a subreport based on tblDefects.If you want,
you
can
base
your main report on a totals query like:
SELECT tblDefectCount.ID, tblDefectCount.Date,
tblDefectCount.PartNum,
tblDefectCount.TotalSort, Sum(tblDefects.DefQuantity) AS
SumOfDefQuantity
FROM tblDefectCount INNER JOIN tblDefects ON tblDefectCount.ID =
tblDefects.ID
GROUP BY tblDefectCount.ID, tblDefectCount.Date,
tblDefectCount.PartNum,
tblDefectCount.TotalSort;
--
Duane Hookom
MS Access MVP
message
ok, i apologize for such long posts. just trying to give you
actual
data
within my tables. thought that perhaps with actual data from
the
tables
and
actual results from the reports, it would help you get a
better
idea
of
what
i am trying to do and what is actually happening.
anyways.
Sample 1
[tbl Defect Count]
ID Date PartNum TotalSort
2713 1/4/2005 40000 1292
2722 1/5/2005 40000 210
[tblDefects]
AutoID ID DefCode DefQuantity
6660 2713 20 2
6661 2713 16 47
6673 2722 16 1
6674 2722 24 5
when the report that i have now is run, the start date, end
date,
and
part
number are asked for (for Sample 1) and i get the following
answer
for
the
date range of 1/4/05 - 1/5/05 and part number 40000.
i am getting a total of SumOfTotalSort of 3004 if i use a
running
sum
and
a
total of 1502 if i don't use a running sum. the totals for the
sumofsum
of
DefQuantity of 55 is correct.
Sample2
[tbl Defect Count]
ID Date PartNum TotalSort
2704 1/4/2005 AW060352 396
2705 1/4/2005 AW060352 660
2733 1/5/2005 AW060352 531
2756 1/6/2005 AW060352 100
[tblDefects]
AutoID ID DefCode DefQuantity
6693 2733 26 1
6694 2733 24 2
6726 2756 24 1
6727 2756 25 1
6728 2756 26 1
when the report that i have now is run, the start date, end
date,
and
part
number are asked for (for Sample 2) and i get the following
answer
for
the
date range of 1/4/05 - 1/5/05 and part number AW060352.
i am getting a total of SumOfTotalSort of 2,418 if i use a
running
sum
and
a
total of 1,056 if i don't use a running sum. when manually
adding
up
the
numbers i am getting a total 1,687. the total for the sumofsum
of
DefQuantity
of 6 is correct.
hope all this makes sense to you.
:
I won't even try to read, understand, copy to tables,
reformat,...
your
long
examples. I doubt that SortTime and some other fields have
any
bearing
on
your issue. Please try to provide only a couple main records
and
no
more
than 10 detailed records and be accurate so we don't have to
look
back
and
forth between two emails.
I have put a lot of time into attempting to do your work in
the
past
(as
have many others). Please don't make it so darned hard. You
should
be
able
to describe and illustrate you needs in much less complexity.
--
Duane Hookom
MS Access MVP
in
message
ok no prob. the following will be samples of data that i am
using
in
particular.
Sample 1
[tbl Defect Count]
ID Date PartNum SortTime TotalSort NCM_Num
Cont
PlantNum
2713 1/4/2005 40000 5.66 1292
Press
Side
1
2722 1/5/2005 40000 0.5 210
Press
Side 1
[tblDefects]
AutoID ID DefCode DefQuantity
6660 2713 20 2
6661 2713 16 47
6673 2722 16 1
6674 2722 24 5
Sample2
[tbl Defect Count]
ID Date PartNum SortTime TotalSort NCM_Num
Containment
PlantNum
2704 1/4/2005 AW060352 1.66 396 Press Side
1
2705 1/4/2005 AW060352 1.66 660 Press Side
1
2733 1/5/2005 AW060352 4 531 GP 12
1
2756 1/6/2005 AW060352 1.5 100 Press Side
1
2807 1/10/2005 AW060352 2.66 333 Press Side
1
2856 1/10/2005 AW060352 3 330 GP 12
1
2857 1/10/2005 AW060352 3 660 GP 12
1
2858 1/10/2005 AW060352 3 660 GP 12
1
2859 1/11/2005 AW060352 1.5 165 GP 12
1
2860 1/10/2005 AW060352 4 663 GP 12
1
2893 1/12/2005 AW060352 0.58 594 Press Side
1
2903 1/12/2005 AW060352 4 666 Press Side
1
2904 1/14/2005 AW060352 4.58 330 Press Side
1
2921 1/13/2005 AW060352 4 669 Press Side
1
2940 1/17/2005 AW060352 6.83 666 GP 12
1
2941 1/17/2005 AW060352 1 234 GP 12
1
2984 1/19/2005 AW060352 0.17 33 GP 12
1
3011 1/20/2005 AW060352 2 528
GP
12
1
3012 1/20/2005 AW060352 2.08 660 GP 12
1
[tblDefects]
AutoID ID DefCode DefQuantity
6693 2733 26 1
6694 2733 24 2
6726 2756 24 1
6727 2756 25 1
6728 2756 26 1
6772 2807 7 1
6773 2807 24 1
6774 2807 26 1
6866 2859 24 3
6867 2860 26 1
6868 2860 7 1
6869 2860 24 1
6924 2903 24 1
6925 2903 15 3
6926 2903 20 2
6927 2904 20 13
6951 2921 26 9
6977 2940 7 1
6978 2940 26 6
6979 2941 15 2
6980 2941 20 1
7034 2984 7 1
when the query that i have now is run, the start date, end
date,
and
part
number are asked for (for Sample 1) and i get the following
answer
for
the
date range of 1/1/05 - 1/20/05 and part number 40000.
PartNum SumOfTotalSort SumOfSum Of DefQuantity DefCode
40000 1502 48 16
40000 1292 2 20
40000 210 5 24
On the report i am getting a total of SumOfTotalSort of
3004
if i
use a
running sum and a total of 1502 if i don't use a running
sum.
the
totals
for
the sumofsum of DefQuantity of 55 is correct.
for sample 2 i get (for same date range but part number
AW060352)
PartNum SumOfTotalSort SumOfSum Of DefQuantity DefCode
AW060352 4488
AW060352 2962 19 26
AW060352 2458 9 24
AW060352 1695 4 7
AW060352 1230 16 20
AW060352 900 5 15
AW060352 100 1 25
on the report i am getting the correct answer for the total
sorted
of
13,833
only when i am running it as a =Sum([SumOfTotalSort]). if
i
am
not
running
as a =Sum([SumOfTotalSort]), then i get an answer of 4,488.
which
is
the
same amount as the amount with null values for defcode and
defquantity.
hope this helps.
:
You gave us the two "quizzes" but forgot the answer key!
Why
would
you
not
tell us in each example what you calculate the total to
be?
How
about
some
sample records?
--
Duane Hookom
MS Access MVP
"jkendrick75" <
[email protected]>
wrote
in
message
I am needing a query to power a report that my boss wants
done
by
this
friday. i have been working on this report for a couple
of
weeks
now,
without getting anywhere.
this is what i have for my tables.
Parent table: [tbl Defect Count]
ID (autonumber; PK)
Date
Part Number
SortTime
TotalSort
NCM_Num
Containment
PlantNum
Child Table: [tblDefects]
AutoID (autonumber; PK)
ID (linked to [tbl Defect Count].[ID])
DefCode
DefQuantity
the relation ship between these two tables is a 1 to
many
relationship.
for
1 record in [tbl Defect Count] there could be anywhere
from
1
to
27
records
in [tblDefects], or no record at all.
what my problem is currently is if there are more
records
(for
a
given
part
within a given date range) in [tbl Defect Count] than
there
are
records
(records that correspond to an ID in [tbl Defect Count])
in
[tblDefects] i
need to have the totals box in the footer of the report
set
for a
running
sum. however, if there are more records (for a given
part
within
a
given
date range) in [tblDefects] than there are records
(records
that
correspond
to an ID in [tbl Defect Count]) in [tbl Defect Count] i
need
to
have
the
totals box in the footer of the report set for a
non-running
sum.
example:
[tbl Defect Count] has 2 records (or 2 ID numbers) of a
part
within
a 1
month date range. however [tblDefects] has a total of 3
records
between
the
two records in [tbl Defect Count]. i need to have my
totals
box
in
the
report footer set as a non running sum, otherwise my
total
is
twice
than
what
it should be.
example 2:
[tbl Defect Count] has 8 records (or 8 ID numbers) for a
part
within
a
1
month date range. [tblDefects] has a total of 6 records
between
the
8
ID
numbers in [tbl Defect Count] for the date range. I
need
to
have
my
totals
box in the report footer set as a running sum to count
all
records
and
give
me a correct total.
any help in this matter will be greatly appreciated.
thanks
in
advance
