New problem. With 4 charts on the report for each product, I only want to
run one at a time. The charts are based [Grading_W-2] which joins 2
pass-through queries to an Oracle server with 2 local tables. The resulting
data is VERY large and each chart is doing a crosstab on that data. From my
main menu, I am establishing the filter criteria in the Where Condition of
the DoCmd.OpenReport Action as suggested in one of your other postings to
someone else. When the report opens, one of the charts is blank, but the
other three work as they should. If I switch to design view and then back to
preview, all four charts work.
Can you think of any reason why the fourth chart only works after I cycle to
design and back to preview?
-Cevin
:
Finally got it working. I think one problem was making sure that the data in
my fields between the row source and the record source matched up correctly.
However, even after I fixed that, I still received the following error when I
tried to select fields as master/child links:
"SUBREPORT FILE LINKER
Can't build a link between unbound forms"
I found one reference in the help file to establishing a relationship
between the row source and the record source, which did not previously exist.
I am not sure if this was the last piece of the puzzle that I was missing,
but it does work now. Thanks for your help, and please comment on whether a
relationship has to exist or not for the good of the group.
Thanks, Duane. That's why you're the best.
-Cevin
:
What ever field you use for linking master/child should be a in a report
section where the field value is unique. This could be in the group header
for the field.
--
Duane Hookom
Microsoft Access MVP
:
The Record Source for the Report is:
SELECT Q1.[CAST SERIAL] AS [CAST SERIAL], Q1.[CASTER CLOCK], Q1.[CAST
FOREMAN CLOCK], Q1.[CAST DATE], DatePart('h',[CAST DATE]) AS HOUR, Q1.SHIFT,
Q1.MACHINE, Q1.[NP PLATE], Q2.[INSPECT SERIAL], Q2.[INSPECT DATE],
IIf([DEFECT CODE]<'18',[Grade],'A') AS POTENTIAL, Q2.GRADE, [DEFECT CODE] & "
- " & [Defect_Name] AS Defect, Q2.STATUS
FROM Defect_Codes RIGHT JOIN ([sptPC_Cast_W-2] AS Q1 LEFT JOIN
[sptPC_Grading_W-2] AS Q2 ON Q1.[CAST SERIAL] = Q2.[INSPECT SERIAL]) ON
Defect_Codes.Defect_Code = Q2.[DEFECT CODE]
ORDER BY Q1.[CAST DATE];
This is probably a bad idea since it includes thousands of records, forcing
me to put the charts in the report header. My current train of thought is
that I need to have the row source as a query which identifies each unique
product, [NP PLATE], and thus only returns about 10 records. Then I could
put the chart in the detail section where it belongs and use filters of the
master to limit what product is being reported
The Row Source of the chart is:
TRANSFORM Count(*) AS [Count] SELECT [SHIFT] FROM [Grading_W-2] WHERE [NP
PLATE] LIKE '972210*' AND [STATUS] LIKE '*F' GROUP BY [SHIFT] PIVOT
[POTENTIAL];
Again, [NP PLATE] is the field which holds the product ID and should be the
linked field between the master and child.
Thanks again. I'm on 3rd shift and have limited time to work on this each
night, so thanks for the patience.
-Cevin
:
I believe any file downloaded from the web requires you to right click the
file and choose properties. Click the "UnBlock" button.
What is the exact Record Source of your report?
What is the exact Row Source of your chart?
--
Duane Hookom
Microsoft Access MVP
:
I'm using Access '97. (Thanks Microsoft for not including Access 2003 in the
Office 2003 suite anymore!) I tried opening the database you recommended
from my system at home (since the '97 version at work would not recognize
it), and it said that the source was unsecure and Access would not allow me
to open it for security reasons.
Back to my database, I have to type in [NP PLATE] in both the Link Child
Fields and Link Master Fields properties of the chart. If I try to click the
combo box on the right to select the field, receive the following error
message:
"SUBREPORT FILE LINKER
Can't build a link between unbound forms"
Could this be part of my problem, and if so, how do I correct it?
-Cevin
:
I'm not sure why your linking doesn't work. You might want to download and
look at my "Report with chart using 2 link fields" sample from
http://www.access.hookom.net/Samples.htm
--
Duane Hookom
Microsoft Access MVP
:
Duane, I've been helped by your posts many, MANY times in the past, so I'm
very glad you took the time to answer mine now.
I set the report's Record Source to [Grading_W-2] and listed the filter as
"[NP Plate] LIKE '972210*'". I removed the "[NP Plate] LIKE '972210*'" from
the SQL string of the chart's Row Source, and bound the chart to the report
by putting [NP PLATE] in the Link Master/Child properties of the chart. I
ran the report and the chart has no data in it. I have never had success
with trying to bind charts via the Link Master/Child properties. What am I
missing?
-Cevin
:
If your report's record source contains the [NP PLATE] field, you should be
able to set the Link Master/Child properties of the Chart Control to limit
the chart to a specific product.
--
Duane Hookom
Microsoft Access MVP
:
I am developing a database to report inspection results in a manufacturing
environment. Every week I print a report with 4 charts on it, each relating
to the same product. I print a report for each product. Therefore, the only
thing different between the 10 reports is the product they are about. My
problem is that I have not figured out how to alter the Row Source for the
charts from code and so I have one report with 4 charts for each product.
How can I select a product on a form and have it update the row source of
each of the charts in the table?
Example row source is below.
TRANSFORM Count(*) AS [Count] SELECT [SHIFT] FROM [Grading_W-2] WHERE [NP
PLATE] LIKE '972210*' AND [STATUS] LIKE '*F' GROUP BY [SHIFT] PIVOT
[POTENTIAL];
[SHIFT] = Which of the 3 shifts made the piece
[Grading_W-2] = query combining manufacturing data with grading results
[NP PLATE] = identifies the product on the report. This is the field I need
to change.
[STATUS] = Used to sort different types of grading results
[POTENTIAL] = has the grading results.
Thanks,
Cevin
