Solved: Simple Chart with Series in Access... Am I missing a Trick?

[itipu]

I am completey lost here.. it is so simple in Excel yet seems impossible in Access. I think exporting to Excel might be an easier option but last resort check with gurus http://www.dbforums.com/db_images_v3/images/smilies/wink.gif

So I have a little DB, still in developmet stage but works fine.. It includes query by subform..

I tried charts in both report (repChemistry) and a form (frmGraph).. none really do what I want http://www.dbforums.com/db_images_v3/images/smilies/wink.gif

Now all I want displayed in my chart are the results of querying/filtering on the Main Form to be listed as follows:

X axis - items from Value field (I don't want sum, don't understand why Access insists on SUM/MIN etc, just values as they are in the query would be nice)
Y axis - items from SampleDate, i.e date

Dots on the graph should be Platfom (i.e. BA, BB, BC or BD). So you get platform values over time.. easy (at least in Excel), each platform is a series.

Now.. as charts like to be linked to queries.. I coded for the subform filtered results to be recorded into a qryDataExport.. to get that query populated cmd Send to Excel needs to be clicked on the Main Form (I am already preparing export to Excel as I have little hope of getting this chart to work)..

And so the chart can be based on that query.. but it still doesn't work.. not sure I am explaing this well http://www.dbforums.com/db_images_v3/images/smilies/smile.gif

Any "know how" would be much appreciated as I am stuck!

[OBP]

itipu, one of the problems that you have is that there are Multiple Values for each Date, I have tried plotting them Excel and can't do it, let alone in Access whose MS Graph is abysmal compared to Excel.
Perhaps my Form choices were wrong.

[itipu]

Yes, so you have to use all drop down etc... so before you can get to the graph, easily done by tweaking button enable function.. so as far as the end result goes, you have different values for the Platform; Medium, Location, Test are always the same; Date is whatever it might be and so are values and units (units will also be the same as they depend on the Test value)...

So below is the graph built in Excel... again bare in mind this is like 0.1% of the data, so there will be more dates and so graph will get wider.

No, I am sensing its time to forget about this in Access... but I think even Excel automation will be complicated. Once the results of the subform query are exported, they will need to be sorted by Column B (easy)... but then you need define series, i.e. you need to track when the value of the cell in Column B changes, so the range of cells in the same column where value is the same will become series, x and y axis.. just the column will change.. now I reckon that ain't easy..

Any thoughts appreciated.

Thanks a lot

Mike

[OBP]

Mike, I think that your Query Structure is the problem.
I can reproduce your Graph in Access by re-arranging the data, but when you reopen the form it reverts back to the query structure.
Do you need all the data in the Query?
When I reproduce your Excel selection in the Mainform I get completely different results to the Excel ones, in fact I get 0.04 for all Values.

[itipu]

Hi OBP, thanks for reply..

Sorry I removed a lot of data for the sample database I uploaded.. and also I added different data in Excel.. but its just values..

I suggest best selection is: BD - Oil - Storage Cells - PAHs - and dates. You will then have a good range of values.

Now which query are you talking about? I do need all the data that is filtered (apart from Measurement really). I thought by saving filtered data into qryDataExport the problem would be resolved... but I guess not. Would it help if instead of a qryDataExport, filtered data would be saved in a temporary table, say tblDataExport? and the graph (either placed in the report or form) would lift the data from there?

P.S. I am assuming you are talking about Main Form? or do you mean frmGraph? Obviously the idea is to pass different query results (in a shape of a qryDataExport for graph or RepSQL for report). And results change on filtering.. i.e. clicking the button Save to Excel which triggers the generation of qryDataExport can just stop there, or it can generate tblDataExport (I think).. and every time you filter the same can happen (without clicking any button, by moving the code into MultiSelect() ).

Much appreciate your time on this!!!

Thanks, Mike

[OBP]

Mike, I was talking about the Export Query produced by the MainForm.
On the Graph datasheet it has to look like this
Date BB BD
01/01/2010 0.5 0.7
01/01/2010 1.2 0.6

etc.
I am not sure if having a Time element in the date would help.
At the moment I am trying to find out how to get the correct Query Format to achieve that datasheet format.

[itipu]

Thanks! I see exactly what you mean as I previously was thinking about that, or rather re-arranging my table to suit the graph's data sheet, but that of course is no good for the rest of the database.

Afraid changing the query format is a bit over my head.. ;) Needless to say thank you so much for persuing this.

Thx

Mike

[OBP]

