Main form, subform from same table

I have a table Work which has two columns, SupeID and EmpID.  This specifies the supervisors and the employees that work for them.


and so on

There is a table called Person that contains ID and FirstName, LastName for each person that is in the Work table.  SupeID and EmpID are both IDs from the Person table.

I would like a form where the top of the form would have the SupeID and their name and then there would be an embedded subform which would have a list of their respective EmpID with names.

I'm a little stuck right now because I'm getting both sets of IDs from the Work table instead of two separate tables.

Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

Mike EghtebasConnect With a Mentor Database and Application DeveloperCommented:
If you intend tp put everybody's name in one table, befor touching the forms, lets have:

PersonID            FNAme          LName         .......     IsSupervisor              IsEmploye    Excluded
autonumber         text               text                               Yes/No                 Yes/No          Yes/No

tPerson list everybody and let us know if they are supervisor or employee or both when applicable (the supervise yet work fir another supervisor). Or they were employee but promoted to Supervisor position.

SupID                 EmpID               Date  ......
Number/Long       Number/Long   Date/Time

Select * From tperson Where IsSupervisor = True And  Excluded = Falst

save the above as:  qSupervisor

Select * From tperson Where IsEmploye = True And  Excluded = Falst

save the above as:  qEmployee

To include in your form, subform, or combo boxes to populate tPersonHat as necessay.

mock5cAuthor Commented:
For simplicity, I will assume that if they are in the SupeID column, they are a supervisor.  If they are in the EmpID column, they are an employee.

So I can create queries such as:

select supeid from Work; for supervisors
select empid from Work; for employees

Of course, the above two queries only return the IDs so I just join each with the Person table to get their names.

select supeid,firstname,lastname from Person
inner join Work on Person.ID=Work.SupeID

select empid,firstname,lastname from Person
inner join Work on Person.ID=Work.EmpID

Now that I have those two queries saved as qSupervisor and qEmployee.  I would assume I can just create one form using the wizard and pull in the fields from the qSupervisor query.  Then I create another form using the qEmployee query.  Finally, I would drag the 2nd form onto the 1st form and that should do the trick, right?  I'm just worried it will fail because there is no relationship defined that says the EmpID is a FK to the SupeID.  This is getting back to my original question which is how do I do this when both are in the same table.  

At this moment, my system is crashing when I create the first form.  There are not many supes and emps so I don't know what this is happening.

Mike EghtebasDatabase and Application DeveloperCommented:
qSupervisor and qEmployee are just supplying names. you don't have any table yet to relate them togethert. If you are new to access, you better consider getting some direction by posting a question or two in "Access Architecture/Design" section to have a table structure put together before attempting to design forms or reports.  A database is table structure not forms and reports. It is a fundation for a database.

After having it done, then continue with this question.


The new generation of project management tools

With’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

mock5cAuthor Commented:
My issue isn't how to design the database.  I have those two tables that I mentioned and will be using that design for very good reasons.  So I just need help with creating the Main form - subform so that I have Supervisor's ID and name listed at the top of the form and an embedded form or datasheet will show each Employee who they supervise (with names).

In other forms I've created, I had tables where one had the PK and the other had a FK.  So it was really easy to create two forms and drag and drop the form that had the field that was a FK onto the other form.  That doesn't seem to be working for me for this.
Mike EghtebasDatabase and Application DeveloperCommented:
From what I see, one cannot build a databse using your rxisting table structure. What I will suggest to wait to get some other experts opinion on this.


Jeffrey CoachmanMIS LiasonCommented:

To be fair, I looked at this problem about 8 different ways, and I came to basically the same conclusion as eghtebas:
It would be difficult if not impossible to achieve what you are after using your current structure.

<In other forms I've created, I had tables where one had the PK and the other had a FK.  So it was really easy to create two forms and drag and drop the form that had the field that was a FK onto the other form.  That doesn't seem to be working for me for this.>
Let's take a closer look at this.
Let's say we use Customers and Orders.
CustomerID (PK)


You link Customers to Orders on CustomerID.

In your current situation I will presume you want "Supes" to be the "One", and "Persons" to be the many.
How can this be if you do not have a Supe query or table???

In this case the Work table has two fields.
These two fields are actually the same field in the Person Table!
(So which field would you join to?)
That is why it won't work.
Again I tried about 8 different query join combinations and none gave me what you were after.

Basically we are trying to "correct" an inefficient table design with queries.

Ditch the Work table and put all the required info in the Person table (along with a IsSupe Field).
(Basically following eghtebas's first post.)
You can then create a Supe query(The One Side), and a Person query(The Many side).
You can join these two queries together and create a third query. This third query will be your "Work" query.

You can now create you main/SubForm with ease.

Here is a sample:
It works just fine.

Let us know what you think.

All Courses

From novice to tech pro — start learning today.