Export to existing excel file

[Kerensky18]

Hello:

I posted a simular question earlier under reports but I think I have a
better undertanding of how I need to accomplish this.

What I need is a formated excel file to do the stoplight or trafficlight
type of chart in.

Here's how I think I need to do it:

User selects Senior Manager, Manager, Manager2, etc, and date range.

Based on the above and the date range access will pull the records that
match the above for the date range selected and export them as follows. Dates
are every monday.


Date1, Date2, Date 3, Date4, etc all dates in range
Fhours data, data, data, data,
Ahours data, data, data, data

The above chart is formated using conditonal formating in excel.


I've done everything but get the data out to excel, I would like to have an
existing chart and append the data and the dates to the existing chart. If
need be I can use a new chart if it can do the coditional formating without
user input.

Any suggestions?

 

[Gina Whipp]

Kerensky18,

I am trying to understand exactly what it is you are trying to accomplish
and am a bit confused as to whether you want to do this in Excel and Access
or just Access. I am a little concerned that if you set up your tables to
have columns like Date1, Date2, etc... that you missed the points of
normalization and relational database design. It might help if you post you
table set-up, if in fact you are using Access.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Kerensky18]

I'm sorry for the confusion..

My tables are not set up that way, I was showing an example of the output
that I'm looking for.

I the following table layout in Access:

Forecast:
FDate - Text- Lookup based on set value list of every thursday from the
beginning of the year untill the end of 2011.

ForecastHours - Number
ActualHours - Number
CommodityID - Number -Related to Commodity Table
ProgramID - Number - Related to Program Table
BudgetID - Number - Related to Budget Table
SkillID - Number- Related to Skill Table

What I'm trying to do is set up a database that will allow users to forecast
how many hours they will need to complete a project, and than for another
user to come in and place the actual hours in for the project.

And finaly the Forecasted and Actual hours need to be output to a chart with
Dates set-up as colums and hours set up as rows. The Actual hours need to
change from Green to Yellow to Red depending on how close they are to the
forecast.

Does this help to clarify it at all?

If need be I can be reached on msn at (e-mail address removed) if you think you
can give me some hints to clarify it more.

Thanks:
Kerensky18

 

[Kerensky18]

One more thing, I've come close with pivottables in Access 2007 but since I'm
using lookups it displays 1 for program instead of the actual program name or
any other lookup field, there also doesn't seem to be any way of doing
conditional formating.

 

[Gina Whipp]

Kerensky18,

One of the issues with using look-up fields in tables. I would suggest you
create tables for those fieldfs and then link to those tables. Conditional
formatting works on forms, I have tried on a Pivot tables but my guess would
be not available.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Kerensky18]

They are linked through 1-many relations to the forecast table.

I still need to figure out how to get a report, form or "insert other method
here," to work like a stop-light chart. It worked find in excel but it got to
hard to sort through all the data.

The other solution I found is to imbed an activex Microsoft Office
Sreadsheet 11 into an access report but I don't know how to populate it with
data.

Just to reitereate I'm looking for an output simular to the following that a
user can generate based of the data in the database by pressing one button
after selecting a date range, program, etc.

Colums = Dates
Rows = Hours and Actual Hours




Thanks for the help:
Kerensky18

 

[Kerensky18]

I found this link discussing what I'm trying to do:

http://www.databaselessons.com/access-data-to-excel-1.php


Thanks for the help:
Kerensky18

 

[Gina Whipp]

Kerensky18,

Yes that does help clarify... I would image that there will be more then
one person per Project, so where is that table? The one that allows many
users per one project? Come to think of it you seem to be missing a few
tables. Is this the only table you have?

Also, posting your real eMail is not a good idea in the newsgroups as it
will be harveted by the spammers! Also note, corresponding via private
eMail does not benefit those in the newsgroup.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Gina Whipp]

Kerensky18,

Okay, glad you found the article. Just concerned you are trying to use
Access like a glorified version of Excel and may not get the results you
want. However, if you found something that works then go with it.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Kerensky18]

Thanks for the suggestions.

