Help required building an Estimating Database

[SSlessor]

Hi All,

I'm new to access and I have mastered the basics of creating tables and forms. I am trying to put together a database which i can use to estimate construction activities.

At the moment I have two tables; Activity Schedule and Resources. I would like to add many different resources to one activity from the activity schedule. I think i need another table but i am not sure how to set up the relationships effectively. I guess i am probably not making much sense, please see attached screen shot for what i am trying to achieve.

Eventually i would like a form where it has the activity listed in the top half and the resources listed in the bottom half and some buttons where you would click on them to add the resource to the activity. This would then calculate the cost of the all the resources for each activity.

I would appreciate any help or pointers you could give me. Thanks for looking.

 

[CBrighton]

I would likely have an extra table.

I would have one table for jobs (or activities), a second detailing all available resources (looks like this is the other one you already have) and a third to for assigned resources.

Basically the resources table would be stand alone and likely be used to feed into into list boxes / combo boxes. The activities table would contain the same as it currently does and the assigned resources one would hold the resources which have been selected for each activity.

The activities and assigned resources tables would then be linked as one to many - one activity with many assigned resources.

This would work well with your planned form design as a simple subform in the bottom half of the form would allow you to display the resources assigned to the activity you are viewing.

 

[SSlessor]

Thanks for the advice CBrighton. I'm slowly getting there! Still getting used to the one to many function!

 

[CBrighton]

No problem.

The key to relational database design is creating a table for each "thing". Some examples of a thing being a customer or an order, etc.

This allows you to easily store multiple sets of data in one table but only one set of data in the main table. It allows you to link multiple addresses to a single customer, multiple items to a single order, and so on.

Mastering the relational database design is vital for more complex databases or even more complex features in simple databases.