Link to home
Start Free TrialLog in
Avatar of Tom Crowfoot
Tom CrowfootFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Whats the best way to design a multiple project tracker Access Database with multiple resources

Hi Experts

I am creating a project tracking database which will track multiple projects (from when we get wind of them through go live to completion).  Each project will have multiple resources (people) working on it and each week I need to be able to assign the number of days each person is to work on each project (the excel attachment shows and excel example of how this would need to be updated Allocation.xlsx). I also need to track the overall days spent by anyone on an individual project.

Can anyone provide any advice or an example of how I need to tie up the tables (presumably 1 main projects table and either one or more resources table & potentially a tabel to track the weekly allocation of resources).

Also how & where I tie in the dates (ie week commencing 26 April 2021) - ideally this need to be perpetual (i.e. automatically add in new dates up to 12 weeks out)
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

You cannot expect us to design your application from the ground up - that's a major piece of work, indeed as there is no single optimum method for building this kind of applications that must meet a lot of individual demands and preferences.

It sounds like your best option would be to team up with an experienced developer knowing what to do when you present the plan and all the details for your project.
Avatar of Tom Crowfoot


Hi Gustav,

thank you for that, rest assured I wasn't expecting that at all, just some help / advice on how the structure the tables to enable me to stand a fighting chance of achieving a tie up similar to the excel spreadsheet. ie because  you can have multiple people on a project at different times you're going to need to have separate 'diaries' for each person which you bring together in a union query so you can update them OR you're going to need have a separate tie up table to tie up a unique ID from a project master to the people to the dates etc

I'll try to reword the question to reflect this, apologies if the initial wording came across as 'build this for me'

many thanks

The first thing to do, is to express your need in detail:
Identify each entities, data, how they are related together ect ...
Only then we can work on a conceptual data model.
thanks, will get that 'down on paper' & uploaded
Avatar of Eirman
Flag of Ireland image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi Eirman

thanks for the advice, I was 1/2 thinking that and have been doing a bit of digging around, but haven't settled on anything yet

Many thanks

Hey Tom, Hope you don't mind ...

I added
Project Management & Project Management Software as question topics.

You can remove them if you think it was inappropriate.
Based on your sample spreadsheet, this appears to be a simple project
Most experienced developers have completed tasks like this
The data has to be "normalized" into a table that looks like this
Person#, Assignment#, Project Type,Date, Hours
A "crosstab query" can then be generated that could produce output that looks like your spreadsheet
The Access approach would give you an open ended solution: unlimited people, projects, etc
The necessary data entry forms would have to be developed

Well sometimes my memory does wonders..... :)
If you are really up to chase the project management here is a sample application with a lot of bells and whistles
From the Roger's Access Libray :
The sample database has a sample project management with the infamous MsFlexGrid Control
User generated imageUser generated image
I took Eirman's advice and went for a non access solution in the end - I went for a fairly complex excel sheet but had to drop the certain elements. thank you for all your help & advice though