Relationship query needs answering!

[Guest]

Hi,

Due to work demands some temp employees in my office may work in more than
one department.

for my database I am aiming to record the total number of hours each
employee works in any number of departments on a weekly basis.

I am struggling to understand table relationships. At the moment my database
allows for multiples dates and multiple departments but how can I be sure
that the working hours information is attached to the correct date?

Or is it better to have a one-to-many relationship? If that is the case, I
want to be sure that a new date will create a new set of records for the
employees.

 

[Guest]

Open Access
Click on File - New - Templates (On my computer)
Choose Databases tab
Choose Time and Billing Icon, Click [OK]
Type in the name you want yourmdb file to have, click [Create]
When the Database Wizard open, click [Next]
Allow the Wizard to choose stuff for you, so click [Next], [Next], [Next]
Type in the title you would like your database to have, click [Next], [Finish]

Now, you have tables, queries, forms and reports already made for you.

 

[Guest]

There is also this template, which you can download, from Microsoft's site:

http://office.microsoft.com/en-us/templates/TC010184611033.aspx?CategoryID=CT011366791033

 

[Guest]

Sounds like you should consider adding another field. One for assigned
department and another for work department. Lots of organizations use
charge codes that relate to the department charge for the work.

 

[Guest]

Hi,

thanks for the link. However, what I require is a little bit more
complicated than this but it is along the right lines!!!

My database needs to store working hours information for each employee on a
weekly basis but each employee can work for more than one department in a
week.

I created a form (date) with a sub form (dept). Basically, I can create
multiple departmental records for a week and each week will have its own set
of records.
This, in turn, will be put into another form (employee). I hope this clears
it up a little bit.

I am struggling a bit to know how close I am.

I think I am right in saying that a foreign key is in in the table to which
the relationship is going. The tables I have at the moment are as follows
(from left to right).

Employee

Payroll (PK: 1)
Employee
work
status
rate

date

Payroll (FK: Many)
Date (PK: 1)

Link

Date (FK: Many)
Dept (PK: Many)

Dept

Dept (FK: 1)
Subdept
Costcentre
Standard
timehalf
double

As far as this tells me, each employee has more than one date and each date
has more than one department. The last link tells me that many departments
can only have one set of recorded working hours (can that be right?)

In terms of data collection, these are the only fields that I need but if
more are needed to be added to be certain of the relationships then it will
be done!

thanks

 

[Guest]

I have created some forms I am now 95% sure I have the database the way I
want it.

 

[Immanuel Sibero]

Scubadiver,

I have created some forms I am now 95% sure I have the database the way I
want it.

Let's put it to the test (I think one way to help you is to critique your
design)
- How would you print a list of employees from, let's say, the Operations
department? You wouldnt be able to.

- How would you print a list of subdepartments of, let's say, the Operations
department? You wouldnt be able to.

- Let's say I work 8 standard hours today (May 19th) and 5 standard hours
yesterday (May 18) for the Operations department How would you record this?
Where would you record the data? In the Dept table? Is the field Dept a
primary key?

There are many more holes I see, but let's start with the above.


HTH,
Immanuel Sibero