Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Christchurch
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi, I have a database recording pupils, classes and teachers with the following fields and relationships: There is a many-to-many relationship between tblPupils and tblClasses via 2 1-to-many relationships with the intermediate table tblPupilClasses. tblClasses has fields Room, Year, ClassID (primary key) (pupils stay together in a class for the duration of a calendar year in this school). tblPupils has various fields and PupilID (primary key). tblPupilClass has PupilID (joins to tblPupils), ClassID (joins to tlbClasses), and PupilClassID (primary key). There is the equivalent thing set up with staff - many-to-many relationship between tblStaff and tblClasses via tblStaffClass.

    On a form for pupil information, there is a sub-form for class information, so that several "year and class" entries can be made. The record source for the sub-form is a query containing fields from the tables tblPupilClasses and tblClasses. This is working well, entries can be made for the the years a pupil is at the school, for the various pupils.

    How would I go about creating a query etc so I can find other pupils who were also in a particular "year and class" combination? The results could be displayed either in a control on the form, or on a report, however I am not sure how to approach the design of the query to find the information.

    Regards, Roger

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Create a query based on tblPupils and tblPupilClasses, joined on PupilID.
    Add the fields from tblPupils that you want to display.
    Also add the field ClassID from tblPupilClasses, and clear its Show check box.
    In the Criteria line for ClassID, enter the expression

    [Forms]![frmPupils]![sbfPupilClasses]![ClassID]

    where frmPupils is the name of the pupil form (the main form) and sbfPupilClasses is the name of the subform as a control on the main form.
    If you wish, you can also add tblClasses to the query, linked to tblPupilClasses by ClassID, and add fields from tblClasses to the query grid.

    You can use this query as record source for a list box, for another form or for a report.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Christchurch
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Hans for the fast reply. I have created the query as you explain and created a list box based on it. I see that I have more issues to resolve since the only name I see in the list box is that of the first record. The database may not yet be functioning correctly, and I may not have set the relationships correctly after all? Hopefully the following will provide more clarity on what I am trying to achieve.

    A "class" is basically a group of pupils who use one particular room only on any given year. So it makes sense to pre-populate tblClasses with the known rooms and years so we would have in tblClasses (as well as a primary key):
    1980 1
    1980 2
    1980 3
    .
    .
    .
    1989 1
    1989 2
    1989 3
    1989 4

    For any class there are many pupils. And a pupil over their time at the school would be in many classes (over various years). Hence I need a many-to-many relationship and via the intermediate table - tblPupilClasses. I think I have this right?

    Any particular class has only one teacher that year. A teacher could teach a number of classes over subsequent years, so this would simply be a 1-to-many relationship and therefore doesn't need an intermediate table? Do I have this right?

    So I thought I would pre-populate tblClasses with the years and rooms since this is known and we don't want data added outside of the known year/room pairs. However when I use the form and add 1989 and room 4 for a pupil, I get a second record in tblClasses with data "1989 4". This might explain why my list box you have proposed doesn't show another pupil also in room 4 in 1989? How would I limit data entered in my sub-form to only what is pre-populated in tblClasses? Am I correct in thinking I only need one row in tblClasses containing a year/room pair?

    I hope this is making sense. Thanks for any further help you may be able to offer.
    Roger

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The data structure and relationships look good to me - as far as I can tell you have everything set up correctly.

    I think the problem is with the record source for the subform. It needs to contain the ClassID field from tblPupilClasses, not that from tblClasses.

    And since you have predefined the room-year combinations, the user should not enter them separately, but select a combination from a combo box (dropdown list) bound to ClassID, with tblClasses as Row Source, and with ClassID as the hidden first column, and Room and Year as visible columns.

  5. #5
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Christchurch
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The correct ClassID field is already included. I eventually realised the cause of my confusion - the problem is the list box that I created was only getting refreshed when opening the form. I added to the list box "on double click" event a simple "me.refresh" and see that it is functioning as intended. I now have a couple of questions following on from here.

    First, we'll probably start off with the list box not visible, and have it not visible when moving to a new record on the main form, and add a command button to show it and refresh the data. How do I reference the list box from the command button code to change visible=true and do a refresh?

    I'd also like to refer to the fields in the sub-form and string them together in a text box to function as a heading above the list box so I can have something like "class mates in room " & [room] & " and " [year]". What is the syntax for referring to those fields?

    Thanks very much for your help.
    Regards Roger

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The On Click code for the command button would look like this:

    Code:
    Private Sub cmdShowListbox_Click()
      With Me.lstClassmates
    	.Visible = True
    	.Requery
      End With
    End Sub
    Let's say that the subform as a control on the main form is named sbfPupilClasses. The control source for your text box could be

    ="Class mates in " & [sbfPupilClasses]![Room] & " and " & [sbfPupilClasses]![Year]

  7. #7
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Christchurch
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks again Hans, this is coming along well. A couple more little questions if I could? First, I'd like to set visible=false when navigating to a new main record - which form event is the right one to use here?

    Second, I am needing to refine the text of the text box so that it takes values from the sub-form fields ONLY when clicking the command button, could you help with the syntax of that? Currently it updates depending on which record in the sub-form has the focus. I don't think I want to be specifying a control source for that text box at all.

    Regards, Roger

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    > First, I'd like to set visible=false when navigating to a new main record

    You can use the On Current event of the main form:

    Code:
    Private Sub Form_Current()
      If Me.NewRecord Then
    	Me.lstClassmates.Visible = False
      End If
    End Sub
    > Second, I am needing to refine the text of the text box...

    In that case, leave the control source of the text box blank, and set its text in the On Click event procedure of the command button:

    Code:
    Private Sub cmdShowListbox_Click()
      With Me.lstClassmates
    	.Visible = True
    	.Requery
      End With
      Me.txtSomething ="Class mates in " & [sbfPupilClasses]![Room] & _
    	" and " & [sbfPupilClasses]![Year]
    End Sub

  9. #9
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Christchurch
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' date='29-Sep-2009 23:50' post='795436']
    > First, I'd like to set visible=false when navigating to a new main record

    You can use the On Current event of the main form:

    Code:
    Private Sub Form_Current()
      If Me.NewRecord Then
    	Me.lstClassmates.Visible = False
      End If
    End Sub
    My apologies - I said the wrong thing there. I want this to be triggered any time the main form moves to any different record, not just specifically a new one?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Simply remove the If and End If lines:

    Code:
    Private Sub Form_Current()
      Me.lstClassmates.Visible = False
    End Sub

  11. #11
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Christchurch
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='794531' date='24-Sep-2009 00:21']And since you have predefined the room-year combinations, the user should not enter them separately, but select a combination from a combo box (dropdown list) bound to ClassID, with tblClasses as Row Source, and with ClassID as the hidden first column, and Room and Year as visible columns.[/quote]

    We have this combo box set up, unfortunately the table populating it has around 1500 records, so selecting a particular room/year combination is quite unmanageable!

    1980 1
    1980 2
    1980 3
    .
    .
    .
    1989 1
    1989 2
    1989 3
    1989 4

    so there might be (say) 15 records containing 1980, but maybe a couple of extra classrooms were built and available in 1981, so there is no guarantee of the same number of rooms in a given year (but the information is known).

    Is there some way that this combo box populated with so many rows could be split up into 2 separate combo boxes, the first one looking at unique year entries from the populating table, and the second returning only the rooms for that year?

    Hope this makes sense. Regards, Roger

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='woja' post='796708' date='07-Oct-2009 10:18']Is there some way that this combo box populated with so many rows could be split up into 2 separate combo boxes[/quote]
    Yes - see Basing one combo box on another.

  13. #13
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Christchurch
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Hans, that is a great article, I think I can see how to get this working. In some ways my situation will be easier since it is not from 2 tables and the same principles will apply.

    The first thing I think I need to do is create a query that finds unique rows from qryClasses. I can create a combo box with the wizard which creates the sql for me:
    SELECT qryClasses.classID, qryClasses.year FROM qryClasses ORDER BY qryClasses.year

    and this populates the combo box. I need to include the primary key classID to make this work (which is fine). However this shows all the 1500 records from the table, 20 or so for 1980, 20 for 1981 etc. How can I modify my query so that it returns unique entries from the "year" field only? I have been trying unsuccessfully to do this with including DISTINCT in the sql but my syntax knowledge is poor.

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Try

    SELECT DISTINCT qryClasses.year FROM qryClasses

    You shouldn't include classID here because it is not unique within each year. You don't need an ORDER BY clause; this is implicit in SELECT DISTINCT.

  15. #15
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Christchurch
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='797048' date='09-Oct-2009 12:57']Try

    SELECT DISTINCT qryClasses.year FROM qryClasses

    You shouldn't include classID here because it is not unique within each year. You don't need an ORDER BY clause; this is implicit in SELECT DISTINCT.[/quote]

    Thanks, yes that does work, the problem I had when trying that before and not seeing data was related to the number of columns and their width.

    I have very nearly got the selection of rooms working, I have a combo box displaying the available classes that are based on the year selected in the first combo box. However the problem now is that each time I select 1980 for a year, and Room number 1, I am adding a NEW row to the table that the year/room combinations are coming from. What I actually want to do is have a pre-populated table with year and room combinations, and so not be adding the new record each time a pupil is added to a room. Now if I select 1980 as the year, I have 2 rows in the combo box containing room 1, and this breaks the ability to find other class mates who were also in room 1 that year.

    Hope this makes sense and you might be able to suggest where this is going wrong? We had this working earlier in the thread with a combo box that was populated by the total list from that table, and I'm a bit confused why it doesn't do this now.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •