Query to generate multi records

[hlam]

I need to generate each employee a record for each week in the month. I have
a table that has all employee names, another table that has the week no of
the month. In order to do this I have to create 3 queries.

1. to retrieve employee name from employee table (a1)

SELECT tbl_Employee.Op_ID
FROM tbl_Employee
WHERE (((tbl_Employee.Active)=True));

2. to retrive the week no of the month (a2)
SELECT tbl_Select_Rec.Week_no
FROM tbl_Select_Rec
GROUP BY tbl_Select_Rec.Week_no;

3. to produce records for each employee (a3)
SELECT a1.Op_ID, a2.Week_no
FROM a1, a2;

Is there a better way of doing this to replace these 3 queries by just one?

 

[Steve Schapel]

Hlam,

I don't understand the purpose of the GROUP BY clause in the second query.

Anyway, why not just like this?...

SELECT tbl_Employee.Op_ID, tbl_Select_Rec.Week_no
FROM tbl_Employee, tbl_Select_Rec
WHERE tbl_Employee.Active=-1

If the tbl_Select_Rec table contains duplicate values in the Week_no
field, then...

SELECT DISTINCT tbl_Employee.Op_ID, tbl_Select_Rec.Week_no
FROM tbl_Employee, tbl_Select_Rec
WHERE tbl_Employee.Active=-1