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

[Robeen]

Hi!

Is there an easy way [in a query or a series of queries?]
to display this:

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:

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

[ketbdnetbp]

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

[tanvi]

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

[Robeen]

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!

[Robeen]

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

Branch - Location: [Branch] & ": " & [Location] & "."

into one field, then had

[Officer]

as the second field, and concatenated

#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!!