Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Sorting and grouping (A2K, SR1)

    My table tbl_Courses contains data on all the 200+ courses we offer to workers at an industrial site. The workers are required to complete XX clock hours in the learning lab before they undergo a supervisor's evaluation every two years. Supervisors evaluate employees using nine performance criteria, and most of our courses support issues underlying one or more of those nine criteria. Some courses support a single criterion; others support two or even three criteria. Courses that do not support a criterion we list as electives.

    My task is to produce a printable document--an MS-Access report--that will display all our courses, but sorted by the criterion number. This would be easy if all courses supported just one criterion. However, some courses will appear two or three times in this report. For example, course number 126 will appear under Criteria 2, 5, and 7. I've tried two approaches to designating the appropriate criterion in the table. In the first approach, I draw on the fact that no course supports more than three criteria. I have three fields--Crit_A, Crit_B, Crit_C--and I simply keyed in the appropriate numbers for each course. If a course supports but one criterion, that criterion number goes under Crit_A, and the other two fields remain empty.

    The second approach involves 10 fields: one for each criterion and one for electives. I labeled the fields Crit_1 through Crit_0 (zero for electives), and formatted the fields as Yes/No.

    What I haven't worked out is how to set up a query to sort out this mess and produce the report I want. My attempts have resulted in either a blank query or a query that displays each course 10 times (once for each criterion plus elective)! I'm still kinda new to this stuff, and I need some help. Anybody...? TIA

  2. #2
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting and grouping (A2K, SR1)

    Sorry about that I forgot I was in a secure workgroup files when I created the first file. Download it again and you should be able to open it now.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Sorting and grouping (A2K, SR1)

    Paul, you've done a marvelous job with your example! The report you produced is almost exactly what I'm trying to produce.

    Now...I looked at the link table and its relationships and I must confess I'm puzzled as to where those two "extra" tables came from. That is, how/where/why do we have them? They don't appear in the list of tables in the database window.... Know whadda mean?

    Thanks again from a real newbie!

  4. #4
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting and grouping (A2K, SR1)

    I'm not sure what you mean. I've uploaded another version - this one has the relationships defined in the relationship window. There should be 3 tables as discussed earlier:

    tblCourse
    tblCriteria
    tblLink

    tblCourse is related to tblLink in a one-to-many relationship as is tblCriteria to tblLink. So whenever a course is related or supports a criteria there should be a record in tblLink. One advantage of doing it this way verses having a field for each criteria in the course table is if you should add another criteria you don't need to create an additional field just create a new record in tblCriteria. The same thing holds true if you add an additional course. Hope this clears the fog (which I helped create, sorry).

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Sorting and grouping (A2K, SR1)

    G'morning, Paul! I'm writing from home now, where I use AXP; vs work, where I use A2K. I can see why my question about "extra tables" puzzled you. When I opened your attachment at work yesterday and opened Tools>Relationships, there were the three tables you described AND (honest injun) two other tables. I forget the names, but I think they each had just one field and they had (I think) one-to-one relationships with the link table. Now, this morning when I opened the attachment in AXP, those extra tables did not appear. Weird, huh? At any rate, I think I have the idea now. Thanks again!

  6. #6
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting and grouping (A2K, SR1)

    Well, this is odd. I got your new post via email but now when I come here it doesn't show up. I wonder what's going on?

    Your first question about how does tblLink know the course Math supports 3 criteria. I entered those myself in the table. I used the lookup wizard when I created the two fields in tblLink so if you go to create a new record in tblLink you get a drop down list for each field representing the values in the other 2 tables. Therefore each record in tblLink represents a relationship between a course and a criteria and vice versa.

    Your second question dealt with the creation of tblLink. A linking table such as this should contain at least the primary key fields from the related tables as foreign keys. The primary key for the tblLink consists of both fields - in other words you can only have one instance when any one course supports one criteria i.e. Math can support Trust only once. This is called a compound key when more than one field comprises the primary key. This type of key is not that uncommon so it is a good concept to learn. When in design view, select both fields and click on the primary key button in the toolbar. This will make the combination of the 2 fields the primary key for the table.

    If anything else is not unclear post again - it may or may not show up here but it looks like I will get an email.

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Sorting and grouping (A2K, SR1)

    Good afternoon, Paul: Today I downloaded your second version of your sample database. This second version answers some questions but raises others.

    Before I saw Version 2, I was unable to see how your tblLink knew which criterion/criteria each course supports. The table tblLink in the original version showed that Math supports criteria X and Y, but I could not see how the table knew that. Now I see your subdatasheets, which is a feature I have yet to explore. It appears that, by clicking the plus sign next to a course name in tblCourse, I can designate which criteria that course supports. Same goes for the tblCriteria, but therein lies a problem. I have a lot more courses, so a drop-down field is out of the question. Can you suggest how best to work this factor into the linking (junction) table. (In my original post, I tried to explain the two approaches I've used so far--I'll be happy to elaborate if my explanation is unclear.)

    Also: How did you build your link table? I tried to build one using a make-table query, but I ran into an error message: "...the resulting table cannot contain more than one Primary Key field." But doesn't your link table have two primary key fields? In design view, I see the primary key symbol alongside both of them. Thanks again!!

  8. #8
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting and grouping (A2K, SR1)

    It sounds like what you have is a many-to-many relationship between the criteria and courses. This is difficult but not impossible to model in Access. It usually requires 3 tables. In your case you would have a course table, a criteria table and a linking table which at the least would contain the primary keys from the first two tables. The course and criteria tables will have a one-to-many relationship to the link or detail table. This will not prevent a course showing up more than once on the report however. Attached is a small db I whipped up. Ignore the descriptions as they don't make sense but I hope the structure is helpful.

    ps - It's in Access97. Didn't have access to my 2k machine.
    Attached Files Attached Files

  9. #9
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting and grouping (A2K, SR1)

    OK I've uploaded a new version with a form and subform which is usually the way I deal with these situations (this version is in A2k). I used a main form for the course and a subform for the link. As you move from course record to record you can then designate in the subform which criteria the course supports.

    The 2 methods you originally posted may be simpler for data entry but I think you will find it more difficult to create a useable report. If you do some reading on normalization principles one thing you should come across says if your tables contain fields with null values repeatedly something may be amiss with the design (your first method). Another principle in normalization is not repeating the same type of information over and over across many fields (your second method having 10 criteria fields).

    Of course these are guidelines and you as the developer must decide what is best for the design of your application. Once you have a solid design everything else shold fall into place.

  10. #10
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting and grouping (A2K, SR1)

    Hi Paul

    I can't find the new version, could you point me to it or post it again?

    Thanks, John

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Sorting and grouping (A2K, SR1)

    You'll find the new version by clicking on the attachment icon in Paul's first reply. He just replaced the old version with a newer one in the same location.

    Paul: Thanks a bunch! BTW: I know you're wondering what happened to the first post you got from me today. I wrote it, then re-read your posts and decided to completely re-write my last question. Sorry for the confusion!

  12. #12
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting and grouping (A2K, SR1)

    That would explain why I couldn't see it. I guess the post is removed temporarily while you're editing it. Makes sense but it was confusing when I first opened the thread.

  13. #13
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting and grouping (A2K, SR1)

    The newest version is named course2k.zip and can be found in my original reply to this thread.

  14. #14
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Sorting and grouping (A2K, SR1)

    Hello, Paul: I've studied your sample, and things are slowly becoming clearer. At the moment, I'm trying to set up the many-to-many relationship with your database as my model. I have the three tables: tbl_Courses, tbl_Criteria and tbl_Link. In Tools > Relationships..., I get the three tables, create the relationships (all the lines are in place, with the 1's and the infinity symbols all in the right places) and save. But I've found that when I close and then immediately re-open the Relationships window, line between tbl_Link and tbl_Courses has disappeared!! Once again, I'm overlooking something.... Suggestions?

  15. #15
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting and grouping (A2K, SR1)

    I don't recall experiencing that. Try opening a new query and adding the 3 tables to it. Does the line show up in the design grid? I'll take a look and see if I can duplicate it. I don't have A2k just 97 and xp, though.

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
  •