Copying/multiple instances of sessions/bookings on a course

[postmanphat]

Hi,

I've had a look about on here and generally online and can't find what I'm looking for.

I want to create a simple course booking system. I've got the following tables (simplified):

tblUsers - table of users
UserID*
Name
etc

tblSession - table of all sessions
SessionID*
Date
Title
etc

tblSessionUser - link table between users and sessions to record who is booked on sessions
SessionUserID*
SessionID (FK)
UserID (FK)
FeePaid
etc

tblCourses - table of courses
CourseID*
Title
StartDate
etc

tblCourseSessions - link table between tblCourses and tblSessions
CourseSessionID*
CourseID (FK)
SessionID (FK)

It's all fine I can book people onto individual sessions and I can create courses and sessions. What I need some help with is booking users onto numerous sessions.

If I set up a course with 10 sessions, and 10 users are booked onto the course (i.e. those 10 users are booked on for all 10 sessions). So I'll go into my first session and book those 10 people on - is there a way I can duplicate those bookings for the remaining 9 sessions for that course? I don't want to have to manually book those 10 people on every one of the 10 sessions, it would be great if I could ammend the first session on that course then it updates the attendees for all the following sessions on that course? It may be worth pointing out that users may not all book onto a course at the same time.

Hope that makes sense - I've tried to make it as clear as possible!!!

Thanks in advance as always!

Cheers

Dave

 

[dhookom]

Typically you can use an append query that pull a couple values from controls on a form or elsewhere:

INSERT INTO tblSessionUser (SessionID, UserID)
SELECT SessionID, Forms!frmUserEntry!txtUserID
FROM tblCourseSessions 
WHERE CourseID = Forms!frmUserEntry!cboCourseID;

You could substitue a totals/group by query from tblCourseSessions and tblSessionUser for tblCourseSessions.

Duane
Hook'D on Access
MS Access MVP

 

[postmanphat]

Hi dhookom,

That very nearly works - it's not picking up the courseID or the membershipID from the subform (subSessionUsers) on the frmSession form automatically.

Also, I populate the initial course session with my 10 attendees, when I run the query as well as doing what it's supposed to do (put those 10 in all the other course sessions), it duplicates all those booked onto the first session on the first session so I end up with 20 attendees on that first session (my initial 10 all on there twice)

Cheers

Dave

 

[postmanphat]

H iAgain,

I've got it to pick up the membershipID and the courseID automatically now, but now it is just putting those attendees on the same, first session... not 'session 2'?

Thanks again for your help!

Dave

 

[dhookom]

You didn't tell us anything about subforms or membershipId.

You can use an left or right join to the existing tblSessionUser table so only those not in the current records will be appended.

Duane
Hook'D on Access
MS Access MVP

 

[postmanphat]

Sorry I meant to say UserID not membershipID.

 

[dhookom]

I would first create a query of all sessions for all course for each user

qselUsersSessionsCourses
==============================
SELECT DISTINCT U.UserID, S.SessionID, S.CourseID
FROM tblSessionUser U JOIN tblCourseSesssions S ON U.SessionID = S.CourseID;

Then create a query with qselUsersSessionsCourses and tblSessionUser that selects all records from qselUsersSessionCourses (LEFT or RIGHT JOIN on UserID and SessionID).

Then add UserID and SessionID from qselUsersSessionsCourses to the grid as well as SessionUserID from tblSessionUser. Set the criteria under SessionUserID to Is Null. This should display all possible users and sessions that are not in tblSessionUser. You can further filter this and use it to append to tblSessionUser.





Duane
Hook'D on Access
MS Access MVP

 

[lespaul]

can you have sessions without courses? do all users attend all sessions of a course?

Leslie