Access Relationships Help! (1 Viewer)

[Paul]

Hi,

I am inexperience with databases and access and would welcome some assistance on a problem I am having with my table structures.

From our time sheet system I am able to get a dump of employee ID, date , hours, cost code, etc I have been able to link this to employee name and cost code description tables with no issues. The problem I am having is the project has a set of hourly charge rates which is dependant on the organisation you work for, the date the hours were incurred and the agreed project category (position) of the employee. I have create a table with the organisation, rate effective from date and project category as primary keys and other fields for the rate etc. I then have been trying, unsuccessfully, to get this table related to the others with no joy. When I run a query to multiple the hours by the rate for each cost code each entry in the time sheet get all the rates applied rarher than the correct rate i.e. multi entries for the same hours. Any assistant guidance would be great.

Thanks

Paul

 

[jzwp22]

I'm not totally sure about your business model and I was a little confused about your tables, so let me just start from scratch.

You have employees, so we'll need a table to hold them

tblEmployees
-pkEmpID primary key, autonumber
-txtFName
-txtLName


You have organizations

tblOrganizations
-pkOrgID primary key, autonumber
-txtOrganizationName

The employees belong to an organization; I assume that many employees belong to an organization.

tblOrganizationEmployees
-pkOrgEmpsID primary key, autonumber
-fkOrgID foreign key to tblOrganizations
-fkEmpID foreign key to tblEmployees

You have projects
tblProjects
-pkProjID primary key, autonumber
-txtProjectName

You have categories or roles that an employee can be in

tblCategories
-pkCatID primary key, autonumber
-txtCategoryName

It sounds like employees are somehow assigned to each project & with that a category or role the employee plays with respect to the project

tblProjEmployees
-pkProjEmpID primary key, autonumber
-fkProjID foreign key to tblProjects
-fkEmpID foreign key to tblEmployees
-fkCatID foreign key to tblCategories


You need some way of capturing the hours for employess related to a project

tblProjEmpHours
-pkProjEmpHoursID primary key, autonumber
-fkProjEmpID foreign key to tblProjEmp
-dteWorked
-hrsWorked

I am still a little unclear as to the rate issue. Is the rate determined based on the employee's base rate and then that base rate is increased by some factor due on the organization for which the employee works? And then is another factor added to the rate depending on the project category of the employee? For example, let's say employee A gets $5.00 base rate. They work for organization 1 that entitles them to an additional 10% increase in their rate, so $5.00 + 0.1*(5.00)=$5.50. And then they are a supervisor for project A which entitles them to yet another 10% of their base rate: $5.00+ (0.1*5.00)+ (0.1*5.00)=$6.00. Perhaps you could explain the rate issue further?

 

[Paul]

Thanks you for your help; you have spent some time in assisting!

I will change my structure as I now understand that my tables were not correctly normalised and see how I get on.

The rate issue. Each employee has an internal rate which is know as a target rate. This is the rate the organisation is targeting to charge out people (field EmployeeTRate). However, on projects the rates are nearly always different (often less). The project rate is normally decided by a project position i.e. civil engineer (field ProjCat) and can have little relation to the target rates set by the organisation. These rate could during the cause of a project experience a rate increase and therefore I need a valid from and to date so the date the hours were incurred can apply the current rate. I also need to deal with possibility of a person project rate changing througout the project i.e. promotion, assigned to different role etc. Its probably worth mention the other organisations are sub-consultants and won't have a target rate just a project rate.

Thanks once again for you help.

Thanks

 

[jzwp22]

Since you can have many rates for an employee during the course of the project you have a one-to-many relationship which requires another table

I had this table describing the employee-project

tblProjEmployees
-pkProjEmpID primary key, autonumber
-fkProjID foreign key to tblProjects
-fkEmpID foreign key to tblEmployees
-fkCatID foreign key to tblCategories

We'll need this table:

tblProjEmpRates
-pkProjEmpRatesID primary key, autonumber
-fkProjEmpID foreign key to tblProjEmployees
-currRate
-dteEffective (effective date--i.e. start date)

You technically do not need the end date since if there is a new record added, it will have a new effective date, so the end date for the previous rate will be the day before the new effective date.

 

[Paul]

Hi,
Thanks once again for your structure. I have set the tables up as described but am yet to test the system. One question I do have is in the tblProjEmpRates:

-pkProjEmpRatesID primary key, autonumber
-fkProjEmpID foreign key to tblProjEmployees
-currRate
-dteEffective (effective date--i.e. start date)

What is there to stop the situation of having the the same ProEmpID with different rates but with the same effective date. Wouldn't access get confused on which rate to select? I orginally thought that the dteEffective could be part of the key to keep the ProEmpID and effective date unique?

Thanks for your assist so far.

 

[jzwp22]

Having two different rates for the same project employee with the same effective date would violate good business practices I assume. To prevent the duplication, then, is the job of the database designer (you I assume). You will need some code in the form you design for this to prevent any duplications.

 

[Paul]

Thanks once again.

I have been looking at using the index function to avoid duplications across multi fields. This seems to resolve my concern.

Another issue I have experienced is when using lookups to tables to enable the user to see a meaningful piece of data rather than the auto number reference, i.e. project number rather than the auto number, I cann't seem to copy and paste data from excel. It seems to want the autonumber reference Its quite strange as it lets you select the the value i.e Project number, just not paste the value. I'm guessing its looking for the autonumber generation reference.

Thanks once again for your assist its looking pretty good now.

Paul

 

[jzwp22]

I'm not clear as to what you are trying to say here especially with the reference to Excel:

Another issue I have experienced is when using lookups to tables to enable the user to see a meaningful piece of data rather than the auto number reference, i.e. project number rather than the auto number, I cann't seem to copy and paste data from excel. It seems to want the autonumber reference Its quite strange as it lets you select the the value i.e Project number, just not paste the value. I'm guessing its looking for the autonumber generation reference.

When using a combo box on a form, you can adjust what is displayed in the combo box for the user to see. If you don't want the user to see the autonumber field, adjust its column width property to zero. To do this, go into design view of the form click on the combo box and then right click and select Properties-->Format-->Column Widths. The bound field of the combo box should be your autonumber field since it binds to the underlying table. The general consensus is that primary and foreign key fields in a table should have no significance to the user, but there is much debate on the topic (natural versus surrogate keys).

If you are using lookup fields at the table level, then you might want to check out this link