Relationship problem!?

[Vo0do0uk]

Take a look at the attachement which is a .bmp


I need to make it so that shift links to employee, and an employee can only have one shift on a single day! but can't work this out.

the way it is now an employee could end up with overlapping shifts on the same day!?

please advise!

 

[Vo0do0uk]

Perhaps i could create a Unique index for staffname and date?

but not sure how to tackle this!

 

[Mile-O]

How's this for you?

It will only work provided that on the DateOfShift field, you only put dates in and not a date and time as 06/05/04 (which is actually 06/05/04 00:00:00) is different from 06/05/04 23:59:59 for example.

 

[Vo0do0uk]

Fantastic!

thats exactly what i'm after!!

been trying for days to find a way of making sure that an employee can only work one set of times on a specific day!

thanks a million

 

[Rich]

No overtime allowed then?

 

[Vo0do0uk]

for your next trick:

take a look at jpeg, it's the warning box that displays this information saying the primary key blah blah

it's confusing, and the user won't have a clue what that means

how can i get this to read something like

"You already have that employee working on that day"

Cheers

 

[Mile-O]

I'd use the OnError event of the form. If you are still dealing with the table structure then the forms should be a little bit away.

 

[Parker]

for your next trick:

take a look at jpeg, it's the warning box that displays this information saying the primary key blah blah

it's confusing, and the user won't have a clue what that means

how can i get this to read something like

"You already have that employee working on that day"

Cheers

What about overrides for double shifts etc? What happens when someone changes shifts? Or are you saying that this never happens?

 

[Vo0do0uk]

if i ran a query to find everyone who worked on say 6/05/2004 it would show all their names and start/finish times. i was thinking of just setting up a form to allow user to change finish time to incorperate any 'overtime'

its for a restarant, and luckly they never ever work after 23:00 so thus it should work on a daily basis!

what do you think??

 

[Vo0do0uk]

Employees don't work double shifts, if there were to work say day shift and night shift then the start would be say 09:00 and finish would be 23:00.
but this is a rare occurence

as for changing the shifts, if an employee doesn't work this shift then the user can delete that record of them working, and put in another record for the employee whom took his place

I'm only building a simple system really.

 

[Parker]

Employees don't work double shifts, if there were to work say day shift and night shift then the start would be say 09:00 and finish would be 23:00.
but this is a rare occurence

as for changing the shifts, if an employee doesn't work this shift then the user can delete that record of them working, and put in another record for the employee whom took his place

I'm only building a simple system really.

As I said to you on another thread. I would store time worked seperate from the sequence for calculating rosta.

 

[Vo0do0uk]

As I said to you on another thread. I would store time worked seperate from the sequence for calculating rosta.

Sorry Parker - i keep getting confused

The time worked is the information from calculating the rosta. to change the time worked i would need to change the rosta. so these will have the same information

-- why would i need to record the same information twice?

 

[Parker]

Sorry Parker - i keep getting confused

The time worked is the information from calculating the rosta. to change the time worked i would need to change the rosta. so these will have the same information

-- why would i need to record the same information twice?

NO NO NO NO NO !


Time worked is time worked

Time schedule is time shedule.

If I tell one of my staff there working for 16hrs on Next Sunday they may agree(not if the've got any sense) but they may phone in sick on Saturday.
What are you going to do then? Print a new rosta so everyone knows in time?

If the guy attends then use a riutine to say that he completed the shift. If he dosn't attend then, chances are someone else will fill in, so you just record their times instead. If he does attend but doesn't complete the shift then you just need to ammend his finish time.

Rosta is a calculation ------- don't store calculations.
Time worked is information and needs to be stored.

Once again you have two issues (pos 3 if you want to be clever)

1. Schedule
2. time worked (for wages and IRS calc)
3. (pos)availability

Idealy you would check availability then work out the rosta and then store the time worked for wage calculations.

If you store availability you can use this in a query to work out the rosta. Then print the rosta using a report (as in my examples) sometime latewr you would enter the actual times (into a form bound to a table) worked and use this with queries to work out wages, etc.