+ Reply to Thread
Results 1 to 5 of 5

Access Table Query to Combine Records

  1. #1
    Registered User
    Join Date
    12-12-2011
    Location
    Collegeville, PA
    MS-Off Ver
    Excel 2007
    Posts
    14

    Access Table Query to Combine Records

    Is there a way to query this table back to combine records that have like accounts and like cusips?

    For example records 1 and 2 have the same account and cusip...can i put them in the same line (Account - CUSIP - AMOUNT L - AMOUNT S - DIFFERENCE)? Also PH9217 does not have an offset...i would just have AMOUNT S be 0. L stands for Ledger S for Statement.

    ID account cusip amount L/S
    1 PH4056 123456789 500 L
    2 PH4056 123456789 600 S
    3 PH4055 676767677 1000 L
    4 PH4055 676767677 1000 S
    5 PH9217 123456789 500.01 L

  2. #2
    Registered User
    Join Date
    12-12-2011
    Location
    Collegeville, PA
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Access Table Query to Combine Records

    any ideas?

    the only way i was thinking is maybe query out Ledger items and statement items into 2 different tables and then union them together.

  3. #3
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019; O365
    Posts
    19,161

    Re: Access Table Query to Combine Records

    I was able to do a self join on the table and get all that had both items but could not get the item 5 to list. Give your idea a shot and let us know if it works or doesn't.

  4. #4
    Registered User
    Join Date
    12-12-2011
    Location
    Collegeville, PA
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Access Table Query to Combine Records

    do you think you could show me your query you created? i tried something i thought might work but was unable to get them on the same line.

  5. #5
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019; O365
    Posts
    19,161

    Re: Access Table Query to Combine Records

    Please Login or Register  to view this content.
    Alan

  6. #6
    Registered User
    Join Date
    12-12-2011
    Location
    Collegeville, PA
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Access Table Query to Combine Records

    This seems to be almost perfect...i think the join has something to do with why item 5 won't show up...bc the query is looking for accounts that are the same. anyone know how i might change this query to show item 5 even if it doesn't have a corresponding "s" amount? this is the result i get using
    alan's query.

    account cusip Sheet1.amount Sheet1.L/S Sheet1_1.amount Sheet1_1.L/S
    PH4055 676767677 1000 L 1000 S
    PH4056 123456789 500 L 600 S

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1