Table Structure & Relationships vs Reporting - Confused Help! (1 Viewer)

[christakis]

Hi Guys,

I am trying to develop a database for my company to track the process / details of each project vs resources.

Each project has 12 engineering roles. Engineers can do one or more role in each project. To make things more complicated engineers can work under different companies.

I want to be able to generate two reports (If I figure these out I'll figure out the rest).
1st report: A list of all projects, their status and the engineers that work for each project.
2nd report: A list of all engineers (preferably sorted by company) stating which projects they are/will be working on and what role(s) they do for each project.

So far, I created two tables. Projects and Contacts, and a couple of forms but can't get nowhere near to managing to get the reports I want. I suspect the root problem is the table structures & relationships.

Also, do you believe it would be better to do this in Excel?

Let me know your thoughts.

Thanks in advance for any help,
Christakis

 

[jzwp22]

I would stick with Access for your application rather than Excel.

If multiple people can work on a project, that describes a one-to-many relationship which is handled with a separate but related table.

I would also recommend having 1 table for all people:

tblPeople
-pkPeopleID primary key, autonumber
-txtFName
-txtLName
other fields related to the person

A table to hold the project info
tblProjects
-pkProjectID primary key, autonumber
-txtProjNo
-txtProjectName

tblRoles (a table to hold all of the roles that a person may play)
-pkRoleID primary key, autonumber
-txtRoleName

Now relate the people to a project

tblProjectPeople
-pkProjPeopleID primary key, autonumber
-fkProjectID foreign key to tblProjects
-fkPeopleID foreign key to tblPeople

Since a person can have many roles related to a project, that is another one-to-many relationship

tblProjectPeopleRoles
-pkProjPeopleRoleID primary key, autonumber
-fkProjPeopleID foreign key to tblProjectPeople
-fkRoleID foreign key to tblRoles

To make things more complicated engineers can work under different companies.

I was a little unclear about the above statement. Are you saying that some of the engineers do not work for your company? Or are you saying that an engineer of your company can work on projects for other companies (you subcontract engineering services)? If you simply mean that an engineer can work on multiple projects (and each project can be tied to different companies), then the structure I presented above should work, but if you could explain further we'll have a better idea.

 

[christakis]

Hi,

Thank you for your reply. The company is part of an alliance consisted of several different companies. Due to the nature of the projects, engineers from different companies work under the same project. This is not sub-contracting as such but rather "providing support" when resources are low. All projects are for one customer. The alliance was set up to ensure that resources are always available for the projects to be completed. I know it's a weird set up...

Having said that, each engineer can have one extra field: txtCompany.

Your table structure makes a lot of sense. What confuses me with this structure is: form design. At least for adding new records, ideally, you would want to have two forms. One for adding Engineers and their roles and one for Projects. How do I combine 5 tables in 2 forms?

Anyway, I'll start playing with it right now and let you know how it goes. If you happen to stumble on a good tutorial in form design with multiple tables can you please post me the link?

Thank you very much for all the help so far.

Christakis

---Update---
I've played around with the tables you suggested. I also added a new table:
tblCompanies with:
pkCompany
txtCompany

and added a fkCompany in table tblPeople.

I played around with reports and got exactly what I wanted by using Report Wizard. So... the table structure is very good.

My problem now is to create a user friendly form to add/edit project details. I've never worked with creating multiple records in different tables using one form before. Are there any good tutorials on this?

Cheers,
Christakis

PS: I attached the new structured DB if you want to have peek.

 

[jzwp22]

You will need a form with subforms and possibly a subsubform (in other words: a subform within a subform). For example, your main form would be base on your project table and then within that form you would have a subform based on tblProjectPeople (with a combo box to supply the engineer) and with that subform a form based on the tblProjectPeopleRoles. You would use a combo box to supply the role(s) for the person. If the engineer is a not in the database already, you could use some code in the not in list event of the combo box used to supply the engineer to open a form to add the new engineer.

I noticed that you have lookups (list/combo boxes) at the table level. Although Access has this capability, it is generally not recommended. Check out this site for more details.

 

[christakis]

You will need a form with subforms and possibly a subsubform (in other words: a subform within a subform). For example, your main form would be base on your project table and then within that form you would have a subform based on tblProjectPeople (with a combo box to supply the engineer) and with that subform a form based on the tblProjectPeopleRoles. You would use a combo box to supply the role(s) for the person. If the engineer is a not in the database already, you could use some code in the not in list event of the combo box used to supply the engineer to open a form to add the new engineer.

I noticed that you have lookups (list/combo boxes) at the table level. Although Access has this capability, it is generally not recommended. Check out this site for more details.

Hi, Thank you for your response. I will look into how to eliminate lookup fields. I believe it's about time I brush up my form design skills as the problems are no longer table structure. Thanks for all the help. Really appreciate it.

Christakis

 

[jzwp22]

You're welcome. Please post back with any additional questions.