Excel 2000 MSDE 2000
Budjetti $20-100 USD
Job Description:
Objective: Create Excel 2000 macros to import/export workbook data to/from MSDE database.
Requirements:
1. MSDE 2000 database
[url removed, login to view] Sql script to create new database, tables, relationships
1b. Required Table ProjIssue (Field/Type/Size/Key)
Project/Var/255/FK
IssueNumberUnique/long//PK
IssueNumber/long/
IssueGroup/Var/50
IssueWork/Var/50
IssueRaisedByID/long//FK
IssueOpened/Date
IssueOwnerID/long//FK
IssueDescription/Var/max
IssueImpact/Var/max
IssuePriority/char/1
IssueRank/long
IssueDue/Date
IssueClosed/Date
IssueStatus/Var/max
IssueweightOwner/long
IssueweightWork/long
IssueWeightGroup/long
IssueweightIssue/long
IssueEscalate/long
IssueUpdated/Date
1c. Required Table ProjGap (Field/Type/Size/Key)
Project/Var/255/FK
GapNumberUnique/long//PK
GapNumber/long/
CTQNumber/long//Fk
GapShortName/Var/50
GapGroup/Var/50
GapWork/Var/50
GapEnvironmentA/var/50
GapEnvironmentB/Var/50
GapProcessDescA/Var/max
GapProcessDescB/Var/max
GapInterimState/Var/max
GapFutureState/Var/max
GapPriority/Char/1
GapRank/long
GapRaisedByID/long//FK
GapOwnerID/FK
GapWeightOwner/long
GapWeightRequirement/long
GapWeightWork/long
GapweightGroup/long
GapOpened/Date
GapDue/Date
GapClosed/Date
GapUpdated/Date
1d. Required Table ProjActionItem (Field/Type/Size/Key)
Project/Var/255/FK
AINumberUnique/long/PK
AINumber/long
AIGroup/var/50
AIWork/Var/50
AIRaised/Var/50
AIOpened/Date
AIOwner/Var/50
AIDescription/Var/max
AIImpact/var/max
AIPriority/char/1
AIRank/long
AIDue/Date
AIClosed/Date
AIStatus/var/max
AIweightOwner/long
AIweightwork/long
AIweightGroup/long
AIweightAI/long
AIIsTask/long
AIUpdated/Date
1e. Required Table ProjCTQ (Field/Type/Size/Key)
Project/Var/255/FK
CTQNumberUnique/long//PK
CTQNumber/long
CTQShortName/Var/50
CTQDescription/Var/max
CTQOpened/Date
CTQValidated/Date
CTQDeprecated/Date
CTQSponsorID/long//FK
CTQWeightSponsor/long
CTQWeightCTQ/long
CTQUpdated/Date
1f. Required Table ProjAssumption (Field/Type/Size/Key)
Project/Var/255/FK
AssumptionNumberUnique/long//PK
AssumptionNumber/long
AssumptionGroup/var/50
AssumptionWork/Var/50
AssumptionRaisedByID/long
AssumptionValidatedByID/long
AssumptionDeprecatedBy/long
AssumptionOpened/Date
AssumptionValidated/Date
Assumption/Deprecated/Date
AssumptionweightValidator/long
AssumptionWeightGroup/long
AssumptionWeightWork/long
AssumptionWeightAssumption/long
AssumptionUpdated/Date
1g. Required Table ProjResource (Field/Type/Size/Key)
Project/Var/255//FK
ResourceUniqueID/long//PK
ResourceName/Var/50
1h. Required Table Project (Field/Type/Size/Key)
Project/Var/255//PK
ProjectName/Var/50
1i. Enhance DB Design/Schema to easily support the following reporting relationships
Project > ProjIssue
Project > ProjGap
Project > ProjCTQ
Project > ProjAssumption
Project > ProjActionItem
Project > ProjResource
ProjIssue <> ProjGap
ProjIssue <> ProjCTQ
ProjIssue <> ProjAssumption
ProjIssue <> ProjActionItem
ProjIssue <> ProjResource
ProjGap <> ProjCTQ
ProjGap <> ProjAssumption
ProjGap <> ProjActionItem
ProjGap <> ProjResource
ProjAssumption <> ProjActionItem
ProjAssumption <> ProjResource
ProjResource <> ProjActionItem
1j. Provide parameterized script option to add tables to an existing Database or (default: create new database)
2. Provide script to automatically Create MS Excel 2000/2003 compatible workbooks from database
2a. One workbook per project
2b. Separate worksheets for Issues, Gaps, Assumptions, ActionItems, Resources, CTQs
2c. First row of each workbook is column headings from database tables
2d. Resources sheet is populated from Database on workbook creation.
2e. Drop down list boxes on Issues, Gaps, Assumption, Actionitems, CTQs sheets are implemented for Owner, and RaisedBy columns.
2f. Provide ability to enter a new Resource (not in database, or list box) from the spreadsheet and have it populate the database, and refresh the Resource Sheet.
2g. Automatically enable autofilter on all sheets
3. Create Excel macros inside MS Excel worksheets to import from/export to the database.
3a. Use generic re-usaable parameterized subroutines and functions,
3b. Import option to overwrite or update changes only from worksheet to database.
3c. Import/update all worksheets within a workbook at one time to database
3d. Export option to overwrite or update changes only from database to worksheet
3e. Export/update all worksheets within a workbook at one time.
4. Produce .Net web site control panel to administer utility scripts.
4a. Create new, empty database
4b. Import to existing database
4c. Select Project to export from Datbase to workbook.
4d. Select directory/file name to create new Excel Workbook(s)
4e. Select file or directory for import/export
5. Critical To Quality:
5a. Synchronise multiple copies of project workbooks to database without error
5b. bi-directional update from database to workbook and from workbook to database
5c. Time to delivery
5d. Performance
5e. Compatiblity with MS Excel 2000.
5f. Compatibility with Win2k and Win XP
5g. Flexible DB design for future enhancements.
5h. Parameterized scripts
5i. Table driven parameters.
5j. Simple, Easy to use interface.
5k. Must run on desktop as standalone (with command line or graphical utility) if no webserver is available.
6. Provide detailed deployment instructions for web deployment and desktop deployment ( win2k/win XP)
6a. Provide suggestions for future improvements.
6b. Provide "lessons learned"
