[AccessD] Excel Graphs from Access Data - Ideas, Please

Tina,
What I generally do is
1) Save the user's selections (date parameters, etc.) selected from list
boxes, spinners, or whatever in the worksheet. 
2) Run update queries that update Access tables based on these selections.
3) Run select queries with joins (or where clauses using subqueries)  based
on the tables in 1)
4) Copy the resulting recordset into a hidden sheet that can be referenced
by the graph.
5) If desired, ranges can be made expandable to accomodate changes in the
number of records.

I particularly recommend you look into pivot charts which give the user
flexibility to change the displaye data after it has been downloaded. I use
DAO when the data comes from Access but it can be downloaded directly from
other sources, such as an AS400, using ADO. I tried to post sample code but
the list rejected it as too long ;-(

Jim Hale
-----Original Message-----
From: Tina Norris Fields [mailto:tinanfields at torchlake.com]
Sent: Friday, September 22, 2006 10:43 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Excel Graphs from Access Data - Ideas, Please


Hi All,

Client is an oil and gas producer.  Daily data of several kinds are 
reported from the field on a daily basis.  Client would like to be able 
to see graphs showing, for instance, daily production for well A for a 
selectable time range (the last month, this last week, this last 
quarter, whatever) - he would also like to be able to see daily 
production for two or three wells at a time on the same graph, or for 
all the wells in a given oilfield (individually or as a total). 

Once a query is devised for selecting the date range, the specific 
wells, or the complete oilfield (separately or in combination), I want 
to export the selected data to Excel and draw the appropriate graphs.  
And, I want to do all that fairly automatically. 

The client would like users to be able to tweak data in Excel for the 
graph, if need be - as in to exclude outlying data points - but, of 
course, not alter the actual data captured in the Access data table.  
So, I am thinking of using a make-table query and sending the data from 
the "made" table to Excel.  Is this a good idea?

Some of you have done some automation with Access and Excel, so I would 
appreciate knowing what the pitfalls are that I need to be careful of.  
I am thinking to build essentially template Excel files with already 
configured graphs for the choices that are going to be offered to the 
user - which well or wells, or which well-field, and for what date 
range?  Does this sound like a good idea?

With great care, I believe I can code

I really would like your creative and technical advice, here - so, all 
ideas are welcome.

Thanks,
Tina

***********************************************************************
The information transmitted is intended solely for the individual or
entity to which it is addressed and may contain confidential and/or
privileged material. Any review, retransmission, dissemination or
other use of or taking action in reliance upon this information by
persons or entities other than the intended recipient is prohibited.
If you have received this email in error please contact the sender and
delete the material from any computer. As a recipient of this email,
you are responsible for screening its contents and the contents of any
attachments for the presence of viruses. No liability is accepted for
any damages caused by any virus transmitted by this email.