Mike, I must admit I am struggling with this one, the MS Graph Wizard is a pig to use. I can create a Crosstab query with the corretc data layout, but the graph wizard refuses to handle it.
I will see if the Pivot Table form is any better.

[itipu]

Hmm sounds like you got further than I did with the crosstab query. As a workaround though, if the query data is saved in a temporary form in a different format, i.e. in the format you indicated in an earlier post, would the Wizard still not like it? It clearly doesn't like the query but I think saving into a form might be worth a short... though I have no idea how to do it... just a thought.

Cheers

Mike

[OBP]

Mike, I have it in the correct format on a Pivot Table, but the Graph insists on using Sum instead of the actual values like the MS Graph Wizard.
The Wizard won't ike it whether it is a Table or Query and won't interact with a form, which is just a dataset anyway.
I am beginning to think it may be better to use a Crosstab query and Link Excel to it.

The problem is you want all the Flexibility built in to it and I can't see how to do that.

[OBP]

I have linked an Excel sheet to the Crosstab Query and that works OK.

[OBP]

However the Crosstab will only work if the Date has a Time element to it to dfifferentiate between the Date records.

[itipu]

I see... would you mind sharing you end result with crosstab and Excel? Would be great to see both, i.e. one with the pivot table but still sum.. and the one with Excel ;)

By changing date to include time, do you mean that tblMain SampleDate field needs to loose Input Mask and become General Date? or do you mean SQLStr formattin, i.e. "\#mm/dd/yyyy\#" ... chaning it in the tblMain is not an issue, and probably changin SQLStr is fine too, I has some issues as it was not pulling the right results because it would always count time as midnight or something..

Anyways would be greatfull to see what you achieved with Excel.

Thanks a lot

Mike

[OBP]

Mike, here is the Access database with the Excel workbook, you will have to re-establish the Link to the database as at the moment it is looking for it on my Desktop.

You can create the same type of link yourself by clicking on the crosstab query (just once, not to open it) Copy, goto the worksheet, select the starting cel and Paste Special>Link.
Each time you open the Excel workbook it will ask if you want to re-establish the link.
You can of course automate the opening of Excel as required.

I was thinking that you could enter the date in it's current format and input mask on the form and then add the time part of it using VBA, as long as there is one second difference between the records the Crosstab will list the results, as soon as they become just dates the Crosstab wants sum/average them etc. I currently get around this by using Max, Min, first or Last and because the dates are all 1 second different it treats them as separate records.

[itipu]

Thanks, either I am a little confused or something else..

I have Access 2000 at work but at home I use 2007 ... so far everything worked the same..

Now I copied the link to Excel worksheet.. no problem.. now every time I do my selection on the Main Form and click the button Send to Excel.. go to your Excel worksheet, open it up... I get error in Access (not Excel) saying MS database engine doesn't recognise '[Forms]![Main Form]![cboMedium] as valid field name or expression... Chart1 in your worksheet remains unpopulated.. I get the same error if I try to view qryDataExport_Crosstab...

As for the dates.. I've noticed you tried adding time to a few records.. the thing is, my SQL on the Main Form stops working... Best thing I reckon is to somehow ammend qryDataExport... but not too sure at the moment since I don't think I am getting the same results as you are getting on your PC.

Cheers

Mike

[OBP]

Check that the qryDataExport query that you are using is the same as mine, it has to have all of the Search Criteria declared as "Parameters".

[itipu]

Sorry,

I didn't quite understand how it worked initially.. I was pressing my old Send to Excel button without realising you changed the way qryDataExport works.. it now works fine. I see what you meant about the date... the qryDataExport_Crosstab needs to see some difference in the dates..

Now.. I am not sure how this date amandment is going to work. My observations so far...

1) You've changed the format of some of the dates in tblMain to allow for a 1 second difference in the time stamp. While these dates are now visible in the dates drop down boxes on the Main Form, selection using them doesn't quite work, because of my elaborare SQL format.

2) The best way would be to amend dates without any visibility to the user, i.e. so that SampleDate in both table and drop downs on the Main Form would remain in the mm/dd/yyyy format. Not sure how to go about this... or really table can be changed as long as Main Form doesn't show time stamp, but from what I tried already with SQL I had lots of issues with date with time format in my SQL stringing...

3) Finally, lable on the Excel graph shouldn't show time stamp, but date only (I guess not an issue really as lables could probably be easily formatted)..

All in all I think the biggest issue is point 2... I thin before the routine starts a table format function should be initialised to chang date format (?) ... I could possibly see that VBA could be provided to create the Excel bit from scratch each time including graph.. Would I be assumign correctly that Form1 with graph, could principally get its source from qryDataExport_Crosstab and the issue is only that it does this wiered thing with y-axis?

