Considering going from Excel to Access. Need some help with the overall design

[jaredmason]

Hi all!

I have a bunch of excel spreadsheets that overlap somewhat, and I am thinking about going to Access. The overlapping data entry is not a major issue for me, but I have one particular spreadsheet where I can't seem to get pivot tables to behave in unless I lay out my data in a really bizarre way, which is very challenging for entering/manipulating data.

Feel free to take a look at my current workbook and layout. The data tab is how I would like to have my data, but the list tab is how I have needed to put my data in order for pivot tables to recognize the dates as dates and not text. The Vertical tab is how I might want one of my reports to layout and the Horizontal tab is how my report needs to be laid out in order for the chart to show correctly. The Chart (Horizontal) tab is one of my main reporting goals.

The big problem is that if I keep my data in the original 'data tab' format, the pivot tables don't fully work for me. But if I was to change to the 'list tab' layout then I would end up with around 200,000 rows.

So do I have a good case to be looking at Access? Will I ultimately recoup the time setting up the database in time saved using it? And how should I design my database?

Any help very much appreciated. Thanks

[June7]

The List arrangement is how the data will have to be in Access table in order to produce the Vertical and Horizontal outputs. 200,000 rows should not be an issue. Access is limited by file size, not number of records.

Review 'sticky' thread tutorials at http://forums.aspfree.com/microsoft-access-help-18/

[jaredmason]

Thanks for your reply -I read the sticky's you pointed to and have saved that pdf to refer back to later. I still wouldn't say I really feel qualified to design a database though. My feeling is that the design is probably the most important part, so if someone can help me conceptualize the different tables I might require, then I am happy to plug away with online tutorials until I can implement that design. I have taken the odd course on databases and such, so the ideas aren't foreign to me, I just want to be sure I don't botch the planning stage.

I believe my entities/tables are TableProjects and TableResources. I could add in another table for Clients possibly, but I have no need to keep track of who the project is for or what their details are. What other tables might I need? Or do I need multiple tables at all? I'm having a spot of trouble deciding how complex this needs to be...

[jaredmason]

Side question: Will my forms be able to have some Excel-like functionality? Like copy, paste, cut etc. It'll be quite important that I will still be able to rearrange my numbers for various tasks and weeks very quickly

[June7]

You are correct in that getting the data structure correct first is vital. The 'sticky' thread on database principles should provide enough guidance to draft out a database design. You can present your effort for analysis.

No, forms will not have Excel-like functionality. A relational database is completely different from a spreadsheet.

[jaredmason]

All I can think of is that there should be one table for PROJECTS and one table for RESOURCES. I would say they have a many-to-many relationship as a project can have many different resources assigned to it and a resource can be assigned to many different projects. I am having a bit of trouble conceptualizing how the resources will be assigned - I should be theoretically able to assign an infinite amount of any resource to a project, but I am not sure how to record the quantity of assignment. Eg. how do I assign 3 web-developers to a project?

PROJECT
ProjectID*
Name
Confidence
Status
IsActive
StartDate
EndDate

PROVIDER
Name*

CLIENT
Name*

RESOURCE
ResourceID
Type???
Location???
WeekAssignedTo???
Qty???

...I'm really lost on the resource part. I should be able to assign a percentage of full-time equivalent workers (resources) to a project and the number of FTE's can fluctuate to my liking throughout the project. There are 11 different kinds of resource...

[June7]

Many-to-Many relationships require a third 'junction' table to associate the records.

PROJECT_RESOURCES
ProjectID (foreign key)
ResourceID (foreign key)

Assume each developer can have more than one project. Will there always be 3 developers or no more than 3? Two ways to handle. One is 3 fields in Project table for up to 3 developers. Not really a 'normalized' structure and has issues but can be made to work. Other is a junction table that associates project with developers.

PROJECT_DEVELOPERS
ProjectID (foreign key)
DeveloperID (foreign key)

[jaredmason]

Sorry I was a bit unclear. The developers *ARE* the resource. So there are 11 types of resources available to be applied to different projects - a variety of different kinds of designers, developers, architects, engineers and testers. We assign those resources to different projects on a week by week basis in percentages. So one project may have an architect working the first 4 weeks at 50% (ie part-time) and at the same time that project may have web developers assigned to weeks 3-8 at 200% (ie 2 people full time). We don't assign any particular person, that get's figured out as we go, but we need to be able to assign an endless number of resource types to any given project including fractions of a resource.

Hopefully that explains the challenge I am having with this design. I struggle within a database context to visualize how I should assign a fraction of a resource, or multiples of a resource, and also how I can assign the resource at varying amounts over time.

[June7]

Each resource assignment would be a record in the Project_Resources table.

ProjectID
ResourceID
DateStart
DateEnd
FTE allowance
etc.

[jaredmason]

That makes sense. But how do I reflect varying work rate? A resource can be at 200% from weeks 4-7, then 50% from weeks 8-9 for instance

[June7]

Guess that will be two separate records showing DateStart and DateEnd.

Percentage figures might not be best way to show the apportionment of staff resources. Look at FTE units http://en.wikipedia.org/wiki/Full-time_equivalent. But project costing is outside my experience.

[jaredmason]

Yup. FTE's is what I am using... it's just I have explained the situation to others using the term FTE and it turned into another stumbling block for them to get lost on. So I simplified my explanation... in any I am talking about a fraction of a team member's work weak whether you want to call it 0.5, 50% or whatever.

I'm not sure if this is correct. But is there another way of thinking about this where instead of having start and end dates the resource is assigned to each week? That way it can go up and down easily and also those values should be quite straightforward to change

[jaredmason]

For example, say I assigned a resource to from weeks 1-2 at 100% and 3-4 at 200%. What would happen in your database structure if I was to change my mind about weeks 1-2 and bring that down to 50%? Wouldn't that screw with the weeks 3-4, which I would like to stay the same?

Hope that makes sense

[June7]

I don't think change to one record would have to impact another. If you want to reduce the dedication of staff in weeks 1-2 and not up the effort in weeks 3-4, so be it.

[jaredmason]

How does the database handle these changes though? Here's probably a better example. Say I book in a resource at 100% for weeks 1-6. One record is created - all good. What happens if I decide to increase that resource to 200% in weeks 3-4? Would that create a second record stacked on the first? Or would it replace one record with multiple records? What if I then cancelled all work for week 4?

How would the database know how to split and merge records?

By the way, thanks for all your help. I'm not at all unappreciative - just inquisitive