Creating New Records for a Date Range

[Guest]

I am creating an employee attendance database. It will be used by Managers to
log employee vacation time. Entering individual days is not a problem, but if
an employee is taking two weeks, is there a way to enter the range and have
access create records for each date within the range. The current form layout
is as follows, Corp ID, Last Name, Date, Reason Code. For example, an
employee is taking 1/01/07 to 1/15/07. Is there a way to enter this info with
having to do it individually. And of course, exclude weekends.

 

[Allen Browne]

Create a table containing a record for each work date. Just one date/time
field named (say) TheDate, marked as primary key, and save the table as
tblDate.

You can now create an Append query statement that selects the days in the
range the person will be away, and appends them to your table of absences.

If you need help getting the SQL statement to execute, mock up a query,
change it to an Append query (Append on Query menu), type in any literal
employee in the Field row. Once you have mapped the fields, you can switch
to SQL View (View menu) to see an example of the string you need to create.

Ultimately, the code will be:
Dim strSql As String
strSql = "INSERT INTO ...
dbEngine(0)(0).Execute strSql, dbFailOnError

 

[John Vinson]

Sorry if I am being confusing, but unfortunately, I am a bit confused. I
understand building a table to eliminate weekends and holidays. But I still
cant visualize tying the queried dates into my table. Here is an example of
my table:

Tbl_Attendance_Tally
Corp_ID
Last_Name
Date
Day_Code (ie:vacation, sick, personal)

For every record, this information is required.. If I build a form to
accomodate multiple date input, for example, From_Date and _End_Date and
query the business day table to retrieve all potential business days in the
range entered, how do I create individual records for each date. Last
example, I am taking off 2/1/07 to 2/14/07 and they are all vacation days. I
want the ability to enter this once. I am sorry if I am repeating myself, I
just want to make sure I am clear.

PMFJI... I see Allen hasn't had a chance to respond.

I think what he had in mind is that you would create an Append query,
drawing the date from this table of business days and the other fields
from your Form; e.g.

INSERT INTO tblAttendanceTally(Corp_ID, Last_Name, [Date], Day_Code)
SELECT [Forms]![YourFormName]![cboCorp_ID],
[Forms]![YourFormName]![txtLast_Name], tblWorkdays.WorkDate,
[Forms]![YourFormName]![cboDay_Code] FROM tblWorkdays
WHERE WorkDate BETWEEN [Forms]![YourFormName]![From_Date] AND
[Forms]![YourFormName]![To_Date];

A couple of suggestions: Last_Name is NOT suitable, unless you refuse
to hire two people who happen to have the same name. I know three men
named Fred Brown and once worked at the same university as Prof. John
Vinson. Use a unique EmployeeID instead. And, don't use Date as a
fieldname - it's a reserved word for the builtin Date() field.

John W. Vinson[MVP]

 

[Guest]

It worked.. Thank you both for all your help!!

Sorry if I am being confusing, but unfortunately, I am a bit confused. I
understand building a table to eliminate weekends and holidays. But I still
cant visualize tying the queried dates into my table. Here is an example of
my table:

Tbl_Attendance_Tally
Corp_ID
Last_Name
Date
Day_Code (ie:vacation, sick, personal)

For every record, this information is required.. If I build a form to
accomodate multiple date input, for example, From_Date and _End_Date and
query the business day table to retrieve all potential business days in the
range entered, how do I create individual records for each date. Last
example, I am taking off 2/1/07 to 2/14/07 and they are all vacation days. I
want the ability to enter this once. I am sorry if I am repeating myself, I
just want to make sure I am clear.

PMFJI... I see Allen hasn't had a chance to respond.

I think what he had in mind is that you would create an Append query,
drawing the date from this table of business days and the other fields
from your Form; e.g.

INSERT INTO tblAttendanceTally(Corp_ID, Last_Name, [Date], Day_Code)
SELECT [Forms]![YourFormName]![cboCorp_ID],
[Forms]![YourFormName]![txtLast_Name], tblWorkdays.WorkDate,
[Forms]![YourFormName]![cboDay_Code] FROM tblWorkdays
WHERE WorkDate BETWEEN [Forms]![YourFormName]![From_Date] AND
[Forms]![YourFormName]![To_Date];

A couple of suggestions: Last_Name is NOT suitable, unless you refuse
to hire two people who happen to have the same name. I know three men
named Fred Brown and once worked at the same university as Prof. John
Vinson. Use a unique EmployeeID instead. And, don't use Date as a
fieldname - it's a reserved word for the builtin Date() field.

John W. Vinson[MVP]