Append Queries to related ID and its related IDs?

[Banana]

I have a list of class sessions. To help with maintaining attendance, each session has its own roster. I just need to add a functionality so user can make sure the attendance roster is synchronized with the past sessions and future sessions in events of last minute additions/drop or updating names/contact infos. The sessions are related to themselves as below:

ClassID  PreviousClassID
1  Null
2  1
3  2
4  Null
5  4

So therefore to propagate the changes into all sessions how would I instruct the query to look for each related ID then checks if it's also related...

I'm thinking I may have to use VBA to do what, but I hope that can be done with a append query.

Thanks!

 

[grnzbra]

I'm not sure what you want to do. In the above example, after you are done, what should you have? What do you mean by session? ie Accounting 101 Morning Session and Accounting 101 Evening Session. or Accounting 101 Monday Session and Accounting 101 Thursday session. or Accounting 101 Fall Session and Accounting 101 Spring session.

Are the records of students?

 

[Uncle Gizmo]

I'm not sure I get it either! Is the class ID, the ID of a subject, an actual room, or is it the id of a student taking a class?

 

[Banana]

Oh boy. I have to apologize for garfunkling up my post!

The ID here refers to ClassID. The attendance roster is actually in another table as it is a many-many relationship (more than one person can be enrolled in one class, and can be enrolled in several other classes).

There is already a junction table that I can just look up the ClassID and copy the Attendee information to the new session.

By session, I mean a session in a series. For example, one of our class has three meetings. I wanted to be able to note who may have came to our first session, but didn't make it to second and third or something like that. Hence, each session had their own attendance roster.

Furthermore, I related the sessions using self-join to ClassID, hence "PreviousClassID". I'm now having a head scratching session as to why I designed that the session would look up only immediately preceding session, and not to the first class. I thought I had a good reason, but now I'm not very sure!

Assuming the data is properly normalized, I want to know if a query can easily retrieve a ClassID that is in a given session's PreviousClassID, look its PreviousClassID up and retrieve the next ClassID until there is nothing more to retrieve, so I know that the query will update all sessions of a class.

I hope it's now clear as mud.

 

[Banana]

Was it clear enough?

 

[grnzbra]

Let's see if I've got it.

Student A attends session 1
Students A and B attend Session 2
Student B Attends session3

You have a student table and an attendance table which contains a 4 records

Student A Session 1 Attended Yes
Student A Session 2 Attended Yes
Student B Session 2 Attended Yes
Student B Session 3 Attended Yes

You want to have two more records

Student B Session 1 Attended No
Student A Session 3 Attended No

If this is what you want, put both tables into a query but don't join them. You will get one matchup between each record on each side. You may have to put in a DISTINCT because I think you'll get duplicate records (9)

Is that what you mean?

 

[Banana]

grnzbra,

Thanks for answering my question; that was useful info, however the crucial clue I'm after is how to *retrieve* all sessions, since a session is related only to its immediate previous session and immediate next session. We may have several multi-session class of same topics so I can't just search by name of the class and date of class. I want to enable my user to review the attendance from *any* session, and observe attendence for all sessions. Therefore I want to know if SQL is capable of looking up a immediate previous session, then checks their previous session, if any, and adds to the collection to be ultimately displayed.

Did that clear it up?

Again, thanks!

 

[grnzbra]

It sounds like the sessions are fields in a record. Previous Session, Current Session, Next Session. You need to have a seperate record for each session. That way, it doesn't matter which session you wish to view.

 

[Banana]

No, each sessions are a record of their own.

Maybe I'm being dense, but I'm not seeing how I can get query to return all sessions of single class when the session are related only to immediate previous/next sessions. I'm suspecting that I should have had designed to have a class table and a session table to make relating easy.

Currently the relationship is something like this:

tblAttendees
PersonID
ClassID

(the details about Attendees are kept in tblPerson)

tblClass
ClassID
ClassTypeID
ClassDate
PreviousClassID
*some more details

tblClassType
ClassTypeID
ClassType

ClassType indicate what kind of class this is. However they are not necessary unique in tblClass as we can use same materials at different dates or for a different audience. Hence a lookup table is used for ClassTypeID in tblClass.

Each record in tblClass actually represent single session, as some class only have one session, whereas other may have several sessions. In latter case, whenever a new session is added, the first session's ClassID is placed in PreviousClassID. Third session inherits the second session's ClassID. See my first post for an example of how it breaks down.

The question is about how to retrieve ClassID for all sessions of single multiple session class to be updated/appended/reviewed.

Did that help any?

 

[grnzbra]

The PreviousClassID is the problem. If you are saying that the Class ID changes from session to session, I think you have a design problem. However, if you are stuck with that design, you could create a table that would indicate which class it was and all the Class IDs associated with it and the dates of association.

Perhaps the ClassTypeID would be the value what was common for all the sessions of the same class. You would then have the dates in what is now the previous session id field and you could pick the sessions by date. Or you could create a compound key consisting of ClassID and TypeID and that way you could number ClassID for each ClassTypeID and use the same numbers over again for a different ClassTypeID

 

[Banana]

Just so I understand this right, I could just make ClassID and ClassTypeID a composite key and use it for "PreviousClassID", thus no change to design other than re-defining a key, correct?

I'm not sure how Access handles composite keys, given that ClassTypeID is derived from a lookup table, while ClassID is a autonumber.

 

[grnzbra]

No. The whole concept of the Previous ID field limits you to only the previous ID. Following that design would lead to a new field every time a new session is added and you'd end up with, in addition to PreviousID, PrePreviousID, PriorPrePreviousID and all of that.

If ClassID is autonumber you can't do it that way.

I think you have a design problem. You are using the ClassID, which changes from record to record to reference something that occurs several times in the table. You need to get rid of the autonum field and replece it with two fields.
ClassID and TypeID (or how about SessionID and ClassID). To create a composit key, highlight both fields in design view and then click the key button. Now you can have duplicate numbers in either field but not duplicate combinations. So you could have Session 1, Class1; Session2, Class1; Session1, Class2; but you couldn't have another Session1, Class1. Then if you want to take attendance, your attendance table would have both as the foreign key.

 

[Uncle Gizmo]

Don't forget to back-up!

>>> You need to get rid of the autonum field <<<

I am sure I don't need to mention this, however I'm going to anyway! Don't forget to back-up!

 

[Banana]

All right.

I was hoping to make minimum design changes as possible. Sounds like composite key might be asking too much. It also wouldn't work, because there will be same classes taught at different dates, meanign I'd need three-part composite key (date) to make it truly unique, which would be useless.

I guess I'll just create another table to hold an autonumber and group sessions together with a common autonumber from that table instead.

Unless I got this wrong, thanks for your help!

Edit: That post was "submitted" yesterday but never made it until I returned to the work; internet was lost.

Gizmo, thanks for the reminder. *looks over at bajillion backups* Another one coming up....

 

[grnzbra]

If you are stuck with keeping the PriorClassID, perhaps you could use VBA to loop through the table looking for the ClassID that equals the PriorClassID of the previous record and append to a table.

 

[Banana]

Was that to imply my proposed solution of adding a table to group together session isn't feasible? I don't see any problems with adding the table and modifying PreviousClassID to be related to this new table so I'm not really stuck with keeping it.

 

[grnzbra]

I'm not trying to imply anything. I still not sure of what you are doing. My suggestions were based on what I thought you are doing at the time I make the suggestion.