Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    Seattle, Washington, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This will probably be a no-brainer for someone... I will try to explain the scenario.


    It's an Access 2007 DB made from scratch. My goal is to keep track of my special education evaluation cases at my schools I work at.
    I have a table with all of the students' contact info from my two schools (about 800 records), tblStudents with [StudentName], [DateOfBirth], etc.
    If a student gets referred for an evaluation, it becomes an active case.
    So there is a table, tblCases, with [Student], [CaseStartDate], [TypeOfCase], etc . The [tblCases].[Student] field looks up from the [tblStudents].[StudentName] field. A case can only have one student, so I have a standard Combo Box that lets me assign a student to the Student field in tblCases and a One-to-Many relationship. Everything works fine up the this point.

    I have another table, tblActions (think David Allen, "next actions") that lets me track meetings, interviews, testing, etc. There is a One-to-Many relationship between tblActions and tblCases because there will be many actions for each case and one case per action... The problem is that I know the cases by the name of the corresponding student--e.g. "John Doe's case." Therefore the actions (meetings, etc) in tblActions need to be associated with "John Doe" (In addition to Access using the IDs). So it seems logical to have a field, "[tblActions].[Case]" that would be a Combo box allowing me choose a tblCases record from the field [tblCases].[Student]. Access *will* let me do this, but as the user, I can only see the CaseID number and not the student's name--which is the dilemma.

    Note, that if I make [tblActions].[Case] look up [tblStudents].[StudentName], it *does* show the actual kids names in the ComboBox, but this is not as useful because when I am entering an action that I have done, I don't want to choose from all 800 kids, but only from the kids that have active cases (also I want it to have a relationship with the correct table). I have tried making [tblActions].[Case] a Text field, and a Number field. I've tried changing all the different settings in the Design>Lookup Tab, and nothing seems to work. I've also tired various work arounds with queries and forms but I can only get my combo box to choose from the CaseID number, never the kid's name.... My assumption is that Access doesn't like to "look up" from a field that is itself a "looked up" field. But I don't know for sure if that is what is causing my problem... Thanks for sticking with this long post and for any ideas... If you can even point me to the right words to google, it will be helpful ) -stevek

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Can you post a sanitized copy of the database?
    Andrew

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Andrew's request is a good one, but I'll try guessing what your issue is. Combo boxes can have several columns, and the usual trick for the situation you describe is to bind the first (actually the zero) column to the record you are storing things, but to hide that column by making the width 0, and then displaying the second column. You can set those properties in the Format section of the Properties box.
    Wendell

  4. #4
    New Lounger
    Join Date
    Dec 2009
    Location
    Seattle, Washington, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Guys, Thanks for the replies. Wendell, I've tried your suggestion, though I'm going to look more into exactly how the different types of binding work. I *am* able to get the 2 columns to show, but it's just 2 columns of numbers... Argh.

    AKW, I can't get my DB to attach here at the forum. It did have a couple of VBA modules, but I've removed them and it still won't attach. I would've saved as an older DB type, but Access didn't want me to (features not compatible).
    I think I've correctly attached it to my web site. It's a small personal site and I promise there are no adds, malware, etc.

    Please anybody have a look!
    http://kunkel321.com/Documents/MyCasesSani.accdb

    Extra note: The tblStudents has a couple of pretend names in it. It's a big table that could be broken-up into smaller related tables, but I keep it as is, because the info in it gets imported from a single spreadsheet.. -steveK

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    The names of the students are only in the students table, so if you want to see them when displaying Actions, you need to get them from the Students table.
    One way to avoid this, as you have done already, is to display Actions on a subform. In this case the name of the student is shown on the main form, so does not need to be repeated for each action.

    The Cases table has a student field which is a lookup. That field is a Number field so it just stores the StudentID of the student. However it is setup as a lookup so it displays the name of the student. But what it displays is not what is really there. It is really just a number.

    You might want to look at this article on the Evils of Lookups.

    So if you want to display student names against actions using a combo box, the combo needs to draw data from the students table. But as you say, you only want it to offer students who have a case. So you need to create a query that joins Students with Cases. Use the StudentID from Cases, and the Student Name from Students.
    What will you do when a student has multiple cases? Because what you really need to do is associate an action with a case.

    PS. Most people find it preferable to use separate fields for firstname and lastname. Breaking data into smaller pieces provides more flexibility.
    It is easy to put things together (using a query) , but harder to pull them apart. There are lots of questions in this forum from people where different bits of data have been lumped into the one field, and they want help breaking it up.
    Regards
    John



  6. #6
    New Lounger
    Join Date
    Dec 2009
    Location
    Seattle, Washington, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi John and everyone,
    I'm still digesting these tips and suggestions--but I wanted to respond and say thanks!!! -steve


  7. #7
    New Lounger
    Join Date
    Dec 2009
    Location
    Berridale, NSW, Australia
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    One method that I use sparingly (because it creates a non normalised situation) is to use code to enter the name ( or whatever piece of information) into a new field in the slave table. The code could run through all your records and record the name. Then you can browse through your slave table and see the name. Use a form to view the record with the Name field locked to prevent accidental editing of it.

Posting Permissions

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