Results 1 to 9 of 9
  1. #1
    RedGoneWILD is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2010
    Posts
    105

    combining field from two records into one

    Hello,



    I'm new here, but plan on being active...im eager to learn and will help when i can.

    I have a database that stores student information, funding, and student advisors

    the student table and advisors table are linked through a table called student advisors, which contains just two fields (studentID & facultyID). a student can have one or two advisors.

    I have a query that displays all active students & some additional fields found in the student table, such as email address & phone number. i want to add a column for their advisor(s). this field should be just the faculty last name. however, if the student has two advisors, then i want that field to be a combo of both advisors last names (faculty1lastname & " / " & faculty2lastname)

    i have some programming experience, so i can pretty much follow whats going on in code, but not much with VB or access, so im not sure how to write this or even where to begin

    any suggestions?

  2. #2
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    i think you would be needing a crosstab query. google is your friend.

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    RedGoneWILD is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2010
    Posts
    105
    thanks for the replies

    google is my friend, but its really only helpful when you know what you're looking for. thanks for the suggestion tho. it doesn't seem like a crosstab query is what im looking for, at least based on the examples i have seen.

    im not trying to group anything, just concatenate the lastName field when there are 2 results for one studentID

    and baldy, that code seems like its what i want...but i was hoping for something a bit more noobish

    something like

    if count of studentID's = 2
    then advisor = lastName1 + lastName2;
    else
    advisor = lastName1;

    something i could enter right into the criteria section of query design view

    PS: baldy i think that code also assumes that you are only looking up one ID at a time since the varIDvalue is static "10255" in the example

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    It can be used either way. The first example shows it being used dynamically:

    SELECT Orders.*, fConcatChild("Order Details","OrderID","Quantity","Long", [OrderID]) AS SubFormValues FROM Orders;
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    In reference to your PM:

    thank you for replying to a thread i made a week ago about concatenating fields.

    but im stuck and confused. I need help.

    I have a table of students, with 44 records having a status of active. I have a table of advisors. When I run a query linking students and advisors, i get 51 results, which is correct because 7 students each have 2 advisors.

    But how do i write a query so i only get 44 records, one for each student, with a field that has advisor...and if a student has 2 advisors, they both show up once in that field...even if only by advisorID

    the code you provided before might work, but i dont even know where to enter it.

    any help is greatly appreciated!

    thanks,
    Red


    Follow up questions should be to the thread rather than the PM system. The code in the link would be copy/pasted to a standard module (don't name it the same as the function). Then you would call it from a query as demonstrated above, obviously with your own table/field names. From the sound of it, the query itself would be based on the student table, so you'd get 44 records. Can you post the db?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    RedGoneWILD is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2010
    Posts
    105
    thanks for the reply...that actually makes sense to me now

    sorry to be such a noob

    yeah, i'll try that out and can probably post a version of the db later

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problemo; we all start out as noobs, so don't worry about that.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    RedGoneWILD is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2010
    Posts
    105
    okay, im still lost

    here's an example db

    if somebody could just show me a working example of how the module and query works, i think i'll be okay...i understand the logic behind the code example, just not how it is applied in access

    thanks for the help

    PS: would only let me upload .mdb, which i guess means 2003 access file
    does it make a difference if im working in 2007 and my real db is .accdb?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 06-23-2010, 02:06 PM
  2. Combining multiple Excel records into Access
    By Jadey78 in forum Import/Export Data
    Replies: 0
    Last Post: 05-04-2010, 07:51 PM
  3. Delete all records in a field
    By cotri in forum Forms
    Replies: 6
    Last Post: 01-29-2010, 02:44 PM
  4. Replies: 2
    Last Post: 01-22-2010, 03:53 PM
  5. changing a records field value
    By tubar in forum Queries
    Replies: 3
    Last Post: 07-06-2009, 07:36 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums