If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
It worked before... .xls in Access
I'm trying to create a pivot chart table using data from an Excel sheet. This
is my data: Sorry about the wrap. Total Hours Start Date Techs. Required End Date Projected End Date Work Days to Complete Actual Days Techs./Month Techs. Required 8 Hour Days 1000 1/3/2005 1 1/31/2005 1/31/2005 21.00 28 5.95 5.95 125.00 600 2/3/2005 4 3/1/2005 18.75 26 3.75 - 75.00 450 3/1/2005 3 3/25/2005 18.75 24 2.45 - 56.25 300 4/1/2005 2 4/27/2005 18.75 26 1.79 - 37.50 1200 5/2/2005 5 5/31/2005 5/31/2005 22.00 29 6.82 6.82 150.00 500 6/1/2005 3 6/29/2005 20.83 28 2.84 - 62.50 765 7/1/2005 5 7/28/2005 19.13 27 4.55 - 95.63 275 8/1/2005 2 8/24/2005 17.19 23 1.49 - 34.38 450 9/1/2005 3 9/27/2005 18.75 26 2.56 - 56.25 230 10/3/2005 2 10/21/2005 14.38 18 1.37 - 28.75 830 11/1/2005 5 11/29/2005 20.75 28 4.72 - 103.75 1000 12/1/2005 6 12/29/2005 20.83 28 5.68 - 125.00 When I try to graph my Start Dates vs. Techs./Month, my dates starts (and ends) at Dec, 30 1899, why? I did this once before with no problems. I made some .xls improvements as far as calculations but the actual data hasn't changed. I even started from scratch. Any one. Any one. -- I reject your reality and substitute my own. Promote hydrogen - one of the best "clean" fuels there are! |
#2
|
|||
|
|||
It worked before... .xls in Access
I had no noticeable trouble importing your data, but the Chart Wizard
tried to aggregate the numbers by year. I changed the Row Source for the chart to the following Query: SQL: SELECT (Format([Start Date],"mmm"" '""yy")) AS Expr1, Int([SumOfTechs/Month]*100+0.5)/100 AS TpM, Sum(ExcelData.[Techs/Month]) AS [SumOfTechs/Month] FROM ExcelData GROUP BY (Format([Start Date],"mmm"" '""yy")), ExcelData.[Start Date] ORDER BY ExcelData.[Start Date]; I put the name of this Query into the Chart's Row Source property (or maybe Access put it there automatically). This Query produces the following output: Query Datasheet View: Expr1 TpM SumOfTechs/Month -------- ---- ---------------- Jan '05 5.95 5.94999980926514 Feb '05 3.75 3.75 Mar '05 2.45 2.45000004768372 Apr '05 1.79 1.78999996185303 May '05 6.82 6.82000017166138 Jun '05 2.84 2.83999991416931 Jul '05 4.55 4.55000019073486 Aug '05 1.49 1.49000000953674 Sep '05 2.56 2.55999994277954 Oct '05 1.37 1.37000000476837 Nov '05 4.72 4.71999979019165 Dec '05 5.68 5.67999982833862 Since it produces 3 fields and I wanted to plot only 2, I changed the Chart's Column Count property to 2, after which the Chart displayed only one data series, as I assume you wished. -- Vincent Johns Please feel free to quote anything I say here. jsc3489 wrote: I'm trying to create a pivot chart table using data from an Excel sheet. This is my data: Sorry about the wrap. Total Hours Start Date Techs. Required End Date Projected End Date Work Days to Complete Actual Days Techs./Month Techs. Required 8 Hour Days 1000 1/3/2005 1 1/31/2005 1/31/2005 21.00 28 5.95 5.95 125.00 600 2/3/2005 4 3/1/2005 18.75 26 3.75 - 75.00 450 3/1/2005 3 3/25/2005 18.75 24 2.45 - 56.25 300 4/1/2005 2 4/27/2005 18.75 26 1.79 - 37.50 1200 5/2/2005 5 5/31/2005 5/31/2005 22.00 29 6.82 6.82 150.00 500 6/1/2005 3 6/29/2005 20.83 28 2.84 - 62.50 765 7/1/2005 5 7/28/2005 19.13 27 4.55 - 95.63 275 8/1/2005 2 8/24/2005 17.19 23 1.49 - 34.38 450 9/1/2005 3 9/27/2005 18.75 26 2.56 - 56.25 230 10/3/2005 2 10/21/2005 14.38 18 1.37 - 28.75 830 11/1/2005 5 11/29/2005 20.75 28 4.72 - 103.75 1000 12/1/2005 6 12/29/2005 20.83 28 5.68 - 125.00 When I try to graph my Start Dates vs. Techs./Month, my dates starts (and ends) at Dec, 30 1899, why? I did this once before with no problems. I made some .xls improvements as far as calculations but the actual data hasn't changed. I even started from scratch. Any one. Any one. |
#3
|
|||
|
|||
It worked before... .xls in Access
The weird thing is that I can create any chart I want in excel, but not in
Access. -- I reject your reality and substitute my own. Promote hydrogen - one of the best "clean" fuels there are! "Vincent Johns" wrote: I had no noticeable trouble importing your data, but the Chart Wizard tried to aggregate the numbers by year. I changed the Row Source for the chart to the following Query: SQL: SELECT (Format([Start Date],"mmm"" '""yy")) AS Expr1, Int([SumOfTechs/Month]*100+0.5)/100 AS TpM, Sum(ExcelData.[Techs/Month]) AS [SumOfTechs/Month] FROM ExcelData GROUP BY (Format([Start Date],"mmm"" '""yy")), ExcelData.[Start Date] ORDER BY ExcelData.[Start Date]; I put the name of this Query into the Chart's Row Source property (or maybe Access put it there automatically). This Query produces the following output: Query Datasheet View: Expr1 TpM SumOfTechs/Month -------- ---- ---------------- Jan '05 5.95 5.94999980926514 Feb '05 3.75 3.75 Mar '05 2.45 2.45000004768372 Apr '05 1.79 1.78999996185303 May '05 6.82 6.82000017166138 Jun '05 2.84 2.83999991416931 Jul '05 4.55 4.55000019073486 Aug '05 1.49 1.49000000953674 Sep '05 2.56 2.55999994277954 Oct '05 1.37 1.37000000476837 Nov '05 4.72 4.71999979019165 Dec '05 5.68 5.67999982833862 Since it produces 3 fields and I wanted to plot only 2, I changed the Chart's Column Count property to 2, after which the Chart displayed only one data series, as I assume you wished. -- Vincent Johns Please feel free to quote anything I say here. jsc3489 wrote: I'm trying to create a pivot chart table using data from an Excel sheet. This is my data: Sorry about the wrap. Total Hours Start Date Techs. Required End Date Projected End Date Work Days to Complete Actual Days Techs./Month Techs. Required 8 Hour Days 1000 1/3/2005 1 1/31/2005 1/31/2005 21.00 28 5.95 5.95 125.00 600 2/3/2005 4 3/1/2005 18.75 26 3.75 - 75.00 450 3/1/2005 3 3/25/2005 18.75 24 2.45 - 56.25 300 4/1/2005 2 4/27/2005 18.75 26 1.79 - 37.50 1200 5/2/2005 5 5/31/2005 5/31/2005 22.00 29 6.82 6.82 150.00 500 6/1/2005 3 6/29/2005 20.83 28 2.84 - 62.50 765 7/1/2005 5 7/28/2005 19.13 27 4.55 - 95.63 275 8/1/2005 2 8/24/2005 17.19 23 1.49 - 34.38 450 9/1/2005 3 9/27/2005 18.75 26 2.56 - 56.25 230 10/3/2005 2 10/21/2005 14.38 18 1.37 - 28.75 830 11/1/2005 5 11/29/2005 20.75 28 4.72 - 103.75 1000 12/1/2005 6 12/29/2005 20.83 28 5.68 - 125.00 When I try to graph my Start Dates vs. Techs./Month, my dates starts (and ends) at Dec, 30 1899, why? I did this once before with no problems. I made some .xls improvements as far as calculations but the actual data hasn't changed. I even started from scratch. Any one. Any one. |
#4
|
|||
|
|||
It worked before... .xls in Access
jsc3489 wrote:
The weird thing is that I can create any chart I want in excel, but not in Access. As I said, my version of your chart looked OK in Access, but maybe I was using a different style of chart from the one you used. However, if Excel offers you better chart styles than Access does, why don't you export the data into Excel and plot them there? -- Vincent Johns Please feel free to quote anything I say here. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
The "Right" web hosting for data access pages?? | Ron Ehrlich | General Discussion | 9 | May 6th, 2005 05:49 AM |
Encrypt AccesS File? | milest | General Discussion | 2 | February 9th, 2005 07:58 PM |
Hiding Access | Mark A. Sam | Using Forms | 4 | December 1st, 2004 09:09 PM |
MICROSOFT INVESTING HEAVILY IN ACCESS | Mike Painter | General Discussion | 39 | October 15th, 2004 03:56 PM |
You do not have exclusive access... ERROR | Robin | General Discussion | 1 | July 6th, 2004 01:18 AM |