Query to generate multi records

  • Thread starter Thread starter hlam
  • Start date Start date
H

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?
 
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
 
Back
Top