Choose a record without making a subform

[Guest]

Here is what I have so far. Table Events, EventID Primary Field. Table
Employees, EmployeeID primary field. Table EventEmployee, EventEmployee
Primary Field, EmployeeID foreign Key and EventID Foreign Key.
I created a Form called Events with Subform EventsEmployees. So I have the
information of the event on the form, and I can choose more than one employee
for each event. So far this is no problem.
I have another table called Positions, PositionsID Primary Key. Another
table called EmployeePositions, EmpPos primary key, PositionsID foreign key,
and EmployeeID foreign key.
I can create another Form that has Employee as the Main Form and Employee
Positions as the SubForm and choose more than one positions for each Employee.
Now what I cannot do is create a Form that has the Event and a Subform that
I can choose the Employee based on the position. In other words I want to
know what position this employee can do and add it to the Events.

Is this posible?

Thanks

 

[Guest]

I have created the tables and relationships as you have specified.

tble_employee
EmployeeID (PK)

tble_eventemployee
EventEmployeeID (PK)
EmployeeID (FK)
EventID (FK)

tble_events
EventID (PK)

tble_employeeposition
EmpPosID (PK)
PositionsID (FK)
EmployeeID (FK)

tble_positions
PositionsID (PK)

Each employee can have many events
Each event can have many employees

Each employee can have many positions
Each position can have many employees


"create a Form that has the Event and a Subform that I can choose the
Employee based on the position". Can you be clearer?

I think you will need to improve the design.

 

[Guest]

I need to choose several employees for one event. For example, I have Event:
Wedding that needs 2 waiter and 1 busboy. In the Event Form I want to see the
name and position of an employee and choose Peter(waiter), John(waiter), and
Alex(busboy) in the subform. That is my idea so far, but maybe there is a
better way.
Thanks

 

[Guest]

Ok, this part of my problem I got it figured out. Like I said in my previous
post, I have a Form called Events and a Subform where I can choose employees.
What I did in the drop down menu of the employees is add the position to the
same column via concatenation(&). Hope this clears things up, I have a few
other problems to solve, and I am sure I will ask for more help.

 

[John W. Vinson]

Ok, this part of my problem I got it figured out. Like I said in my previous
post, I have a Form called Events and a Subform where I can choose employees.
What I did in the drop down menu of the employees is add the position to the
same column via concatenation(&). Hope this clears things up, I have a few
other problems to solve, and I am sure I will ask for more help.

If you're storing multiple positions in one field by concatenation - YOU ARE
ON THE WRONG TRACK. That's *very* bad design and will come back to bite you;
it will be very much harder to search for data by position.

What do you have against subforms!? They are the ideal solution to your
problem: it is a perfectly typical many (employees) to many (positions)
relationship, normally implemented by adding a third table with fields for
EmployeeID and PostionID. The subform would be based on this table and let you
quickly and easily select multiple employees for a position, or multiple
positions for an employee.


John W. Vinson [MVP]

 

[Guest]

Funny you mention it, I was just thinking about that last night. I cannot
figure out another way to choose the employee based on the position. This is
the level that I have so far...
Client
Agreement
Event
Employee
Position.
Between Event and Employee I have a linking table and between Employee and
Position I have another linking table. How can I have a Form with Event
information with a subform to choose more than one Employee based on the
position?

Thanks

 

[Guest]

Here is a bit more information on what I have so far. I have this form that I
want to use as a subform to choose Employees based on the position that they
can perform. The Form has the following Control Source in SQL....

SELECT EventEmployee.EventEmployee, EventEmployee.EventID,
EventEmployee.EmployeeID AS EventEmployee_EmployeeID,
EventEmployee.OrderDate, EventEmployee.RequiredDate, Employees.EmployeeID AS
Employees_EmployeeID, Employees.FirstName, Employees.LastName,
EmployeePositions.EmployeeID AS EmployeePositions_EmployeeID,
EmployeePositions.PositionsID, Positions.PositionID, Positions.Position
FROM Positions INNER JOIN ((Employees INNER JOIN EmployeePositions ON
Employees.EmployeeID = EmployeePositions.EmployeeID) INNER JOIN EventEmployee
ON Employees.EmployeeID = EventEmployee.EmployeeID) ON Positions.PositionID =
EmployeePositions.PositionsID;

I cannot update the records, so I think this is where I am at fault. Could
you please look at it and let me know where the problem is?

Thank you very much.
Carlos