Create multiple records from a date range 1

[tamer64]

Here is my dilemma.

I have created an attendance DB which works great for single event entries. But I was approached today and was asked what if the employee takes a week off? That would mean 7 seperate entries would need to be made in the DB. Currently, users fill out a form which then appends the date to a table. There is one date field "DateofOccurence" and I am thinking I would need to add two additional field "Start Date" and "End Date" to the form and tables. In essence, the supervisor would have the option of entering a single Date in the field "DateofOccurrence" for single events or option(2) using the Start and End date for events other than a single occurrence.

How would i create a query that will take a single record (if the start and end dates option is used) and create multiple records Here is my example:

Table1
EmployeeID, FullName, Code, DateOfOccurrence, StarDate, EndDate, Remarks
2334, JohnDoe, PTO, 7/1/13/, 7/7/13, out sick.

Append the data to Table2 whereby the start and end dates are inserted into the DateofOccurrence field.

Table2
EmployeeID, FullName, Code, DateOfOccurrence, StartDate, EndDate, Remarks
2334, JohnDoe, PTO, 7/1/13, Out Sick.
2334, JohnDoe, PTO, 7/2/13, Out Sick.
2334, JohnDoe, PTO, 7/3/13, Out Sick.
2334, JohnDoe, PTO, 7/4/13, Out Sick.
and so on...

Any suggestions you can provide is very much appreciated.
Thank you in advance!

 

[SkipVought]

Hi,

Create a Working Calendar table that includes the working dates for each year. Then join.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[BigRed1212]

if the employee takes a week off? That would mean 7 seperate entries would need to be made in the DB

I don't see that as a bad thing. Think of it this way: entering a start date and an end date and then categorizing that time period really is in a way summary data. you are kind of summarizing what happened. You don't want summarized data- you want it at the atomized level.

What do you do with a 1/2 day of sick (got sick, went home)?

I don't think you want your time information and your employee info in one table. Your Table 1 should be stuff about employees: name, id, department, address, anniversary date, phone number, etc.

Then I would have a table of time (Table2):
recordid pk
employeeid
workdate
houramount
type

John works all day on the 15th and the 18th he has two entries in Table2;

1   2334 7/15/13  8  regular
765 2334 7/18/13  8  regular

Fred (Employee 2551) takes a vacation day, drinks too much, comes in the next day, works half a day, throws up, and goes home sick the rest of the day. He has three entries in Table2:

2   2551 7/15/13  8  vacation
955 2551 7/16/13  4  regular
705 2551 7/16/13  4  sick

This Thursday, maybe they both get:

12 2334 7/4/13  8  holiday
75 2551 7/4/13  8  holiday

Bill takes a week off he has 5 or 7 entries.

Something like this makes a query like "show me what John did this pay period" a lot easier.

select type, sum(houramount)
from table2
where workdate between payperiodstart and payperiodend
and employeeid = 2334
group by type

 

[Andrzejek]

In addition to BigRed1212's suggestion, I would have a separate table for Type:

[pre]
TypeID (PK) Type_Desc
0 Regular
1 Vacation
2 Sick
3 Holiday
4 Hang-Over
5 Goofing Off
...
[/pre]

Have fun.

---- Andy

 

[BigRed1212]

...
6 Posting on Tek-Tips
...

 

[tamer64]

Thank you all for your replies!

I do have the tables constructed as you mentioned.

All I really need to do here is somehow create a query that will take a single record that has a date range and insert those records into another table (creating multiple records for each date in the date range).

Table1

EmpID, EmployeeID, FullName, Code, StartDate, EndDate
1, 232, Frank, PTO, 2/1/13, 2/4/13
and Insert into Table2

Table2
EmpID, EmployeeID, FullName, Code, DateofOccurrence
1, 232, Frank, PTO, 2/1/13
2, 232, Frank, PTO, 2/2/13
3, 232, Frank, PTO, 2/3/13
4, 232, Frank, PTO, 2/4/13

How can this be accomplished in a sql query?

 

[SkipVought]

SELECT
  a.EmpID
, a.EmployeeID
, a.FullName
, a.Code
, b.date

FROM
  Table1 a
, CandarDates b

WHERE b.Date >= a.StartDate
  AND b.Date <= a.EndDate

resultset

EmpID EmployeeID FullName Code Date
1.0   232.0      Frank    PTO  2013-02-01 00:00:00
1.0   232.0      Frank    PTO  2013-02-02 00:00:00
1.0   232.0      Frank    PTO  2013-02-03 00:00:00
1.0   232.0      Frank    PTO  2013-02-04 00:00:00

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Andrzejek]

You can do this in VBA:

Dim datDate As Date
Dim strSQL As String

strSQL = "SELECT * FROM Table1 WHERE ..."
rs.open strSQL, Cn

datDate = rs!StartDate

Do While datDate <= rs!EndDate
    strSQL = "INSERT INTO Table2 (EmpID, ... , DateofOccurrence) " & _
        " VALUES (" & rs!EmpID & ", ... , #" & datDate & "#)"
        
    Cn.Execute strSQL
    [green]'Add 1 day[/green]
    datDate = DateAdd("d", 1, datDate)
Loop

Have fun.

---- Andy

 

[tamer64]

SkipVought,

I tried this and received the following error:

"The Microsoft Access Data Engine cannont find the input table or query "CandarDates".

SELECT
T_201_B_Creates_Employee_Event_Table.EmployeeID, T_201_B_Creates_Employee_Event_Table.LName,
T_201_B_Creates_Employee_Event_Table.FName, T_201_B_Creates_Employee_Event_Table.MInitial, T_201_C_Creates_Employee_Event_Table.DateofOccurrence

FROM
T_201_B_Creates_Employee_Event_Table, CandarDates T_201_C_Creates_Employee_Event_Table

WHERE T_201_C_Creates_Employee_Event_Table.Date >= T_201_B_Creates_Employee_Event_Tablea.StartDate
AND T_201_C_Creates_Employee_Event_Table.Date <= T_201_B_Creates_Employee_Event_Table.EndDate;

 

[BigRed1212]

If you choose Skip's method which relies on the calendar table he mentioned earlier, the cheap and dirty way to make the calendar table is to do a fill > series > date in Excel from a chosen start date and import that as a table.

 

[Andrzejek]

That's because Skip said:

Create a Working Calendar table that includes the working dates for each year. Then join.

Table:
CandarDates
[tt]
Date
...
2013-02-01
2013-02-02
2013-02-03
2013-02-04
... [/tt]

Have fun.

---- Andy

 

[tamer64]

Thank You all again for the replies.

Let me put together a calendar (BigRed212 great idea using excel). This gives me something to workon the rest of the afternoon. I hope this all works and gets me out of this bind.

Have a Great Day!

 

[BigRed1212]

Don't forget that if you want to "create rows" in an existing table as you said, you will want to wrap Skip's returned result set in an INSERT INTO statement.

INSERT INTO Table2 (fields)
SELECT stuff (same number of fields)
FROM Table1
WHERE etc

 

[tamer64]

Ok Thanks again. If I run into problems and or have questions I will post it.