Columns setting in report

[deepaksharma]

I have a main form in which a sub form is created. In the main form, customer details are entered and in the sub form, shades and meters related to the goods ordered by the customer are entered in 2 fields.While preparing the report of this form, I have set the sub forms of shades and meters in the column option 3 column located in the page setup tab.



I want that when the report is generated, the heading of shades and meters should be shown before each line of shades and meters. How can this be set? Please see the attachment.

Thank you..

[Welshgasman]

You put labels in the detail band.?
Perhaps look on YouTube. Plenty of Access tutorials there on most aspects of Access.

[deepaksharma]

You put labels in the detail band.?
Perhaps look on YouTube. Plenty of Access tutorials there on most aspects of Access.

Yes, I added the label but the problem was that the label started getting shown in all three columns whereas I want it to be highlighted only in the first column.

[June7]

Usually, fields would be horizontal with corresponding labels in header section which would mean 3 sets of labels to align with 3 column pairs.

Your arrangement calling for vertical labels at left is unorthodox and there is no setting to create. A solution would most likely involve VBA and a temp table.

[deepaksharma]

Usually, fields would be horizontal with corresponding labels in header section which would mean 3 sets of labels to align with 3 column pairs.

Your arrangement calling for vertical labels at left is unorthodox and there is no setting to create. A solution would most likely involve VBA and a temp table.

thanks can you suggest any code for this

[June7]

This is one approach.

Source table:

ID	Shade	Meter
1	red	200
2	black	100
3	milky orange	200
4	white blue	50
5	light black	400
6	sky blue	200
7	red	200
8	him white	300
9	light brown	500

A table named TableTEMP with fields: RowNum, Label, Col1, Col2, Col3.

Code:

Sub DataToThreeColumns()
Dim db As DAO.Database, rs As DAO.Recordset
Dim x As Integer, r As Integer
Dim s1 As String, s2 As String, s3 As String
Set db = CurrentDb
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Table1")
r = 1
CurrentDb.Execute "DELETE FROM TableTEMP"
Do While Not rs.EOF
    For x = 1 To 3
        If x = 1 Then s1 = rs!Shade & vbCrLf & rs!Meter
        If x = 2 Then s2 = rs!Shade & vbCrLf & rs!Meter
        If x = 3 Then s3 = rs!Shade & vbCrLf & rs!Meter
        rs.MoveNext
    Next
    db.Execute "INSERT INTO TableTEMP(RowNum, Label, Col1, Col2, Col3) " & _
        "VALUES(" & r & ",'Shade' & Chr(13) & Chr(10) & 'Meter','" & s1 & "','" & s2 & "','" & s3 & "')"
    r = r + 1
Loop
End Sub

[June7]

And I played around with a non-VBA solution. Consider:

Same source data.

Query1:
SELECT ID, "Shade" & Chr(13) & Chr(10) & "Meter" AS Label,
"Col" & IIf([ID] Mod 3=0,3,IIf([ID] Mod 2=0,2,1)) AS Col,
[Shade] & Chr(13) & Chr(10) & [Meter] AS Data
FROM Table1;

Query2:
TRANSFORM First(Query1.Data) AS FirstOfData
SELECT DCount("*","Query1","Col='" & [Col] & "' AND ID<=" & [ID]) AS GrpSeq, Query1.Label
FROM Query1
GROUP BY DCount("*","Query1","Col='" & [Col] & "' AND ID<=" & [ID]), Query1.Label
PIVOT Query1.Col;

Unfortunately, I expect DCount() to cause slow performance in large dataset. Row numbering can be done with nested subquery but might not be any faster http://allenbrowne.com/ranking.html
Also, gaps in ID sequence will likely cause uneven columns.

[deepaksharma]

This is one approach.

Source table:

ID	Shade	Meter
1	red	200
2	black	100
3	milky orange	200
4	white blue	50
5	light black	400
6	sky blue	200
7	red	200
8	him white	300
9	light brown	500

A table named TableTEMP with fields: RowNum, Label, Col1, Col2, Col3.

Code:

Sub DataToThreeColumns()
Dim db As DAO.Database, rs As DAO.Recordset
Dim x As Integer, r As Integer
Dim s1 As String, s2 As String, s3 As String
Set db = CurrentDb
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Table1")
r = 1
CurrentDb.Execute "DELETE FROM TableTEMP"
Do While Not rs.EOF
    For x = 1 To 3
        If x = 1 Then s1 = rs!Shade & vbCrLf & rs!Meter
        If x = 2 Then s2 = rs!Shade & vbCrLf & rs!Meter
        If x = 3 Then s3 = rs!Shade & vbCrLf & rs!Meter
        rs.MoveNext
    Next
    db.Execute "INSERT INTO TableTEMP(RowNum, Label, Col1, Col2, Col3) " & _
        "VALUES(" & r & ",'Shade' & Chr(13) & Chr(10) & 'Meter','" & s1 & "','" & s2 & "','" & s3 & "')"
    r = r + 1
Loop
End Sub

I tried this code many times in my report but I failed every time so I am attaching my file. Please if you adjust this code in it, it will be very kind of you.
The report in this file is named report2.
Thank you.

[June7]

Code needs to be in a general module, not behind report.

Report would NOT use multi-Columns settings.

SQL is missing FROM keyword.

There is no TableTEMP. Might want to use name GreyTEMP. If this will be a multi-user split db, GreyTEMP must be in frontend. Some developers prefer to create and delete table each time procedure runs instead of just deleting records.

