Noobie Help!!

[kevlarsoul]

Hi everyone




I'm trying to build a database for something that "in my mind" should be crazy simple, but when I try, I just cant get it to do what I need it to do.

I'll explain..

I want a database that contains a list of projects and people involved, with a check box to identify completed projects.

I have done tables like this:-

Main table
Project_ref
Date
Info1
Info2
Info3
Info4
....etc

Then I have a 2nd, 3rd and 4th table titled tbl_secretary; tbl_Researcher; tbl_validator. These tables contain primarily information on people. First and last name, salaries, gender, level of experience. each of these tables contains slightly different information. Hence the reason for not just having one table.

The Main table has a project ref that is unique and can have obviously many secretaries, researchers and validators. These people can also work on other projects (potentially) so i guess duplicates should be allowed. (?)

I am trying to create a data input form that has all of this information on one screen, so that I can input the main information once and then add the people involved in the project.. for example 6 secretaries, 4 researchers, 8 validators etc..

So i can then go back and run a report that lists those involved within the project. Look up a person and see what projects they was involved with. Total salaries with the project. Count how many people were involved.

I have tried to add "Project_Ref" to each of the three other tables, to establish a relationship that way. But then I just get 3 tables with tones of the same reference number in. I tried to add a Primary ID key like Sec_ID, Res_ID and Val_ID all to the main table and link to the other tables that way but it doesn't work either.

I cant figure out how i can establish relationships with the 3 "staffing" tables to all link to the main table

I hope this makes sense to someone who can help. There may even me a template out there that fits the bill, but ive not been able to find anything.

Thanks in advance for any help

David

[andy49]

Does each project have one secretary, one researcher etc and can an individual be a researcher to more than one project?

[orange]

So in overview, you have people and projects. The people can perform one or more roles on any particular project(s). The first step with any database development is to understand the requirement. Best to get a clear description using the old who, what, where, when, why, how, how much and how often as a guide. This will help identify the "business processes and business facts". What are the things involved and how do they relate to one another?

There are many Projects
There are many Roles (secretaries, researchers and validators ... projectLeader/manager??)
There are many People/Persons
People work on Projects
A Person can have 1 or more Roles on a Project

Here is a link to info on database planning and design

I recommend you work through 1 or 2 of the tutorials from RogersAccessLibrary as mentioned in the link. It will clarify some of the confusion you are experiencing. You can work through a tutorial in 30-45 minutes and what you learn can be used with any database.

As for

Date
Info1
Info2
Info3
Info4

Date is a reserved word so should not be used as a field name.

Fields with numeric suffixes 1,2,3,4 tend to raise a red flag and usually indicate a non-normalized structure. See the pdf document in the link provided for more info.

First and last name, salaries, gender, level of experience.

Salaries and Level of experience typically have some time related attribute. So you may need to reference an "as-of some date concept". Experience (and skills) often involve more than 1 specific area (programmer, analyst, DBA, graphic designer, networking, welder, technical writer, trainer......)

Good luck with your project.

[kevlarsoul]

Hi Andy49

There will be many secretaries, researchers etc involved in one project, and these can work on more than one project, tho not necessarily concurrently.

So like one project to many sec, res, val

[kevlarsoul]

Hi Orange

Yeah those fields I gave were just examples for something to type, I would identify the date as eg Start_date, End_date etc. Other than dates, and salaries all other fields are short text. People can potentially do more than one role, but more likely will only do one role within their heading.

There are many Projects.. Yes
There are many Roles (secretaries, researchers and validators ... projectLeader/manager??) no just those three headings
There are many People/Persons Yes
People work on Projects Yes
A Person can have 1 or more Roles on a Project Possibly


I've structured my tables like

tbl_Sec

Sec_ID (auto)
Forename
Surname
Start_date
Salary
Gender (dropdown)
--------------

tbl_Researcher

Res_ID (auto)
Forname
Surname
LvlExp (dropdown)
Gender(dropdown)
-------------------

etc

Hope this helps, and thanks for the link, i'll have a look.

[ssanfu]

You should read up on Normalization....
See What Is Normalization, Part I: Why Normalization?
There are 5 parts to this article


When they talk about "The Normal Forms", mostly only the first 3 are reached when normalizing table design.
There are 4 parts.

And see "The Normal Forms: In a Nutshell"


You also need to understand many-to-many relationships.



Work through the tutorials orange provided, then keeping the rules of normalization in mind, consider this as a starting place: