Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2004
    Posts
    7

    Unanswered: Concatenating Text when grouping records

    Here's a fun one:

    I'm currently working on our budget - there are cases where I'm submitted maybe five inputs to one line item from different departments, each of which includes a text field explaining the assumptions behind that line item.

    In my process, I subtotal all five line items into one record. What I want to is to have my query capture whatever's in each text field of those 5 submissions and to string them altogether in the text field of the single subtotalled line. The actual count of submitted line items will range from zero to maybe 10.

    I see that this can be done with VB at the report level, but I need to do it at the query level to prepare the budget data to be lined up with prior year actuals data.

    Thanks for any suggestions!

    Galen Moore

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Galen,

    Can you provide an example of what your trying to do?
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Aug 2004
    Posts
    7

    Concatenate text in a subtotal query

    As Submitted:

    Accnt Code Jan$ Feb$ Etc$ Text:
    1234567 1 1 1 HR travel Cost, $1/month
    1234567 2 2 2 Admin travel Cost, $2/month
    1234567 3 3 3 Engineering travel Cost, $3/month
    1234567 4 4 4 Production travel Cost, $4/month

    Desired Subtotal Query output:
    Accnt Code Jan$ Feb$ Etc$ Text:
    1234567 10 10 10 "HR travel Cost, $1/month, Admin travel Cost, $2/month, Engineering travel Cost, $3/month, Production travel Cost, $4/month"

    Many Thanks!

  4. #4
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    GM here's an example of a concate I do in a query:

    [LastName]+", "+[FirstName]+" "+IIf([middleInitial],[middleInitial],"")+" "+IIf([suffix],[suffix],"") AS FullName

    This is in SQL. So Its taking a person's Last name, first name and checking for middle int & a suffix

    so if the table was and had a few records like
    LastName______FirstName_MiddleInt___suffix
    Good_________Johny_____B.___JR
    Smith________Hank
    Baldin________Adam_____ _____III

    You would have a field (in that query/report) and the results would be:
    FullName
    Good, Johnny B. JR
    Smith, Hank
    Baldin, Adam III

    If you dont have the Iif's you'd get an error
    Hope this gives you and idea
    Last edited by rguy84; 09-01-04 at 14:32.
    Ryan
    My Blog

  5. #5
    Join Date
    Aug 2004
    Posts
    7

    Concatenating Text - Subtotal Query

    Thanks - but I'm chasing a slightly different problem; If we assume that Mssrs. Good, Smith and Baldin are employees in the same Widget department who happened to produce 1,000 widgets each,

    Input, before subtotal&Concatentate record text function:

    DeptID Widgets Employee
    1234 1,000 Good
    1234 1,000 Smith
    1234 1,000 Baldin


    I want to subtotal that numeric data on DeptID while concatenating the text info in a query to get the following output:

    DeptID SumofWidgets Employees
    1234 3,000 Good, Smith, Baldin


    Thanks,

    Galen

  6. #6
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    1. Are you able to determine how many of these records you have for any particular time you want to combine them into one string?

    2. Are the records in the same table or do they all come to you in different tables?
    If they are different tables, does the naming convention of the tables and field follow a pattern?
    If they are from the same table, what field are you using to determine the records that you want to combine the text fields of?

    Steve

  7. #7
    Join Date
    Aug 2004
    Posts
    7

    Concatenate text - STTL Query

    1. Are you able to determine how many of these records you have for any particular time you want to combine them into one string?
    >>Could be anywhere from zero to maybe 10 for any of 500+ line items

    2. Are the records in the same table or do they all come to you in different tables?
    >>all in the same table

    If they are different tables, does the naming convention of the tables and field follow a pattern?
    >> n/a, thank goodness

    If they are from the same table, what field are you using to determine the records that you want to combine the text fields of?
    >> in this case, initially just the account number.

    Thanks

Posting Permissions

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