>>I would like to click on a company name in a company list box and have the jobs display for a person who's name is selected in a list of employees in a second list box. Each of these employees has multiple jobs in multiple companies so I would also like to click on a person's name in the employee list box and have the jobs display that the person has in the selected company from the first list box. At any point in time jobs are displayed for the selected person in the selected company.
>>
>>There must be an easy way to do this but it's not a typical many-to-many relationship. It's probably taught in Database 101 but I missed it. Any ideas?
>>
>>Thanks!
>
>Michael,
>You can get what you want with SQLs, right ? So you can do it with listboxes too. First w/o listboxes at all SQL would look like :
>
>

* Companies
>select distinct company, compid ;
> from companies ;
> into cursor crsComps nofilter
>
>* Employee
>* Assuming we don't want to restrict to employee of selected company
>* Otherwise add a where clause like
>* where employee.compid = crsComps.CompId
>select distinct lastname-(', '+FirstName) as EmpName, empid ;
> from employee ;
> into cursor crsEmployee nofilter
>
>* Jobs with restrictriction to Company+Employee
>select Job,JobExtra,JobId,CompId,EmpId ;
> from Jobs ;
> where Jobs.compid = crsComps.CompId ;
>  and jobs.EmpId = crsEmployee.EmpId ;
> into cursor crsJobs

Now all you need is to make these into listboxes :)
>
>

*lstCompany.init
>with this
> .Rowsourcetype = 3

> .RowSource = ;
>[select distinct company, compid ]+;
>[ from companies ]+;
>[ into cursor crsComps nofilter]
> .ColumnCount = 1 && Don't want to show id and no need to play with columnwidts
>endwith
>
>* Gotfocus or some other place you want to refresh a listbox content
>this.Requery() && For comapny we don't need this but using as a template
>this.ListIndex = 0 && Again we don't need here but for only jobs probably

>As you can see for listbox all we do is to 'stringify' SQL as RowSource :) You'd create the other listboxes the same way. You're free to have columncount 1-fcount() and only need to set columnwidths for columncount columns. You don't need to set boundto anything since you wouldn't use it at all. Instead you'd always use cursorname and fieldname to query a value. ie : at anytime you could get compid from companies listbox saying crsComps.CompID. Only exception to this is where you set listindex to 0. With SQL is rowsource record pointer moves in its cursor implicitly :)
>Cetin
-----------------------------------------
I do need to restrict the list of employees to only those who work for the selected company. You said:

>* Assuming we don't want to restrict to employee of selected company
>* Otherwise add a where clause like
>* where employee.compid = crsComps.CompId

The employee table doesn't have a compid field. The compid field is located in the 'joining' table between the company table and the employee table. (The joining table is the table with the job titles for each employee in each company which is what I want to display.) In the joining table I have a compid and emplid for every company and every employee. If I put the compid field in the employee table it would expand the table by a multiple of the number of companies. So, now I'm faced with the problem of how to list only the employees of the selected company in the employee list when ever a company is changed in the company list. Any ideas???