Results 1 to 5 of 5
  1. #1
    Join Date
    May 2011

    Unanswered: Combine Data from One Table into Multiple Columns of Another

    Need some serious direction here please.

    I have one table with People and their various data (Address, City, etc.) and 4 blank columns at the end Transaction 1, Transaction 2, etc..

    I have another table with Transactions (date, description, etc.).

    Each person from the People table has a PeopleID, each Transaction has a PeopleID from the People Table in it.

    I want to run a Query that copies the Transaction_Description into a Column for each time it sees a matching PeopleID.


    Table 1
    5555, John Doe, Doe Street, Anywhere

    Table 2
    1, 5555, Oct_Purchase
    2, 5555, Nov_Purchase

    PeopleID, Name, Address, City, Transaction Desc. 1, Transaction Desc. 2
    5555, John Doe, Doe Street, Anywhere, Oct_Purchase, Nov_Purchase

  2. #2
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Surrey, UK
    Provided Answers: 18
    What you're asking breaks data normalisation, and you generally won't get many people here offering to help you do that.

    Why do you need this? What are you trying to achieve? How do you know that no-one will ever have more than four transactions? If you answer these questions, perhaps we might be able to offer a solution.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  3. #3
    Join Date
    Dec 2008
    I also do not understand why you need this, but if you need it I respect that fact.
    I think that you can accomplish this with creating a pivot query. So instead having 1,2,3 as a value in your Table 2 use "Transaction Description 1", "Transaction Description 2", "Transaction Description 3" and then make query that will transform the table into pivot query (read more at Pivot Query in Access)
    And then you can join the table 1 and pivot query to display all the data in a single recordset.

    I hope this helps.


  4. #4
    Join Date
    May 2011
    I'll look into the Pivot Query. It's a one time thing. Essentially I have 2 tables and want to export an single spreadsheet to use as an import file for another database. Their are never more than 3 transactions technically, but I created a 4th column just in case.

  5. #5
    Join Date
    Oct 2009
    The crosstab feature - whether query or report - should provide you what you need. Presuming you have an Access textbook, easily found at Amazon or any big book store - - they always show a step by step instruction on setting up a cross tab.

    hope it helps.
    www CahabaData com

Tags for this Thread

Posting Permissions

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