Heads gone to mush! help!

[Vo0do0uk]

I'm almost done with a db ive been designing to store information for a restaurant.

i have a prob and can't think of solution... but it's prob easy!!

I have a shifts table which records the dates and shifts that employees are going to work this takes the format of Employee Name, Shiftdate, starttime, finishtime.

This all works fine

However...

I would like to now include a record of dates that employees have booked off. and somehow have this link to shifts. so that:

If Mr x gets a record put in booked off that says Mr X, shiftdate

when it comes to writing the shifts Mr x could be assigned a shift on that date!?

sounds easy eh? i'd love your suggestions. just i've got really far and would have a play but worried about ballsing it up

cheers

 

[Parker]

I'm almost done with a db ive been designing to store information for a restaurant.

i have a prob and can't think of solution... but it's prob easy!!

I have a shifts table which records the dates and shifts that employees are going to work this takes the format of Employee Name, Shiftdate, starttime, finishtime.

This all works fine

However...

I would like to now include a record of dates that employees have booked off. and somehow have this link to shifts. so that:

If Mr x gets a record put in booked off that says Mr X, shiftdate

when it comes to writing the shifts Mr x could be assigned a shift on that date!?

sounds easy eh? i'd love your suggestions. just i've got really far and would have a play but worried about ballsing it up

cheers

First save a copy of what you have done -- do a compact and repair first.

Work with thw copy for development. Right now we have that out of the way you are now talking about one of the things I suggested earlier -------Availability.

Create a table to store another set of times and dates. Decide if you want information relating to when an employee is not available or when they are available. I would suggest, in your case 'not available would be better. Link this table to your employes table.

The rest is query bassed again I'm afraid.

Modify the query you already have to read the dates and times not available and include them in your sort. Or use an if loop in VBA to check not available dates and then pass that as a sort criteria to the SQL and run the whole thing in VBA. But I would say in this case stick to your queries because I know you are on a tight time scale and it will be easier for you to work out.

HTH

 

[Vo0do0uk]

Ok.... you won't belive it but i'm really getting somewhere now i'll post it later and let you see!! i WILL BE FINISHED FOR TOMMOROW!!

it's looking sweet - got action querys to update and append records i'm quite proud.

anyway parker:

I've compacted, copied and created new table it's called tblAvailability

In it i have: EmployeeID and dateofshift as compound PK i figured i needed this like in shift as you don't want user to enter same date off for same employee again. i also have shift time and shift finish.

Now without giving the game away too much!? i know you like to teach not to tell

how the chuff do i get the query to work!?

I have a query that takes all the data from tblShift. in this i have added columns such as day etc and selected "dddd" as format so it shows this.

this query is then made into a form which is called Form weekly rota

this is where i have my append/delete query. So on every monday i can delete from it all the records before the monday and they go a table called old shift. therefore i have a clear rota form to enter the next week's dates

it's a little more complicated but i'm sure u get the gist, anyway how on earth Mr Parker do i edit this query to then make sure that if an employee has an entry in the availablity field (which i am going to only record times they can't work i assume they can work any other time other than this.)
the user automaticaly isn't able to input a shift for the user,

ur right about the whole VB code thing thats a no no! (maybe next project) i'm not giving up!

can't think of query could u poss give example? sorry for massive post APU

 

[Vo0do0uk]

Ok:

I've managed to get the dayofshift from the tblavailability into my query for the rotas which contains the information from tblshift.

so i have tblavailability.dayofshift and then in the next column tblshift.dayofshift and all the other columns but i guess there not too important for this example.

i need to somehow tell the query that if there is an entry in the tblavailability.dayofshift the user can't put an entry into tblshift.dayofshift and therfore not being able to enter a record!

(am i miles away!!!)

 

[Parker]

Ok:

I've managed to get the dayofshift from the tblavailability into my query for the rotas which contains the information from tblshift.

so i have tblavailability.dayofshift and then in the next column tblshift.dayofshift and all the other columns but i guess there not too important for this example.

i need to somehow tell the query that if there is an entry in the tblavailability.dayofshift the user can't put an entry into tblshift.dayofshift and therfore not being able to enter a record!

(am i miles away!!!)

No your bang on target,
Good too see you are finally getting somewhere and happy to here that you are going to meet your deadline.
Sorry I din't have the time right now to give you an example but if yopu lookn in Access help do a search on criteria and pick 'Enter criteria to affect when calculations are performed' that will explain the lines I was thinking about. You should be able to work it out from there.

Horendously busy over weekend but I'll look in when I can. (tip --If you really gat stuck email me; it takes a while but I get it mobile and if I can answer I will)
Good luck

 

[Vo0do0uk]

Access help do a search on criteria and pick 'Enter criteria to affect when calculations are performed' that will explain the lines I was thinking about. You should be able to work it out from there.



had a look here mate but not sure thats what i'm after, or at least i couldn't work it out from the three example it gave me!

This problem seems to be very difficult!! i think i may leave it out and work on it as something that can be included at a later date

it would have been 'lovely' to include it but i have the essential back bones as promised!

thanks very very much for your help!! without u telling me to pull my finger out and do it! i'd of still been sat here waiting for someone to do it!!

I'd like to think i'm getting there on access now.

Speak soon, cheers.