Append Query To Create 52 Records for each of 800 employees

[Neal]

I am trying to create an append/update query that will create 52 records for each employee in our HR database. I have an Employees table linked to an absence record table using [EmployeeID] with a 1 to Many relationship. I want to create the 52 records for each employee in the Absence Record table. The following is a list of fields in the absence record table

[AbsenceID][EmployeeID][EmployeeName][Branch][WeekNo][WeekEndingDate][7 more fields one for each day of the week

I would like the query to create 52 records for each employee and to increment the [WeekNo] field from 1 to 52 e.g

[1][1][Joe Bloggs][London][1][04/01/2004] - the rest of the fields would be blan

t

[52][1][Joe Bloggs][London][52][26/12/2004] - the rest of the fields would be blan

et

Thanks in advance - otherwise someone will have to manually create 41600 records

 

[Duane Hookom]

You can create a table with a single field [WeekNo] and add the values 1
through 52. Then create an append query that includes your employee table
and the WeekNo table. Don't create any join lines. This will result in a
cartesian query where every record from your employee table is combined with
every record from your weekno table.

--
Duane Hookom
MS Access MVP

I am trying to create an append/update query that will create 52 records

for each employee in our HR database. I have an Employees table linked to an
absence record table using [EmployeeID] with a 1 to Many relationship. I
want to create the 52 records for each employee in the Absence Record table.
The following is a list of fields in the absence record table.

[AbsenceID][EmployeeID][EmployeeName][Branch][WeekNo][WeekEndingDate][7

more fields one for each day of the week]

I would like the query to create 52 records for each employee and to

increment the [WeekNo] field from 1 to 52 e.g.

[1][1][Joe Bloggs][London][1][04/01/2004] - the rest of the fields would be blank

to

[52][1][Joe Bloggs][London][52][26/12/2004] - the rest of the fields would be blank

etc

Thanks in advance - otherwise someone will have to manually create 41600

records