naja said:
Hey Philip,
thanks for your quick response and most of all thanks for all your advises,
you are the best and that shows you are a caring person, GBU.
ok, let me begin to tell you a little bit of what they requested me to do.
This DB is a Cost Savings tracking (CS).
There is multiply group within my dept. with different function.
Each employee needs to report their CS for this year and last year.
Table1 has the following:
Employee_Name
Group_Name (the division they belong to)
Table2 has the following:
Savings_Category
Table3 has:
IT_Department
Table4 has:
Amount_Saving
Year
and the last table is the main table where all these data is going to go and
this table has all the above fields plus the following:
Business_Unit_Beneficiary
Date_Savings_Start
Action_Taken
Title
Attachment
What I ambitious to do is in that the main form has the persons's name and
their department, the date when the savings started, type of savings. and on
the subform the calculation per year for that person. so what I want is that
when I select the name of the person automatically the form populates all the
data regarding that person on the subform. I just need to synchronized the
data when is selected.
:
You say the "main table" has all the fields of the other tables. One of
the fundamental guidelines of relational databases like Access is that
you avoid just that. "One fact, in one place" is better.
Let's see if I've got this:
You have a list of employees. Each employee is a member (let's say this
doesn't change) of a Group/Division. I'm guessing that Groups are part
of a Business_Unit. One Groups: many Employees. One Business_Unit:
many Groups.
To structure your tables correctly, you want to pay close attention to
relationships: One-to-Many, or Many-to-Many.
One-to-Many relationships are "modeled" by having separate tables for
the two "things" (posh word is "entities"), and to include the "ID" of
one entity as a "foreign key" in the other's records. So, if you have a
table Groups, you'd have fields for Group-ID and Group-Name. In the
Employees table, you'd have a column for "Group-ID". It helps Access
help you if you "register" (for want of a beter word) the relationship
in the Relationships window.
Your tables "IT_Department", "Savings_Category" sound like simple lists
- the sort of thing you'd put in a drop-down ("combo box"). This is
fine. Each will be a simple table with an ID (key) and a text field,
and the key values will be recorded in other tables as foreign keys.
The table Employee might have these fields (to start with):
Emp-ID (autonumber, primary key)
Emp-Name (Text - maybe more than one field for this: FirstName, LastName)
Emp-ref (Text, internal staff reference number/code)
Emp-Group (long integer, Foreign Key with a value from the Groups table)
Now we have to look at how to record the cost savings. If I read you
correctly, you need to be able to record a total saving for each
employee for each year. I'm not sure how "Savings_Category" fits into
this - what if an employee comes up with four ideas on one year, all in
different categories? Then you'd want to be recording Ideas, not Totals.
Where does Action_Taken fit into all this? (And I can't figure Title
and Attachment at all
Now I'm guessing, but one feasible scenario is that you need a table of
Ideas, including a date field, and you derive the Yearly total by
summing all the Amount_Saving values year-by-year. Does that sound ok?
Then you can associate an Action_Taken, and a Savings_Category, with
one idea.
So, if I'm anywhere near on this, you'd have a bunch of minor tables
which simply store lists of the names of things like Groups,
Savings_Category, and so on.
Your most important tables would be Employee, and Idea. One Employee,
Many Ideas, and we total the Amount_Savings for all the Ideas in one
year to get a total.
Could there be more than one Action_Taken arising from just one Idea?
If so, then we'd need a one-to-many structure (two tables, one storing a
Foreign Key) for that too.
Does this make sense? Or have I read you wrongly? Do you see the
approach I'm taking? Look for patterns: One-to-Many and Many-to-Many.
(I'll explain how we implement Many-to-Many relationships if we come
across any).
Have a think about this and see if you can clarify things for me. I
need to understand more about how this information fits together, and
how it will be used.
Phil
