Desperate - DB development

  • Thread starter Thread starter naja
  • Start date Start date
N

naja

Hi All!

I've been struggling with a DB for my department. I'm don't have that much
knowledge on Access, but I'm learning on my own, but this particular request
has been giving my a lot of trouble. Can somebody helpy to developed this
DB. I have the tables setup already, my only problem is making the form to
behave the way I want. If anybody knows how to upload this file so that you
can look at it, let me know. Thx
 
naja said:
Hi All!

I've been struggling with a DB for my department. I'm don't have that much
knowledge on Access, but I'm learning on my own, but this particular request
has been giving my a lot of trouble. Can somebody helpy to developed this
DB. I have the tables setup already, my only problem is making the form to
behave the way I want. If anybody knows how to upload this file so that you
can look at it, let me know. Thx
Click to expand...

Be wary of people who approach you here with offers to help you for cash
- it's rather against the ethos of this very helpful newsgroup. If you
choose to approach someone that's ok, but first see if folk here can
help you do it yourself. Learning Access is very rewarding but it does
take time and patience.

However, if you go about things the right way, Access can deliver
results in remarkably little time, even for a relative newcomer.

The secret? Get you _tables_ and relationships right (and do seek
advice here on this). Then the various wizards in Access can work
wonders! Meanwhile, you should manage expectations among those waiting
for results.

If you want to post your database somewhere then skydrive.live.com is a
good vehicle for this, but you should be aware that I haven't seen this
done here (although it seems a useful thing to consider). Beware of
including confidential data.

So, what I'd suggest is you post details of your tables and what they
mean first. Include details of what you want the form to do, but expect
most attention to be focused on your tables - that really is the
critical step, so don't be disheartened if you're asked to step
backwards before moving forwards!

Meanwhile, have a look at a couple of my favourite links on table design:

http://www.lynda.com/home/Player.aspx?lpk4=31001

http://tinyurl.com/ms-table-design-tutorial

Phil, London
 
The only people who will work on an entire app, expect to get paid for it.

Instead, try to specifically identify the problem you are having and we will
be glad to help for free. Our role is to help you build an application, not
to do it for you.
 
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.
 
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.

:
Click to expand...

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
 
Back
Top