Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help needed for creating complex form (2003)

    Second screenshot moved into attachment by HansV because it was WAY too large. Please don't post pictures larger than 640 x 480 pixels!

    Good Day guys,

    I'm trying to create a pretty complex form which is supposed to give me data for a Word Document. Because of its complexity I'm trying to devide this into seperate questions...

    1. What table structure is needed for frmDraft? (see pic1 and attached Database)

    <IMG SRC=http://www.rvl-medizintechnik.de/temp/snap.gif>
    <pic1>

    Explanation for 1: Each School can have different assigned Countries and each of those countries can have different Committees assigned. I don't know now how to set up a relational table with everything taking care of.
    Is a table like this a good solution or is there different solutions u can think of:

    .tbljoinSchoolCountryCommittee
    [ID] < tblSchools.ID
    [UNMember] < joinSchoolsParticipation.UNMember
    [CommitteeID] < tblCommittees.CommitteeID

    Now that would give me a table like this:

    <table border=1><td>1</td><td>3</td><td>1</td><td>1</td><td>3</td><td>4</td><td>2</td><td>6</td><td>1</td></table>

    I'm trying to find different approaches because my final goal will be an automatically generated word document such as this (pic2):
    HansV: See attached zip file
    <pic2>

    I hope my first idea gets clear a bit and you can tell me if that's a good table structure?

    Yours
    Dennis

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Help needed for creating complex form (2003)

    Is committee membership dependent on the country AND the school? I.e. country 3 could be a member of committee 4 in association with school 1, but it could also be a member of committee 9 in association with school 3?

  3. #3
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help needed for creating complex form (2003)

    No.

    It goes like this: School 1 has countries 3+4, each of those countries can be in different committees
    School 2 has countries 1+9, each of THOSE countries can be in different committees.

    Let me come up with question 2 right away since I'm working on this right now.

    My table looks like this right now:
    <pre>.tbljoinSchoolMemberCOmmittee
    [ID] < tblSchools
    [UNMemberID] < joinSchoolsMember
    [CommitteeID] < joinSchoolsCommittee
    [Participants] = Participants per Committee (running sum)
    [Year] < frmYear
    </pre>


    Question 2: How to put the right data into the table?

    I know what I want to do with the table, but I don't know how to achieve it.
    The user should be able to only click the available Committees for that Member, but I just don't know how to do that.
    What I think of is that I have some data in the table such as:
    School 1 : Country 1 : Committee 2 : : 2005
    School 1 : Country 1 : Committee 5 : : 2005
    School 3 : Country 5 : Committee 5 : : 2005
    School 3 : Country 9 : Committee 1 : : 2005
    School 3 : Country 5 : Committee 2 : : 2005
    : : : Committee 1 : 1 Participant : 2005
    : : : Committee 2 : 2 Participants : 2005
    : : : Committee 5 : 2 Participants : 2005

    I hope you're slowly getting my idea.
    ---------------------------------------------------------------------------------------

    Shortly in written form of how the basic process works in this database:
    1. School (School X) sends 12 Participants
    - Since there is 10 Committees but 12 Participants -->
    2. School gets assigned 2 Countries (Spain + UK)
    - this is where the database stops now, BUT what it should do -->
    3. For each of the two assigned countries can be chosen which committees will be available (Spain: 1,3,5,6,7, UK: 1,5,9,3,2)
    4. Database calculates the total participants per committee (one participant per country, so just a running sum)

    So the data we have would be:
    <pre>School X
    - 12 Participants
    - Spain
    --- 1,3,5,6,7
    - UK
    --- 1,5,9,3,2
    Committee 1 = 2 Participants
    Committee 2 = 1 Participant
    Committee 3 = 2 Participants
    Committee 5 = 2 Participants
    Committee 6 = 1 Participant
    Committee 7 = 1 Participant
    Committee 9 = 1 Participant
    </pre>

    ---------------------------------------------------------------------------------------

    Dennis

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Help needed for creating complex form (2003)

    Slow down! You haven't got the table structure right yet.

    If I understand correctly, a country is assigned to a single school. Will this be the same school every year, or can Country A be assigned to School 1 in 2005 and to School 2 in 2006?

  5. #5
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help needed for creating complex form (2003)

    No problem. Slowing down.

    You're right. Country A can be assigned to School 1 in 2005 and to School 2 in 2006.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Help needed for creating complex form (2003)

    The primary key in joinSchoolsUNMember should be on UNMemberID and Year (i.e. the school ID should NOT be in the primary key). You should assign only one school to a UN member for a given year.

    If committee membership is by year too, the next join table you need is joinUNMemberCommittee, with fields

    UNMemberID
    CommitteeID
    Year

    The primary key is on the combination of the three fields, since one UN member can be in several committees, and a committee has several members in a given year. Note that the school doesn't come into this, since committee membership is not dependent on the school.

  7. #7
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help needed for creating complex form (2003)

    Gotcha. I was just trying to keep the number of tables low - but I guess it's just not possible [img]/forums/images/smilies/wink.gif[/img]

    I think that should be a good structure for the tables, but now there's still problem two existing... (as I said this is rather complex and I myself have problems with the idea [img]/forums/images/smilies/smile.gif[/img])

    Dennis

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Help needed for creating complex form (2003)

    Perhaps the attached version will help. Warning: it is not complete - it doesn't take the year into account everywhere. It's just meant to give you an idea of how the form could look.
    I added a table joinSchoolUNMemberCommittee with fields ID, UNMemberID, CommitteeID and Year (together forming the primary key), and a query qryJoin that returns all committees associated with a school.

    frmDraft now contains a multi-select list box that is populated in the After Update event of cboSchool, and a command button that saves the selected items to the table.

  9. #9
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help needed for creating complex form (2003)

    Ah! That's something to get me definately started!

    I'm still preferring the solution with checkmarks instead of a list, but it's pretty hard to create those items via table (just like a list box) - but easier for the user to choose from.
    Additionally it would be nice to have one list for each Country instead of all assigned countries for one school in one listbox (that's to make it easier for the user too). I'll try working on that and get back to you.

    Thanks
    Dennis

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Help needed for creating complex form (2003)

    The problem with check boxes is that you'd have to modify the design of the form if the list of committees changes.

    To have all available committees for a school in one list seems easier to me - the user can take the whole situation into account while deciding which committees to select. But if you think otherwise, it's possible to add a combo box listing the countries assigned to a school, and to use this to filter the list box.

  11. #11
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help needed for creating complex form (2003)

    Yeah I was just thinking that checkboxes are easier for the user... anyhow. You definately gave me a good starting point and I'm quite conent with what I have. The thing is now that I don't know how to filter the list box based on the selected Memberstates... (take a look at attachment).

    The problem is now that as soon as you select another assigned Member, then select available committees, all old selections get lost (because of the DELETE SQL statement). I guess I have to change something here, but I'm not sure.

    (I left out the frmUNMember and tblUNMemberCommittee you created because they are not needed and it's easier this way.

    Dennis

    Attachment: new is xxxDraft and qryJoin2!

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Help needed for creating complex form (2003)

    Since you inserted a UN Member list box, you need to take it into account when deleting the old selection:

    strSQL = "DELETE * FROM joinSchoolUNMemberCommittee WHERE ID = " & Me.cboSchool & " AND UNMemberID = " & Me.lboMember

  13. #13
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help needed for creating complex form (2003)

    Works.

    I will work with this for a while and get back to the forums as soon as I have some progress - start a new topic in reference to this one and we'll see [img]/forums/images/smilies/smile.gif[/img]

    Thanks a lot so far

    Dennis

Posting Permissions

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