Printing Detail Record Fields horizontally 1

[Jorgandr]

I need to build a report from a query that has 3 tables joined, however I would like the 2nd level grouping to go accross the top instead of down. ex.

Table 1
-------
Contract #
AffiliateCode
CustFName
CustLName
Loan #


Table 2
-------
ExceptionID
ExceptionCode
ExceptionDesc
ExceptionType

Table 3
-------
ConExceptionID
ExceptionID <-- Foreign Key
Contract# <-- Foreign Key


So the query I use the report has these 3 tables joined. What I want to do is have the report group it by Affiliate and then by a specific ExceptionType. So there would be 5 items for ExceptionType &quot;FireIns&quot; that I would like to display the Contract # if they have a &quot;FireIns&quot; exception. They can have more than 1 &quot;FireIns&quot; Exception but the way that it is set up, it will automatically make it so the reports reads as such

Affiliate: FKNB
Contract # CustName Loan# Exc Item Exc Type
123456 John Doe 9999999 Application FireIns
Binder FireIns
Missing FireIns


What I'd rather have is

Affiliate: FKNB
Contract # CustName Loan# Exc Item for &quot;FireIns&quot;
123456 John Doe 9999999 Application Binder Missing

Does anyone know of a way to accomplish this? Thanks

 

[GShen]

I do not see why you couldn't create a subreport for the lines you want going across and make it a 3 column report, going across and then down. So basically your main report will be on the left and your subreport will be on the right.

 

[jiqjaq]

Yes, this can be accomplished witha little bit of code.

1) Group the report as you mention. Be sure you have a GroupHeader and a GroupFooter for the ExceptionType.

2) The labels for data fields from the Contract# line need to go in the GroupHeader; the actual data fields need to go in the GroupFooter, not the Detail section. The Detail section needs to be not visible and must have the ExceptionDesc field as a control.


3) The textbox for the ExcItem must be unbound (no DataSource). Name it something that make sense like txtExc.

4) At the module level, create a string variable, mstrExc.

5) In the code for the Detail Format section, add the line:
mstrExc = mstrExc & &quot;, &quot; & [ExceptionDesc] Be sure to use whatever you called the field in the Detail section within those brackets.

6) In the code for the GroupFooter Format section, add: txtExc = MID(mstrExc,2) This will put the conctenated result of the grouping into the data field and lop off the leading &quot;, &quot; ; you should end up with Application, Binder, Missing

7) In the code for the GroupFooter Print section, add: mstrExc = &quot;&quot; . This will clear out the variable for the next Contract.

That should do it.