Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2014
    Posts
    36
    Provided Answers: 1

    Unanswered: Report with field with more than one value

    Hi! I have a database that tracks teachers (with all their info) for each class (among other things). Each class is assigned a teacher, but teachers can be assigned to more than one class (different days). I now want to make a staff roster that has the teacher contact info along with each class they teach, something like this:

    Name address phone 1st grade Sunday
    Name2 address phone 3rd grade Monday

    But if a teacher teaches two classes, I want this:

    Name address phone 1st grade Sunday, 1st grade Monday
    Name2 address phone 3rd grade Monday

    So how do I make a report that does this? It seems like I should be able to, but I'm not having any luck. Also, I'm pretty good with the built in Access stuff, but SQL and VBA are over my head at this point. Thanks!

    Karen

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    629
    Provided Answers: 33
    the query would join the tTeacher table to the tClassRoom table on the TeacherID

  3. #3
    Join Date
    Oct 2014
    Posts
    36
    Provided Answers: 1
    Ok, I finally found the magical terms to google to get what I want. I'm using this: http://allenbrowne.com/func-concat.html but it isn't working. I got it added as a module, and not I'm trying to use it in a report. I think I'm stuck on the 3rd argument. Help! Thanks!

  4. #4
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,101
    Provided Answers: 17
    • What values are you passing to the function?
    • What values are stored in the field whose contents you want to concatenate?
    • What error message is being returned?


    Much like the Good Lord, forum users help those who help themselves! :P
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  5. #5
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,101
    Provided Answers: 17
    [Duplicate reply posted by website]
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  6. #6
    Join Date
    Jun 2017
    Posts
    17
    Provided Answers: 1
    Yeah, this should be done in your query. I don't know what your table looks like, but something like this should work:
    Code:
    select teacher.name, teacher.address, teacher.phone, class.grade, class.day from teacher, class where teacher.teacherID = class.teacherID group by name
    Left everything fully qualified to try and help as much as possible.

Posting Permissions

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