Results 1 to 5 of 5
  1. #1
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596

    Display results for each Group 'horizontally' rather than 'vertically'.

    Hi!

    Is there an easy way [in a query or a series of queries?]
    to display this:
    Code:
    Branch   Officer       Total Loans
    1           ABC        $5,678,555
    1           DEF        $6,678,555
    2           GHI        $7,678,555
    2           JKL        $8,678,555
    2           MNO        $9,678,555
    Like this:
    Code:
    Branch   Officer1    TotalLoans1          Officer2     TotalLoans2    Officer3   TotalLoans3  . . .
    1           ABC        $5,678,555           DEF        $6,678,555
    2           GHI        $7,678,555           JKL        $8,678,555       MNO      $9,678,555
    There is an indeterminate and ever-changing possible-number-of-Officers per Branch.

    If this is not possible using queries, is there a better way than writing code to loop through the recordset to get the desired output?
    I've done it before where I've written output to text and also to a table using VBA & recordset - but I'd like to know if there is a way I can get Access to do the 'heavy lifting'.



    Appreciate any input!

    Robeen

  2. #2
    ketbdnetbp is offline Competent Performer
    Windows 7 32bit Access 2003
    Join Date
    Mar 2011
    Location
    Midwest
    Posts
    254
    Robeen - Don't know if it will help in your particular situation but, you might check out (google) the SQL Transpose Method.

    All the best,

    Jim

  3. #3
    tanvi is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    100
    You can do it by Crosstab Query. Check this link for detailed information about Crosstab Query: http://office.microsoft.com/en-us/ac...010341832.aspx

  4. #4
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    Thanks, guys!

    I researched / played with the Transpose method but was only able to invert the rows and columns so that the column headers moved to the left and the data for each column showed from left to right. I'm not sure if I didn't follow through enough with it.
    I'm glad to learn about that, though! It might come in handy one of these days!

    The crosstab is looking more promising and will definitely work for the three fields I put in my example.
    Since my actual data has a total of 6 fields [I should have taken the trouble to give an exact replica of the data structure rather than an abbreviated version] - I am looking through the microsoft link Tanvi provided to see if it will get the whole thing done for me.

    My actual data is structured like this:
    Branch Location Officer # Of Loans Amount AvgInterest
    1 CityA ABC 200 1,500,798 6.5
    1 CityA DEF 300 2,500,798 6.6
    2 CityB GHI 400 3,500,798 6.7
    2 CityB JKL 500 4,500,798 6.8
    2 CityB MNO 600 5,500,798 6.9

    While I'm looking through the Microsoft site, if you know that it will not be possible to get crosstab to work for the above, please let me know.

    Thanks again! I appreciate the suggestions!

  5. #5
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    Just wanted to let you know that I was able to get what I needed using a Crosstab Query.
    I have not HAD to use a crosstab ever before [though I have played with it] so I didn't consider that option.
    Thanks Tanvi.

    I created a query that concatenated
    Code:
    Branch - Location: [Branch] & ": " & [Location] & "."
    into one field, then had
    Code:
    [Officer]
    as the second field, and concatenated
    Code:
    #OfLoans - Amount - AvgInterest: #Of Loans & " - " & Format(Amount,"Currency") & " - " & AvgInterest
    as a third field.

    Then I created the crosstab query based on the above query.

    Worked like a charm! Boss is happy!!

    Thanks again!!

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

Similar Threads

  1. Group by pulling incorrect results
    By jpawson74 in forum Queries
    Replies: 7
    Last Post: 03-28-2012, 09:46 AM
  2. Display results with count of 0
    By jbickl in forum Reports
    Replies: 2
    Last Post: 02-06-2012, 07:07 AM
  3. Replies: 11
    Last Post: 10-04-2011, 02:29 AM
  4. Replies: 2
    Last Post: 01-31-2011, 05:17 PM
  5. Group query results
    By shak2 in forum Access
    Replies: 7
    Last Post: 10-20-2010, 02:41 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