Organizing records in Detail section into different columns 1

[leicklda]

I am having some report formatting trouble.

My fields are as such:
HouseID Option Name Option Type
34 Sunroom Structural
34 Loft Structural
34 Side Entry Garage
34 Service Door Garage
34 Dining Bay Bay Windows

In my report, I want to list these options each in a different column according to type. So one column for Structural, one for garage, etc... I can do this with different textbox controls that each have an if statement as their source: iif([Option type]="Structural",[option type],null).

This works, but there are horizontal gaps between records in each column because each option type actually is a new record and so it goes on a new line.

My data ends up looking like this:

Structural Bay Windows Garage
*********************************************************
Sunroom
Side Entry
Loft
Dining Bay
Service Door


How can I "zip up" these columns? Should I use unbound listboxes? I tried that and it didn't work for a couple reasons: listboxes do not have a CanGrow or CanShrink property, and also they just wouldn't show anything. The recordsource for them was
"SELECT OptNames.Option, OptNames.Type, OptCommunityPlans.fkPlanID
FROM OptNames
RIGHT JOIN OptCommunityPlans ON OptNames.ID=OptCommunityPlans.OptID
WHERE (((OptNames.Type)=2) AND ((OptCommunityPlans.fkPlanID) Like [id]));"

Does anyone know how I can accomplish this?

Thank you in advance.

 

[hneal98]

You can't do it in one query. You will need as many queries as you have option names. you will need to create a table to hold the data and then for each of the option names, you will need to create an update query that will update the option that has not been added already.

That should be enough to get you thinking. Let us know if you still can't figure it out.

 

[dhookom]

You could create a crosstab and use it with the concatenate function found at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

This assumes a table name of tblHouseOptions and field names with no spaces. This solution will combine options with a ", " delimiter.


TRANSFORM First(Concatenate("Select OptionName from tblHouseOptions WHERE HouseID=" & [HouseID] & " AND OptionType=""" & [OptionType] & """")) AS Expr1
SELECT tblHouseOptions.HouseID
FROM tblHouseOptions
GROUP BY tblHouseOptions.HouseID
PIVOT tblHouseOptions.OptionType;
[tt]
HouseID Bay Windows Garage Structural
34 Dining Bay Side Entry, Service Door Sunroom, Loft
[/tt]

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[hneal98]

Yes, that is correct. I forgot about crosstabs. Thanks dhookom.

 

[leicklda]

Thank you both for your suggestions!

Duane - you are brilliant and you saved my butt.

I ended up using your concatenate function (the one that separates "records" with a line break within the field)with the crosstab and it works great.

Your function ended up being:

Function Concatenate(pstrSQL As String, Optional pstrDelim As String = ", ") As String

Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset(pstrSQL)

Dim strConcat As String 'build return string
With rst
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rst = Nothing
Set db = Nothing
Concatenate = strConcat
End Function

One last question - is there a difference between your "if not EOF..." statement and the Do Until loop? Just wondering what the logic was for not using Do Until EOF.

Thanks again!

 

[leicklda]

Also I wanted to add that even if had seen this cool concatentate function, I probably wouldn't have thought to put it in a crosstab to accomplish what I needed to do. The crosstab and conc. function work great together. I learned a lot from this exercise.

The query field:
OptionNames: Concatenate("SELECT OptionName FROM qryOptTypes WHERE (fkCommPlanID = " & [CommPlanID] & " AND typeid = " & [typeid] & ")",Chr(13) & Chr(10))

was just what I needed

Thanks again.

 

[dhookom]

I use the code because this is what I have always used and it always works

Glad you were able to use my Concatenate(). I wrote it years ago and it has been of more value to others than to me so it's good to hear it has value to others.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.