G
Guest
I'm looking for the best approach to layout the foundation of a database that
will be used to track business projects. Currently, management is utilizing
various Excel spreadsheets to store, edit/update, and report out information
related to the projects.
I was provided the "core" data Excel file containing information for
approximately 195 projects. The Excel file is storing information on
projects as far back as 2005, and is storing data on projects that have been
completed, are in a planning stage, are actively in process, etc. Updates
are made to the Excel file on a monthly basis to track changes to the
projects. Going forward, this Excel file is proving to be too cumbersome to
maintain as more and more projects are being added, updated and completed.
Each project has the following columns associated with it on the Excel
speadsheet:
- a unique reference project ID (numeric)
- project name (text-only)
- project description (text-only)
- project type (one of 5 possible options)
- category (one of 5 possible options)
- project start date (formatted as yyQq, where yy = last two digits of year,
and q = 1, 2, 3, or 4 for the Quarter of the year - Q1 is Jan, Feb, Mar; Q2
is Apr, May, Jun; Q3 is Jul, Aug, Sep; Q4 is Oct, Nov, Dec)
- project end date (same format as project start date)
- amount of capital dollars (numeric/currency, but also null and text entries)
- target annual savings (numeric/currency, but also null and text entries)
- budgeted savings for 2005 (numeric/currency, but also null and text entries)
- budgeted savings for 2006 (numeric/currency, but also null and text entries)
- budgeted savings for 2007 (numeric/currency, but also null and text entries)
- revised savings for 2007 (numeric/currency, but also null and text entries)
- basis of savings (text-only)
- measurement (text-only)
- actual savings for current period (numeric/currency, but also null and
text entries)
- actual savings year-to-date (numeric/currency, but also null and text
entries)
- actual savings project-to-date (numeric/currency, but also null and text
entries)
- project status (text-only)
- product line (one of 15 possible options)
- financial area (one of 5 possible options)
- project sponsor (text-only)
- project leader (text-only)
- project location (one of 6 possible options)
I exported the spreadsheet from Excel and had to edit/manipulate some of the
cells to get a clean import into Access. This gave me one table with
approximately 195 records of project information, which I know is not
practical.
At the moment, management is looking to generate four different reports from
this table:
- an overview report of all the project information, grouped by project
type, with subtotals by product line and totals by financial area
- a project savings report for 2007 showing budgeted and revised savings
totals by project type for each financial area
- an annual savings report for each year showing number of projects and cost
savings by project category for each project type
- a "change" report driven by date range to show which projects changed and
what information changed within each project
The first report is easy enough to create (simply a cleanly formatted "dump"
of the data in the table). The second and third reports would require two to
three separate crosstab queries each to generate, and then a way to pull the
queried data together in one report. The fourth report will require a way to
save field data before and after a change is made by date/time stamps so
date-range reporting can be generated.
I'm looking for advice on how to normalize this one table so my database is
flexible enough to generate the above reports, allow for storing of completed
projects, edits and updates by management users, and the ability to export
down to Excel and import back into Access.
I apologize in advance for what seems to be an enormous request from the
community here, but any helpful information is welcomed. Thank you very much!
will be used to track business projects. Currently, management is utilizing
various Excel spreadsheets to store, edit/update, and report out information
related to the projects.
I was provided the "core" data Excel file containing information for
approximately 195 projects. The Excel file is storing information on
projects as far back as 2005, and is storing data on projects that have been
completed, are in a planning stage, are actively in process, etc. Updates
are made to the Excel file on a monthly basis to track changes to the
projects. Going forward, this Excel file is proving to be too cumbersome to
maintain as more and more projects are being added, updated and completed.
Each project has the following columns associated with it on the Excel
speadsheet:
- a unique reference project ID (numeric)
- project name (text-only)
- project description (text-only)
- project type (one of 5 possible options)
- category (one of 5 possible options)
- project start date (formatted as yyQq, where yy = last two digits of year,
and q = 1, 2, 3, or 4 for the Quarter of the year - Q1 is Jan, Feb, Mar; Q2
is Apr, May, Jun; Q3 is Jul, Aug, Sep; Q4 is Oct, Nov, Dec)
- project end date (same format as project start date)
- amount of capital dollars (numeric/currency, but also null and text entries)
- target annual savings (numeric/currency, but also null and text entries)
- budgeted savings for 2005 (numeric/currency, but also null and text entries)
- budgeted savings for 2006 (numeric/currency, but also null and text entries)
- budgeted savings for 2007 (numeric/currency, but also null and text entries)
- revised savings for 2007 (numeric/currency, but also null and text entries)
- basis of savings (text-only)
- measurement (text-only)
- actual savings for current period (numeric/currency, but also null and
text entries)
- actual savings year-to-date (numeric/currency, but also null and text
entries)
- actual savings project-to-date (numeric/currency, but also null and text
entries)
- project status (text-only)
- product line (one of 15 possible options)
- financial area (one of 5 possible options)
- project sponsor (text-only)
- project leader (text-only)
- project location (one of 6 possible options)
I exported the spreadsheet from Excel and had to edit/manipulate some of the
cells to get a clean import into Access. This gave me one table with
approximately 195 records of project information, which I know is not
practical.
At the moment, management is looking to generate four different reports from
this table:
- an overview report of all the project information, grouped by project
type, with subtotals by product line and totals by financial area
- a project savings report for 2007 showing budgeted and revised savings
totals by project type for each financial area
- an annual savings report for each year showing number of projects and cost
savings by project category for each project type
- a "change" report driven by date range to show which projects changed and
what information changed within each project
The first report is easy enough to create (simply a cleanly formatted "dump"
of the data in the table). The second and third reports would require two to
three separate crosstab queries each to generate, and then a way to pull the
queried data together in one report. The fourth report will require a way to
save field data before and after a change is made by date/time stamps so
date-range reporting can be generated.
I'm looking for advice on how to normalize this one table so my database is
flexible enough to generate the above reports, allow for storing of completed
projects, edits and updates by management users, and the ability to export
down to Excel and import back into Access.
I apologize in advance for what seems to be an enormous request from the
community here, but any helpful information is welcomed. Thank you very much!