Probably need to include OfferID in GreyTEMP records so can join to Grey_Offer instead of using Grey_Shade table. Or use GreyTEMP in a linked subreport since you need Grey_Shade data for Sum() calc.

Data shows only one Offer record but I assume there can be more. This will require adjustment in code to make sure correct OfferID is saved with record. Now that I have some realistic data, I find other issues with suggested code.

Label caption Shade & Meters needs to be Shade && Meters or use and instead of &.

Might need to fix report width.

What is this db for? Selvage is a term for fabric edge.

Revised code:

Sub DataToFourColumns()
Dim db As DAO.Database, rs As DAO.Recordset
Dim x As Integer, r As Integer
Dim s1 As String, s2 As String, s3 As String, s4 As String, intOID As Integer, strSM As String
Set db = CurrentDb
Set rs = CurrentDb.OpenRecordset("SELECT * FROM GREY_SHADE")
r = 1
CurrentDb.Execute "DELETE FROM GreyTEMP"
Do While Not rs.EOF
    intOID = rs!offerid
    For x = 1 To 4
        If Not rs.EOF Then
            strSM = rs!SHADE & vbCrLf & rs!METER
            If intOID = rs!offerid Then
                If x = 1 Then s1 = strSM
                If x = 2 Then s2 = strSM
                If x = 3 Then s3 = strSM
                If x = 4 Then s4 = strSM
                rs.MoveNext
            End If
        End If
    Next
    db.Execute "INSERT INTO GreyTEMP(RowNum, OfferID, Label, Col1, Col2, Col3, Col4) " & _
        "VALUES(" & r & "," & intOID & ",'Shade' & Chr(13) & Chr(10) & 'Meter','" & s1 & "','" & s2 & "','" & s3 & "','" & s4 & "')"
    r = r + 1
    s1 = ""
    s2 = ""
    s3 = ""
    s4 = ""
Loop
End Sub

[deepaksharma]

Code needs to be in a general module, not behind report.

Report would NOT use multi-Columns settings.

SQL is missing FROM keyword.

There is no TableTEMP. Might want to use name GreyTEMP. If this will be a multi-user split db, GreyTEMP must be in frontend. Some developers prefer to create and delete table each time procedure runs instead of just deleting records.

Probably need to include OfferID in GreyTEMP records so can join to Grey_Offer instead of using Grey_Shade table. Or use GreyTEMP in a linked subreport since you need Grey_Shade data for Sum() calc.

Data shows only one Offer record but I assume there can be more. This will require adjustment in code to make sure correct OfferID is saved with record. Now that I have some realistic data, I find other issues with suggested code.

Label caption Shade & Meters needs to be Shade && Meters or use and instead of &.

Might need to fix report width.

What is this db for? Selvage is a term for fabric edge.

Revised code:

Sub DataToFourColumns()
Dim db As DAO.Database, rs As DAO.Recordset
Dim x As Integer, r As Integer
Dim s1 As String, s2 As String, s3 As String, s4 As String, intOID As Integer, strSM As String
Set db = CurrentDb
Set rs = CurrentDb.OpenRecordset("SELECT * FROM GREY_SHADE")
r = 1
CurrentDb.Execute "DELETE FROM GreyTEMP"
Do While Not rs.EOF
    intOID = rs!offerid
    For x = 1 To 4
        If Not rs.EOF Then
            strSM = rs!SHADE & vbCrLf & rs!METER
            If intOID = rs!offerid Then
                If x = 1 Then s1 = strSM
                If x = 2 Then s2 = strSM
                If x = 3 Then s3 = strSM
                If x = 4 Then s4 = strSM
                rs.MoveNext
            End If
        End If
    Next
    db.Execute "INSERT INTO GreyTEMP(RowNum, OfferID, Label, Col1, Col2, Col3, Col4) " & _
        "VALUES(" & r & "," & intOID & ",'Shade' & Chr(13) & Chr(10) & 'Meter','" & s1 & "','" & s2 & "','" & s3 & "','" & s4 & "')"
    r = r + 1
    s1 = ""
    s2 = ""
    s3 = ""
    s4 = ""
Loop
End Sub

I am failing in every endeavor. This is very difficult, I wish you would modify this file.
So kind of you.

[June7]

1. create GreyTEMP table with fields: RowNum, OfferID, Label, Col1, Col2, Col3, Col4

2. change report design to NOT use multi-columns

3. use GreyTEMP as source for a subreport - place a subreport container on main report (try report Footer) and set SourceObject property to a form that has GreyTEMP as RecordSource then set Master/Child Links properties to OfferID - need to include OfferID in main report so these objects can be linked

4. put code in a general module or behind a form, not behind report

5. code in form button click event - I don't know where you want this

DataToFourColumns
DoCmd.OpenReport "Report1", acViewPreview

What is so difficult about those edits? Exactly what do you not understand?

[Micron]

Pulleeeze stop replying to every post with a quote of the entire post. It's rarely necessary. Use reply instead of reply with quote. If you must quote from a lengthy post, copy what you need to quote and paste within quote tags.

[deepaksharma]

thank you micron

[deepaksharma]

1. create GreyTEMP table with fields: RowNum, OfferID, Label, Col1, Col2, Col3, Col4

i have made changes as far i understand this point, please check it

[deepaksharma]

Another way comes to my mind, suppose there are total 5 columns in the report and the label of shed and meter is being shown in all the five columns, then can there be such coding of if condition in the report load that Labels show in black color in the first column and in white color in other columns due to which those labels will disappear in other columns.