Results 1 to 4 of 4
  1. #1
    klnlsu is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Apr 2015
    Posts
    107

    changing structure of data

    I would like to convert this table data:
    record1 salesperson1 code1 percent1 code2 percent2 code3 percent3
    record2 salesperson2 code1 percent1 code2 percent2 code3 percent3
    record3 salesperson3 code1 percent1 code2 percent2 code3 percent3
    record4 salesperson4 code1 percent1 code2 percent2 code3 percent3

    Into this:
    record1 salesperson1 code1 percent1
    record2 salesperson1 code2 percent2
    record3 salesperson1 code3 percent3
    record4 salesperson2 code1 percent1
    record5 salesperson2 code2 percent2
    record6 salesperson2 code3 percent3
    record7 salesperson3 code1 percent1
    record8 salesperson3 code2 percent2
    record9 salesperson3 code3 percent3
    record10 salesperson4 code1 percent1
    record11 salesperson4 code2 percent2
    record12 salesperson4 code3 percent3



    I would prefer to use a query that I can then manipulate with VBA code. Can someone help please?

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Assuming your original table is consistent in the number of columns, you would need several queries (one for each column pair) which can be combined into one using a union query

    the basic query would be something like

    SELECT Salesperson, Code1, percent1
    FROM myTable

    and combined

    SELECT Salesperson, Code1, percent1
    FROM myTable
    UNION SELECT Salesperson, Code2, percent2
    FROM myTable
    UNION SELECT Salesperson, Code3, percent3
    FROM myTable

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Tell us about the business this proposed database would support in plain English.

    In general, along these lines --but you know the details -

    We are a retail org with several salesman. Depending on the type of Sale (code) the commission percentage varies.

    You seem to be dealing with (best guess)

    Salesman
    Sale
    Product/Service
    SaleType
    CommissionPercentage

  4. #4
    klnlsu is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Apr 2015
    Posts
    107
    Thank you, Ajax. I have used that method in the past, and I will use it in this instance. I thought maybe there might be another, more efficient way. I will consider this resolved.

    Thanks again!

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

Similar Threads

  1. Replies: 4
    Last Post: 03-25-2015, 04:46 PM
  2. XML Export OLEObject/Image Data Structure
    By duckwilliamson in forum Import/Export Data
    Replies: 0
    Last Post: 10-11-2012, 07:45 PM
  3. Possible outline structure for this data is required
    By Resorte in forum Database Design
    Replies: 3
    Last Post: 08-03-2012, 12:21 PM
  4. Normalized data structure denormalized for data entry.
    By elsuwi in forum Database Design
    Replies: 3
    Last Post: 06-09-2012, 09:53 PM
  5. Replies: 3
    Last Post: 04-27-2009, 02:29 PM

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