It worked before... .xls in Access

[Guest]

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.

 

[Vincent Johns]

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 <[email protected]>
Please feel free to quote anything I say here.

 

[Guest]

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!

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 <[email protected]>
Please feel free to quote anything I say here.

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.

 

[Vincent Johns]

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 <[email protected]>
Please feel free to quote anything I say here.