Avatar of Tom Crowfoot
Tom Crowfoot
Flag 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)
Microsoft Access

Avatar of undefined
Last Comment
Tom Crowfoot

8/22/2022 - Mon
Gustav Brock

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.
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

Fabrice Lambert

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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Tom Crowfoot

thanks, will get that 'down on paper' & uploaded

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Tom Crowfoot

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.
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
David Bernstein

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

John Tsioumpris

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 :http://www.rogersaccesslibrary.com/forum/flex-grid-demo_topic511.html
The sample database has a sample project management with the infamous MsFlexGrid Control

Tom Crowfoot

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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy