Need help importing Access data

[Rick911]

I want to use Excel to build the chart report since they suck in Access. Basically the query will be run every month and it will show all calls in the database and group them by month. The query will contain the total amount of downtime in minutes by month.

In Excel the first column (A) is the month and year, the second (B) is the downtime in minutes, both of which come from the access query. The third (C) is this formula =SUM(B2)/60+0.05*100/10*0.1 . This is being used to convert the minutes into hours and tenths. Then I added a chart that will show a running 12 months of downtime with per month. The data for the chart comes from the C cell range (C2-C13) but the first month in the A column is September, then the second is October and that’s it since we haven’t ran into November yet. I included cells A2- A 13 as my range for the X axis but only the first two cells have a value since it started in September.

The problem I’m running into is when I refreshed the data it pulled in downtime for November, it placed November in A4 where it should and it placed the downtime in minutes in B4 where it should but the C4 (converted minutes to hours) column dropped the reference to the cell C4 and moved everything up by one so, the C column goes from B4 to B6 and then it’s off by one the rest of the way down the column. How can I lock the C column from changing each time a new month is added? The cell highlighted in this screen shot was referencing B5 before I refreshed it , then it change to B6.
excel.png

Thanks
Rick

[Rick911]

Ok, I tried going about this a different way. In my query in Access I am converting the minutes to hours and tenths by adding this
MyTime: Int(((([Sum Of Down Time]/60)+0.05)*100)/10)*0.1.

MyTime.png

And this works in Access as expected, but when I bring the data into Excel as a table or pivot table , the cells are empty. It brings in my other data cells such as month and downtime in minutes correctly. Why won't Excel import the values of my query for this field ? If I can get this figured out I can drop the idea of converting it in Excel and running into the issue I posted above.

Thanks for any help
Rick

[alansidman]

How are you bringing the data into excel? Are you using VBA and the DoCmd.transferspreadsheet method or are you linking the query to an excel spreadsheet and reading it directly into Excel?

Look at this link on exporting a query to a particular excel spreadsheet.

http://accessblog.net/2006/07/export...cel-range.html

[Rick911]

When I first build my pivot table and chart I opened Excel and selected to get external data from Access. Then I browsed to my query and saved the Excel file. Now when I open the Excel file I go to Data, refreash all and this updates my charts. The problem I'm running into is when it brings in the data for the field "MYTime" the cells are emtpy but the other columns of data are imported correctly.

Rick

[alansidman]

Did you look at the link I provided. It is an alternative to doing what you are doing. I have no idea why the issue you have exists. It is difficult to diagnose a patient in absentia.