Filling in the blanks

[fused13]

Hi all,

I have been trying to create a database that looks after staff holidays.

Here's how it looks at present

TBL_Holidays:

Staff_Index
Staff_Name (From a lookup)
Start_Date
Number_of_days

TBL_Holidays_Booked:

Staff_Name
Dates_Booked

Once the form (FRM_Holidays) is filled out I had (until I gave up and decided to ask for help - 2 weeks) a button that went off and run a append query.

Here's my problem. Once the button is pressed I need the TBL_Holidays_Booked to fill with the Dates_Booked.

ie.

John Smith wants his holiday to start on the 1st August for 10 days.

The resulting TBL_Holidays_Booked would look like the following:

John Smith 01/08/06
John Smith 02/08/06
John Smith 03/08/06
..
John Smith 10/08/06

Is there a way to do this? or is there a simplier way of doing it?

Regards

Mark

 

[cuttsy]

You could do some kind of nasty loop in your code using the start date and number of days:

Dim nDayCount As Integer
Dim StartDate As Date
Dim nNumberofDays As Integer
nDayCount = 0
    
While nDayCount < nNumberofDays
        
   'INSERT SQL Statement to add the staff member and Date
   Date = DateAdd(d, 1, Date)
   nDayCount = nDayCount + 1
Wend

However I would argue that you dont need TBL_Holidays_Booked because this info is already held (albeit in a different way) in TBL_Holidays

Also in TBL_Holidays if you have the Staff_Index why do you also need the staff_name?

 

[KevinM]

Your table structure is wrong.

You should only have to enter a start date and Return date for each holiday as ONE record and not 10 records for 10 consecutive holidays.

So if John Smith wants 10 days off from 01/08/06 then a typical record would be...

Name: J Smith
Start Date: 01/08/06
Return Date: 11/08/06

(i'm including weekends for the sake of the example)

A one day holiday would be (again just one record).

Name: J Smith
Start Date: 01/08/06
Return Date: 02/08/06

The 'DateDiff' function would automatically calculate the number of days.

It's like when you book a holiday at the travel agents, there wouldn't be 14 seperate records of you for a 14 night hotel booking, just one record with your checking in and checking out dates.

 

[fused13]

Thanks KevinM/cuttsy

Thanks for your suggestions, excellent. The reason I have the table 'TBL_Holidays_Booked' is that, due to staff numbers (low as usual) we can not have more than two members of staff on leave at the same time. So if John Smith is off between the 01/08/06-11/08/06. But Mary Jones wants the 07/08/06-08/08/06, there would be no way to check if there was any one else on leave at that time.

Maybe I am way off, but this seems to be the only way I can think of that would be able to verify if any other members of staff are on leave at the same time.

Regards

Mark