Managing a list of events

[weircolin]

Hi there

I have a database where it tells me in each person's record a list of
events they have attanded that have been organised. I have created
this by, after guidance, creating three tables and it is working
great. However, what I would like to know is if there is an easier
way of recording who attended what event? Let me explain

ATM the user would create a new event in the Events table, and then
would have to go into the Atendee table and from two drop down menu's
select each person and the event.

Is there any way that I could create it that when a new entry is put
into the Events table that I would be able to have it that a list of
each person's names can be shown with the event in a column with a
check box to be selected by the user or will they have to select eacj
name and event one at a time?

If so, is there a way that no person can be selected to have attended
the same event twice?

I hope this is clear, if not I will try and explain further.

Thanks

Colin

 

[Michael Gramelspacher]

Hi there

I have a database where it tells me in each person's record a list of
events they have attanded that have been organised. I have created
this by, after guidance, creating three tables and it is working
great. However, what I would like to know is if there is an easier
way of recording who attended what event? Let me explain

ATM the user would create a new event in the Events table, and then
would have to go into the Atendee table and from two drop down menu's
select each person and the event.

Is there any way that I could create it that when a new entry is put
into the Events table that I would be able to have it that a list of
each person's names can be shown with the event in a column with a
check box to be selected by the user or will they have to select eacj
name and event one at a time?

If so, is there a way that no person can be selected to have attended
the same event twice?

I hope this is clear, if not I will try and explain further.

Thanks

Colin

Let's say there is a table Events with event_id and a tale Employees with an employee_id.

Let's say we have a table EmployeeEvents with a primary key of (employee_id, event_id).

Now add a yesNo column to table EmployeeEvents and name it attended.

This fills the table for event_id =1

INSERT INTO EmployeeEvents ( employee_id, event_id )
SELECT Employees.employee_id, Events.event_id
FROM Employees, Events
WHERE (((Events.event_id)=1));

After marking attendance, run a delete query.

DELETE *
FROM EmployeeEvents
WHERE attended=0;

You are still left with an attended column in EmployeeEvents, which is essentially redundant.

Maybe someone else has another suggestion.

 

[weircolin]

Let's say there is a table Events with event_id and a tale Employees withan employee_id.

Let's say we have a table EmployeeEvents with a primary key of (employee_id, event_id).

Now add a yesNo column to table EmployeeEvents and name it attended.

This fills the table for event_id =1

INSERT INTO EmployeeEvents ( employee_id, event_id )
SELECT Employees.employee_id, Events.event_id
FROM Employees, Events
WHERE (((Events.event_id)=1));

After marking attendance, run a delete query.

DELETE *
FROM EmployeeEvents
WHERE attended=0;

You are still left with an attended column in EmployeeEvents, which is essentially redundant.

Maybe someone else has another suggestion.- Hide quoted text -

- Show quoted text -

Thanks for that, looking like it what I'm needing. However, I'm stuck
with the bit

INSERT INTO EmployeeEvents ( employee_id, event_id )
SELECT Employees.employee_id, Events.event_id
FROM Employees, Events
WHERE (((Events.event_id)=1));

Also, how do I run a delete querie?

Colin

 

[Michael Gramelspacher]

Thanks for that, looking like it what I'm needing. However, I'm stuck
with the bit


Also, how do I run a delete querie?

The query creates an attendance record for each employee for event 1.

Let's say you have a main form based on table Events.

Now we need a subform for EmployeeEvents. Since we want to see the actual names of the employees,
let's base the subform on this query.

SELECT EmployeeEvents.event_id,
EmployeeEvents.employee_id,
Employees.employee_name,
EmployeeEvents.attended
FROM EmployeeEvents
INNER JOIN Employees
ON EmployeeEvents.employee_id = Employees.employee_id;

In the end we have a main form with a subform.

The problem is that when we go to a new event, we see no records in the subform.

Let's create a command button cmdFillNames to insert names for this event.


Private Sub cmdFillNames_Click()

Dim s As String

On Error GoTo Err_cmdFillNames_Click

s = "INSERT INTO EmployeeEvents ( employee_id, event_id ) " & _
"SELECT Employees.employee_id, Events.event_id " & _
"FROM Employees, Events " & _
"WHERE Events.event_id = " & Me.event_id & ";"

CurrentDb.Execute s, dbFailOnError

Me.EmployeeEvents_Query.Requery

Exit_cmdFillNames_Click:
Exit Sub

Err_cmdFillNames_Click:
Select Case Err.Number
Case 3022 ' duplicate records
Case Else
MsgBox Err.Description
Resume Exit_cmdFillNames_Click
End Select

End Sub

Now we have an attendance record for all employees with an unchecked checkbox.

You could also have another command button to run a delete query to delete all non-attendees.

cmdDeleteNonAttendees

Private Sub cmdDeleteNonAttendees_Click()
Dim s As String

s = "DELETE * FROM EmployeeEvents WHERE attended = 0;"

CurrentDb.Execute s, dbFailOnError

Me.EmployeeEvents_Subform.Requery
End Sub

Better be sure you are finished marking attendance before clicking this button.