Would appreciate some input on point number 2.. I feel a bit bad for this surely is taking lots of your time. Thanks a lot

P.S. just read this..

http://www.eggheadcafe.com/software/aspnet/31764527/need-help-with-group-by-t.aspx

And particularly:
Use a totals query joined in a select query that has the dates. The totals

Sounds like an elaborate work around.
will be repeated for every date. In a report have it hide duplicates.

Mike

[OBP]

I think I would look at using your current qryDataExport query as an append query to put the search results in to a table, then run a VBA Recordset to increment the same Dates by 1 to 5 seconds (the choice is yours based on how many daily records you can have) and then base the Crosstab query on the new table.
Before running the search query again reset the new table's records back to zero ready for a new run.

That is all providing that the Excel graph gives you what you want.

I will take a look at the link you supplied.

[itipu]

Thanks, will give it a try tomorrow! This is about a 3rd time I use Access in my life, I did however do a fair bit of VBA coding for Excel with AD automations and Oracle links etc..

Will try, might disturb you again if I get stuck.. but yes, the Excel graph gives me exactly what I wanted.

Thanks a lot

Mike

[itipu]

Made a few changes:

1 - Cleaned up dates in tblMain
2 - Enforced the sequence so that all drop downs are selected
3 - Clicking Search button now results in a new table tblDataExport being generated which contains qryDataExport records...

I have noticed that qryDataExport which you've changed doesn't read Platform values correctly, i.e. it always lists both BD and BB even if say BD is selected... this worked previously but I did not revert back for the time being (maybe you needed it for something?)

Next step is to modify SampleDate in this table to include time stamp with say a second difference

After a few test runs got this to work.. seems to work. Will try to generate that Excel chart on the fly.

Dim CustDB As Database, CustTable As Recordset
Set CustDB = DBEngine.Workspaces(0).Databases(0)
Set Temp = CustDB.OpenRecordset("tblDataExport", dbOpenTable)
Dim i As Integer
i = 1

Do Until Temp.EOF
Temp.Edit
Temp!SampleDate = DateAdd("s", i, Temp!SampleDate)
Temp.Update
i = i + 1
Temp.MoveNext
Loop

Cheers for help!

Mike

[itipu]

So got the whole date increment working, in a rather simplistic fashion but I think it will work... will need to test with lots of data but really don't think it would make a huge kind of difference... I guess the more complicated version would to check when SampleDate changes at which stage i would become 0 again.. not sure how to do that...

Now looking at generating that illusive graph on the fly...

I guess 2 questions:

1) How best to copy data to the Data sheet in the Excel with tblDataExport records..
2) And then set Series, X and Y Values.. I have setup all the preps in the code below.. apart from copy/pasting...

Cheers

Mike

Private Sub cmdSendExcel_Click()
Dim appExcel As Excel.Application
Dim wBook As Workbook
Set appExcel = New Excel.Application
appExcel.Visible = True
Set wBook = appExcel.Workbooks.Add
With wBook
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Data"
.Application.DisplayAlerts = False
Sheets("Sheet2").Select
ActiveWindow.SelectedSheets.Delete
Sheets("Sheet3").Select
ActiveWindow.SelectedSheets.Delete
.Application.DisplayAlerts = True
.Charts.Add
.ActiveChart.ChartType = xlXYScatter
.ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Graph"
.Application.ActiveWindow.Zoom = 100
.ActiveChart.ChartArea.Select
' .ActiveChart.SeriesCollection.NewSeries
' .ActiveChart.SeriesCollection(1).XValues = "=Data!$A$2:$A$5"
' .ActiveChart.SeriesCollection(1).YValues = "=Data!$B$2:$B$5"
' .ActiveChart.SeriesCollection(1).Name = "=Data!$B$1"
End With
'wBook.SaveAs
'appExcel.Quit
'Set appExcel = Nothing
End Sub

[OBP]

Won't manually setting up one or more workbooks and then Linking them to the Query work, rather than trying to set them up on the fly?
I don't have any experience of working with Excel Graphs from Access, but a search on this forum may show some previous Threads on it, or possibly on the Excel Forum or possibly the Knowledge Base.

[itipu]

The immediate problem is really copying the query to Excel. The way you did Copy Paste as link is rather special way.. I can't seem to find any way of doing query copy.. paste as link in Excel is obvious.

Otherwise exporting query to Excel doesn't quite give you the desired results.

[itipu]

Maybe I should stick with manual ;)