Line Chart Report with Criteria in Header

[clem]

I am having trouble making a line chart report with the criteria on top of the chart.
I am basing it on a query that includes:
Product, Date-Time, Min-value, Max-value, and Actual_value
I can easily make a graph of Min, Act, and Max by date-time, but can't figure out how to put the product name in the header.
The query contains many products, each of which has dozens of date-time specific values. I put in a criteria in the query to limit it to [What_Product], but can't get the specified value in the report.
I want a one page report with the product chosen at the top and the graph below.

I tried Report - Sub-report, but it says they are unbound. Probably means I need a unique key in common? I don't want to use sub-reports, but can't figure it out with or without.

Any suggestions?

 

[dhookom]

Who cares what a Wizard says if you don't need the wizard. You can add a text box to the report:
Name: txtProduct
COntrol Source: =[What Product]
Set the Link Master/Child of the chart control to your product field in the chart and the txtProduct from the report.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[clem]

When I tried this, I get an error "Can't build a link between unbound forms"

 

[dhookom]

Then add a record source/query to your report of the Product table and bind one control to the ProductID or whatever you want to use to set the link master/child properties.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[clem]

Thanks. I think you have hit on the heart of my problem. I don't have a product table. I know that this isn't normalized, but didn't think it would be a problem in such an easy report requirement. Do I have to add a product master and tie the field in the main table to it? Or can I just have a reduntant table (or query?) with that field?

Sorry to be so dense. I just keep thinking this is a simple requirement and shouldn't force me to 3rd normal form.

 

[dhookom]

You only need a query that is grouped by your product.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[clem]

I have been trying that. The graph is still unbound.

 

[dhookom]

I don't know much about your tables and fields. Does your report have a record source? If so, what is it? What is the Row Source property of your Chart?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[clem]

I built a simplified version with the following:
Table1 with Product, Test_Date, and Weight (no key)
Query1 with Product (criteria = [What Product]), Test_Date, and Weight
Report1 with record source Query1 and a chart inside of it with Row Source:
SELECT (Format([Test_Date],"MMM 'YY")),Sum([Weight]) AS [SumOfWeight] FROM [Query1] GROUP BY (Year([Test_Date])*12 + Month([Test_Date])-1),(Format([Test_Date],"MMM 'YY"));

When I try to link master and child fields it says "Can't build a link between unbound forms"

Thanks for your time. Would it help if I sent you this very small, simple test db with only 10 records?

 

[dhookom]

The row source query of the chart should not have a where clause associated with it. The report should be based on a query like:

SELECT DISTINCT Product
FROM Table1
WHERE Product = [Enter Product];

Then set the link master child properties to Product. Your chart row source will need to have the Product field included. Add a text box to the detail section of your report and bind it to the Product field.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]