Programming an Interactive Chart/Graph problem with separate columns

[pjdube]

Hi Everyone!

Need help again!

Ok, so I have a table of data, which goes somewhat like this:

WE	Stat1	Stat2	Stat3	Stat4	Stat5	Stat6	...
2 May 19	1000	200	400	890	9876	765642
9 May 19	2345	243	456	987	908	8765
16 May 19	5344	564	653	9876	2090	90354
...

There are a total of 70 columns (of stats) and 347 rows (of Dates).

I created an Unbound Form which has a Line Chart on it, with three text boxes:

Date From ______
Date To ________
Stat ___________



What I am trying to achieve, is the user can enter the date range and which
stat he wants, and then it will automatically generate the line chart (graph).

I can do a query for a specific column, but can't figure out how to do it so
that when the user inputs "Stat2" (for instance) that it pulls up all the figures
for "Stat2" (and only "Stat2") and the Date and graphs it.

I am able to create a query with the Date From and To option, however have
not been able to program it to accept the "Stat" (column).

Here is what I have right now:

SELECT LRGRAPH.WE, LRGRAPH.Stat1
FROM LRHGRAPH
WHERE (((LRGRAPH.WE) Between [Forms]![LonRangeSearch]![DateFrom] And [Forms]![LonRangeSearch]![DateTo]));

But this only pulls up the first stat. How can I change "Stat1" in the query automatically when the user types in "Stat2".

I hope someone can point me in the right direction!

Phil

[June7]

Fields cannot be dynamic in a query object. Would have to use code to set graph RowSource to revised query statement.

An expression for a calculated field could conditionally select data from desired field but with 70 columns that would be too long an expression so would require a VBA custom function.

Your table structure is not normalized. If it were, dataset could be filtered for a "Stat2" criteria. A UNION query can rearrange multiple fields to a normalized structure but UNION is limited to 50 SELECT lines - you have 70 Stat fields.

[pjdube]

June 7,

Thanks! Ok, so if my make each stat a separate query, then how would I have the "Stat:" TextBox on my Form select that query and run the date range?

Phil

[June7]

I would not make 70 queries. I would have VBA build SQL statement and set graph RowSource property. However, I've never tried that with graph on report. Could be complicated. But then any method will be complicated with your data structure.

Other options:

VBA populates a 'temp' table that is used as graph RowSource.

VBA modifies query object with QueryDefs.

No matter what, VBA will be involved.

I did quick test trying to set graph RowSource on report and it fails.

Easiest approach may be a temp table.