Results 1 to 9 of 9
  1. #1
    emaichen is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    3

    Using information from existing column to define new column


    Hi
    I'm using MS Access 2010.
    I want to convert the table 1 (which is the input table in table format) into a new table 2 (output table) using an access query.
    Looking for help on this.
    Thanks
    Click image for larger version. 

Name:	Capture.JPG 
Views:	15 
Size:	31.5 KB 
ID:	25618

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Have you tried using the Wizard to create a Crosstab Query?
    https://www.youtube.com/watch?v=RVFgjMDeGaw

  3. #3
    emaichen is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    3
    Hi ItsMe
    Thanks, and its useful when you're dealing with one item.
    I'm actually trying to do this for multiple items. I give a slightly modified example to clarify.
    Lets say we have table 1 and I want the information for only a specific country (USA) in a modified format.
    Is there a way this can be done through a query.
    Thanks

    Click image for larger version. 

Name:	Capture.JPG 
Views:	13 
Size:	49.4 KB 
ID:	25621

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    I do not work with crosstab queries often. I believe that there are limitations to their uses. Usually the question is how to do the opposite of what you are trying to accomplish. In those instances, a UNION query may be helpful.

    If I was asked to present the data the way you are describing, I would look to reports and or forms. Another thing that comes to mind are Charts. At the end of the day, if someone demanded it look exactly like such and such, I might export the data to an external Excel file and then display it in a form via an OLE control. However, you will likely be able to do a similar thing using a subform and datasheet view.

  5. #5
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    crosstabs can only return one column/value so you need two crosstabs (one for revenue and one for qty). Then a third to combine the two and joining on client

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    I was wondering about that. So, it is possible to join two crosstab queries?

  7. #7
    emaichen is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    3
    Thanks Ajax & ItsMe

    I ended up creating crosstab queries for each variable (7 in my case). I was able to apply the country filter within each crosstab (I only needed one but it allows multiple filters).

    I then joined them all with an 8th table using another query.

    A bit tedious (which makes me think there must be a more efficient way, specially when more variables are involved) but dies the job for me.

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    join two crosstabs (or more) - yes - looks like OP has done that

    re alternative ways - a bit tedious to create perhaps but relatively efficient and easy to maintain.

    - you could use a pivot table, but that has limited presentation options.
    - you could use one query with multiple subqueries (one for each column of final result) but would suggest equally tedious to create and perhaps not as efficient and would need modification for new columns
    - create a vba function to return a recordset by looping- more complex, less tedious

  9. #9
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Yeah, each time I looked at it, I thought VBA to myself. I also thought pivot table, but I think nowadays that involves using Excel. IIRC, Access is not using that control anymore and Excel has new features for Pivot. So maybe OLE to display Excel or export to a Read Only spreadsheet.

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

Similar Threads

  1. Replies: 2
    Last Post: 02-05-2015, 03:51 PM
  2. Insert Column between existing columns?
    By JC8865 in forum Access
    Replies: 2
    Last Post: 12-06-2014, 09:05 PM
  3. Auto increment on existing number column
    By lcsgeek in forum Programming
    Replies: 1
    Last Post: 04-15-2013, 11:27 AM
  4. Replies: 7
    Last Post: 02-06-2012, 11:54 AM
  5. Replies: 3
    Last Post: 07-28-2011, 11:24 AM

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