Results 1 to 1 of 1
  1. #1
    Join Date
    Jul 2012
    Posts
    3

    Unanswered: String Concatination, SUM(), Access 2010 Standard Query and possible subquery

    I have been searching all day trying to find an answer to what almost seems to be quite simple. I am using Access 2010 standard SQL and have two tables:

    1. Engagement
    2. Event

    (See Picture "Relationships" for more details)

    Code:
    SELECT 
       EV.en_ID, 
       EN.en_PeriodEnded, 
    'A:' & EN.en_BidAudit & ' R:' & EN.en_BidReview & ' T:' & EN.en_BidTax & ' O:' & EN.en_BidOther AS Proposed_Bids, 
       EV.ev_Type, 
       EV.ev_Amount,
       EN.en_Status  
    FROM Event AS EV 
        INNER JOIN Engagement AS EN ON EN.en_ID=EV.EN_ID 
    WHERE ev_Amount IS NOT NULL;
    I would like to have columns en_ID, en_PeriodEnded, en_Status, Proposed_Bids simply only display their value on one row.

    ev_Amount I would like to be summed based off of the grouping en_ID

    And finally, the most complicated would be to concatinate the String values contained in each row of ev_Type also grouped on en_ID, but not limited to three rows. I would want it to be capable of handling all events where ev_Amount is not null.

    Here is a visual to what I am looking for-

    en_ID | ev_Type | Proposed_Bids | en_PeriodEnded | ev_Amount | en_Status
    5258 | Audit, Tax,.. | A:1,500...| 12/31/2012 | $1,450 | 3-Engaged
    5259 | Audit, Tax... | A:1,500...| 12/31/2013 | $1,450 | 3-Engaged

    I feel like this should be easy, but I've been trying this all day now and thought the experts on this forum may be able to put me on the right path. I have also included a screenshot of the results I am getting from the query above.

    Thank you in advance for your time and assistance.
    Attached Thumbnails Attached Thumbnails 2012.7.17 Query Results.bmp   2012.7.17 Relationships.bmp  

Posting Permissions

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