Help with getting data out

[edwardii]

Hi,
I am a fairly basic user of access, can do most things but not very up no SQL.
I work for a charity and am building a database to manage our volunteers. I have a table with all the details for the volunteers. Each volunteer has a supervisor. I have a table with the supervisors names. I have created a combobox on the form to select which supervisor is allocated to the volunteer and all seems great.

I want to be able to run a report, where we can choose the supervisor from a combobox and it will give a list of which volunteers they supervise. So on the form i would have the combobox which gives the list of supervisors to choose from then it runs the report.

Anyone able to help?

[orange]

Are all Supervisors also volunteers?

You might consider a table that identifies the Volunteer (name , address...etc), and
has fields:
IsSupervisor datatype Y/N
ReportsTo number

where every Volunteer (Employee) has a unique Id. If the person is a Supervisor, then IsSupervisor in that Volunnteer's record is Yes.
Each Supervisor has a 0 in the ReportsTo field signifying they are the top level and do not have a Supervisor.
The records for non Supervisors have the Supervisor's Id in the ReportsTo field.

Here's an example:


You can select the Supervisors from the table to populate your Combo of Supervisors.
And, you can update the Volunteer records with the Supervisor's ID value in the ReportsTo field.

Good luck with your project.

[edwardii]

Hi Orange,

Thanks for the quick reply. Yes supervisors are also volunteers. I have added a field IsSupervisor, and have ticked all those that are, and then created a query based on there being a tick in the box, which has given me a list of supervisors. I then created a drop down list on my form and allocated the supervisor to each volunteer using this.

What i want to do now is have the same drop down on another form, and when I select a supervisor, it runs the query but only returns those volunteers who they supervise.

I am sure it is fairly simple, but I am going round in circles.

[orange]

Using my table EMPLOYEE from the earlier post, this SQL should give you the Supervisor info for the combo on your new form.
This would be the rowsource of the Combobox.

SELECT Employee.IsSuper
, Employee.FName
, Employee.LName
, Employee.EmpId
FROM Employee
WHERE (((Employee.IsSuper)=True))
ORDER BY Lname;

You can adjust the code to use your Tablename instead of Employee,
and your field names. The Order By is not required necessarily unless you want the Supervisor info in a specific order.

Hope this helps.