Here is the layout of the database, and yes there will be more than one
user.. I've just been stuck on this "report" so that they can use access as a
analysis tool to see the forecasted hours and actual hours analyzed. They can
go as wide as the Senior Manager veiwing everything under him/her or as low
as commodity. I'm learning more each time I come back and it looks like I
will need a form with a button that uses an OnClick event to use ADO.


Tables:
SeniorManager
SenManID - AutoNum
SeniorManagerName - Text
Manager
ManagerID - Autonum
ManagerName - Text
SenManID - Num
Commodity
CommodityID - AutoNum
CommodityType - Text
BDCManagerID - Num
ManagerID - Num
OrgID - Num
Org
OrgID - Autonum
OrgType - Text
BDCManager
BdcID - Autonum
BdcMan - Name
DateRange
DateRangeID- AutoNum
DateRange - Date/Time, Small Date
Forecast
ForecastID - AutoNum
ForecastDate - Text
ForecastHours - Num
ActualHours - Num
CommodityID - Num
ProgramID - Num
BudgetID - Num
SkillID - Num
Program
ProgramID - AutoNum
ProgramType - Text
Budget
BudgetID- AutoNum
Budget - Text
Skill
SkillID - AutoNum
SkillType - Text

Relations:

SeniorManager -1-M- Manager
Manager -1-M- Commodity
ORG -1-M- Commodity
BDCManager -1-M- Commodity
DateRange -1-m- Forecast (date range holds every Monday date)
Program, Skill, Budget, Commodity -1-M- Forecast

 

[Gina Whipp]

Kerensky18,

A few table change suggestions and a question...

Tables:
tblManager
mManagerID (PK-Autonumber)
mFirstName - Text
mLastName - Text
mManagerTypeID (FK)

tblManagerType
mtManagerTypeID (PK-Autonumber)
mtName (Senior, BDC, etc...)

tblCommodity NOTE - Why 2 fields for Manager?
cCommodityID - AutoNum
cCommodityType - Text
cBDCManagerID - Num
cManagerID (FK-Long Integer)
cOrgID (FK - Long Integer)

Org
OrgID - Autonum
OrgType - Text

DateRange QUESTION - How is this used? Holding every Monday date, not sure
I understand...
DateRangeID- AutoNum
DateRange - Date/Time, Small Date

Forecast
ForecastID - AutoNum
ForecastDate - Text
ForecastHours - Num
ActualHours - Num
CommodityID - Num
ProgramID - Num
BudgetID - Num
SkillID - Num

Program
ProgramID - AutoNum
ProgramType - Text

Budget
BudgetID- AutoNum
Budget - Text

Skill
SkillID - AutoNum
SkillType - Text

I think you are trying to design your report and then make your tables fit.
That would be backwards. Design your tables and the report will fit. As
for designing a form that would be the BEST way to go as they are the
*windows* to the data and allow many options for display and manipulation.
Reports are only for viewing the data no manipulation allowed.

I am also unclear as to why you think you need ADO? You certainly do not
NEED that to *talk* to Excel.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Kerensky18]

Gina:

Thank you for helping me on this.

"DateRange QUESTION - How is this used? Holding every Monday date, not sure
I understand..."

The DateRange table holds the only valid dates that I allow, which is every
Monday. It was the only way that I could think of that would force them to
choose a Monday.
Example:
8/3/09
8/10/09
8/17/09
8/24/09
8/31/09

Commodity has both a Manager and a seperate BDC Manager.

The only problem that I've seen with Access reports is that everything is
via column instead of rows (which is what I need for the one report that this
need to generate):

Date: ActualHours: ForeCast: Date: Data
Data Data
Data Data data VS Rows: Hours: Data,
Data, Data

Kerensky18

 

[Gina Whipp]

Kerensky18,

No problem, SyFy is Bug Night and I really hate bugs!

Okay then I guess that stays just doesn't feel right BUT then I've never
designed anything that only allowed on day of the week to be entered... So
leaving that as it is.

As for the way Access holds the data rows vc. columns, have a look at
Crosstab Queries.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Kerensky18]

Warehouse 13 is good though

I'll look at cross-tabs more later, I delved a little into them today. For
now though I'll review your suggestions and continue tommorow. Thanks for the
help.

Kerensky18

 

[Gina Whipp]

Yes it is!

Okie dokie...

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm