Filter Records

[KarenS]

Hi, I have this table with job numbers stored by days of the week e.g. mon job, tue job, wed job etc. You can have the same job number everyday of the week or different ones. I have to get a report that shows JOB # ??? and all of the fields associated with it. I'm trying with a query, but can't get it! How can I accomplish this?

 

[tkpstock]

This violates the first normal form of database normalization - your data should be atomic (ie - you should not have two pieces of information in the same field).

I would separate the day of the week and the job number - this will facilite your query and report.

If I've misunderstood your situation, let me know...

HTH

 

[KarenS]

Hmmm... Well, what I have is an employee time sheet - I have a form with a tab control for all days of the week. For each day, I need to enter a job #, hrs worked at std rate, hrs worked at double time rate and hours worked at triple time rate. My "job" table looks something like: mon job no, mon std rate, mon ratex2, mon ratex3, tue job no etc.

Thing is, employees get transferred to different jobs almost daily - that's why I did the table this way.

Did this clarify anything for you? Or am I doing garbage?

 

[tkpstock]

Can an employee ever be on two different jobs in one day?

 

[KarenS]

Yes - it's the construction industry

 

[tkpstock]

Your data structure should be built around the data it contains, not necessarily how you want to enter it (if that make sense).

Here is a table which contains everything you need in 6 fields:

EmployeeID
Date
JobNumber
Rate1Hrs
Rate2Hrs
Rate3Hrs

Day of the week can be derived from the date. If an employee can work on more than one job number in a day, then the first three fields would be your primary key, otherwise your first two would be your primary key.

If the rate is determined non-arbitrarily (meaning it can be calculated), then you really only need the hours worked on a given date, and the rate can be calculated. That would reduce your table to three fields.

For example: If the employee gets doube-time for anything over 8 hours, then putting in 12 hours for a given day automatically give you 8 hours at the standard rate and 4 hours at double rate. This example doesn't work if an employee can work multiple jobs in a day, though.

Does this make sense to you?

 

[tkpstock]

Sorry, posted that last reply before I got your reply.

So, if an employee can work multiple jobs in a day, the first three fields would be your primary key.

Is the rate arbitrary or determined by the number of hours the employe works?

 

[KarenS]

Job No can't be a primary key because many different employees work on the same job in the same pay period. Also.. each employee has a standard pay rate (hourly). I have to enter the number of hours each day in the three categories (std, x2, x3) representing the number of hrs worked. Their gross is then calculated - ttl hrs in period x Std rate.

BTW - thanks for your responses thus far!

 

[tkpstock]

Job No can't be a primary key because many different employees work on the same job in the same pay period. Also.. each employee has a standard pay rate (hourly). I have to enter the number of hours each day in the three categories (std, x2, x3) representing the number of hrs worked. Their gross is then calculated - ttl hrs in period x Std rate.

BTW - thanks for your responses thus far!

Job No in combination with EmployeeID and Date can be a primary key. The employee ID should be unique to your employees. Job number should be unique to your jobs. For a given employee on a given job on a given day, you will have one record with x number of hours.

I've attached a sample screenshot for you.

 

[tkpstock]

I've gone ahead and built the data structure I'm talking about.

Please make sure to view and understand the Relationships.

The sample database is attached.

 

[KarenS]

U have been absolutely wonderful. Thank u, I will give it a go.