G
Guest
Hello,
My apologies if I have selected the wrong discussion group.
I need some help coding a Select query on 2 tables. Details are:
Table Definitions:
tblCompany:
CompanyID Autonumber Primary Key
CompanyName Text
tblJobTitles
TitleID AutoNumber Primary Key
TitleName Text
There are only 2 companies in tblCompany (company1 and company2)
There are approx. 15 seperate job titles. Company 1 has 2 titles. Company
2 has the remaining job titles.
I need a query that will return all jobs, Grouped by company.
Optionally, within each group, the job titles should be sorted.
It should look like this:
Company 1 Title 1
Company 1 Title 2
Company 2 Title 3
Company 2 Title 4
Company 2 Title 5
Company 2 Title 6
..
..
..
Company 2 Title 15
Right now, my query is:
Select tblJobTitles.TitleName, tblCompany.CompanyName
FROM tblJobTitles.TitleName, tblCompany
WHERE tblCompany.CompanyName = "Company1"
UNION
Select tblJobTitles.TitleName, tblCompany.CompanyName
FROM tblJobTitles.TitleName, tblCompany
WHERE tblCompany.CompanyName = "Company2"
This returns a recordset where each job title is listed twice, once for each
company. The set is sorted on the job title. Also, each job title appears
for each company, which is not correct.
Results: (These are made up titles) (oops, Company is on left, job title on
right)
Job Title Company
---------- ----------
Mfg. Mgr Company 1
Mfg. Mgr Company 2
VP. Marketing Company 1
VP. Marketing Company 2
Dir. of Prod Company 1
Dir. of Prod Company 2
IS Mgr. Company 1
IS Mgr. Company 2
HR Dir. Company 1
HR Dir. Company 2
Intern Company 1
Intern Company 1
The problem is that Company 1 has only the following 2 job titles:
Mfg. Mgr
Intern
Can anyone help me fix my query?
TIA,
Rich
My apologies if I have selected the wrong discussion group.
I need some help coding a Select query on 2 tables. Details are:
Table Definitions:
tblCompany:
CompanyID Autonumber Primary Key
CompanyName Text
tblJobTitles
TitleID AutoNumber Primary Key
TitleName Text
There are only 2 companies in tblCompany (company1 and company2)
There are approx. 15 seperate job titles. Company 1 has 2 titles. Company
2 has the remaining job titles.
I need a query that will return all jobs, Grouped by company.
Optionally, within each group, the job titles should be sorted.
It should look like this:
Company 1 Title 1
Company 1 Title 2
Company 2 Title 3
Company 2 Title 4
Company 2 Title 5
Company 2 Title 6
..
..
..
Company 2 Title 15
Right now, my query is:
Select tblJobTitles.TitleName, tblCompany.CompanyName
FROM tblJobTitles.TitleName, tblCompany
WHERE tblCompany.CompanyName = "Company1"
UNION
Select tblJobTitles.TitleName, tblCompany.CompanyName
FROM tblJobTitles.TitleName, tblCompany
WHERE tblCompany.CompanyName = "Company2"
This returns a recordset where each job title is listed twice, once for each
company. The set is sorted on the job title. Also, each job title appears
for each company, which is not correct.
Results: (These are made up titles) (oops, Company is on left, job title on
right)
Job Title Company
---------- ----------
Mfg. Mgr Company 1
Mfg. Mgr Company 2
VP. Marketing Company 1
VP. Marketing Company 2
Dir. of Prod Company 1
Dir. of Prod Company 2
IS Mgr. Company 1
IS Mgr. Company 2
HR Dir. Company 1
HR Dir. Company 2
Intern Company 1
Intern Company 1
The problem is that Company 1 has only the following 2 job titles:
Mfg. Mgr
Intern
Can anyone help me fix my query?
TIA,
Rich