SQL help please???

[qajussi]

Hi!

I am so overwhelmed with this query.
As an example, I have two tables.

TblManager
ManagerID
managerName

TblEmployee
StaffID
ManagerID
StaffName

I want to write a query if possible to get all the staffNames when the manager's name is given?

Can you help??
Thank you very much in advance

 

[Lothario]

please post some sample data and the kind of output you are looking for.

-L

 

[lespaul]

aren't managers employees too? You don't need a second table to hold managers. (This structure doesn't work if a single person has more than 1 manager). I would also recommend spliting the name into First & Last if you haven't already done so.

tblEmployees
StaffID
StaffName
ManagerID

StaffID            StaffName            ManagerID
1                   Joe Blow                3
2                   Jane Doe                1
3                   Robert Jordan           2
4                   Terry Goodkind          1

Now if you want a list of staff names and the manager name:

SELECT A.StaffName As Employee, B.StaffName As Manager
FROM tblEmployee A
INNER JOIN tblEmployee B ON A.StaffID = B.ManagerID

this will produce:

Employee           Manager
Joe Blow           Robert Jordan
Jane Doe           Joe Blow
Robert Jordan      Jane Doe
Terry Goodkind     Joe Blow

Leslie

 

[qajussi]

Thanks so much for your help and I am sorry for the bad example.

TblCompany
CompanyID
CompanyName

TblStaff
StaffID
CompanyID
StaffName

TblAffiliatedStaff
StaffID
TypeofAffiliation
AffiliatedStaffID <=== StaffID

++++++++++++++++++++++
Above table structure will be fine.
I am just using them as examples.

I am trying to make a analysis tool.
I have one combo box and two list Box.
A) combo box ==> b) List BOx ==> C) List Box.
Say A)combo box contains the name of company and when I select one of the company name, I want all the staffname who work for the company in B) List box.
And when I select one of the staff name in B) List box, I want all the staff name in C) List box who are affiliated to that staff in B) List box.

I want to code this in Vbscript behind the combo box and list box.

How do I code in Sql to get the
staff names when I choose the company name from the combo box so I can insert the staff names in the B) List box??

Same thing for C) List box,

If I choose any of the staff name in B) List box, I want the affiliated staff name so I can insert them in the C) List box.

I hope I provide you enough.

THanks millions in advance

 

[PHV]

A starting point.
RowSource of comboA:
"SELECT CompanyID, CompanyName FROM TblCompany ORDER BY 2"
In the AfterUpdate event procedure of comboA you set the RowSource of listB:
"SELECT StaffID, StaffName FROM TblStaff WHERE CompanyID='" & Me![comboA] & "' ORDER BY 2"
In the AfterUpdate event procedure of listB you set the RowSource of listC:
"SELECT S.StaffName, A.TypeofAffiliation FROM TblAffiliatedStaff A INNER JOIN TblStaff S ON A.StaffID = S.StaffID WHERE A.AffiliatedStaffID='" & Me![listB] & "'"

If any of the ID field is defined as numeric in the table then get rid of the single quotes in the corresponding where clause.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244