Looking for an efficient way to build a report

[Shawn29316]

Hi,

Please see the attached file, which tells a big part of my story. The numbers represent the number of folks who answered "Y" to each question, based on race and gender. There could be thousands of lines of data as well as a few more race/gender combinations.

I'm looking for an efficient way to get from the data example to the report. I know I can create a table that looks like the report example the hard way (an update query for every race/gender combination). From there I can build the report but there has to be a better way.

I need help with either an easier way to get the data in a format similar to the report example or to skip the whole reformat step and go straight to the report. I can imagine the first could be done but, although the second would be preferable, I'm not smart enough to know if it's even doable.

I'd appreciate any help you could offer!

Thanks,
Shawn

[PhilOfWalton]

As I couldn't open your file, I have no idea what you want. Perhaps if you tried zipping it and re-sending someone might be able to help.

Phil

[Oralloy]

Shawn29316,

Is there any reason that you need to execute an update query?

What prevents you from building the required view of your database, and then use that as the basis of your report?

Alternately, as a view is just a (sometimes updatable) query, you can just build your query as a stored query and base the report on that.

Or, am I missing something obvious?

Cheers,
Oralloy

[Shawn29316]

Oralloy,

I noticed by your name there's an "expert" tag. That obviously doesn't describe me so there may be a simple way to do this which I just don't know.

My logic for the queries was to use one to create a table containing the question number and a blank field for each race/gender combination. Then there would be an update query for each column to add the data for each race/gender combination.

Thanks for you willingness to help!

Shawn

[PhilOfWalton]

I think this might help.

Table 6 is made by copying your Excel file into a new table.

table 7 must be defined exactly as this image


Then use the following code

Option Compare Database
Option Explicit

Function ChangeLayout()

    Dim MyDb As Database
    Dim InSet As Recordset, OutSet As Recordset
    Dim i As Integer
    Dim TestInt As Integer
    Dim FieldValue As Variant
    Dim FieldName As String
    
    On Error GoTo ChangeLayout_Err
    
    Set MyDb = CurrentDb
    Set InSet = MyDb.OpenRecordset("SELECT Table6.* FROM Table6;")
    Set OutSet = MyDb.OpenRecordset("SELECT Table7.* FROM Table7;")
    
    With InSet
        Do Until .EOF
            OutSet.AddNew
            For i = 1 To .Fields.Count - 1      ' ignore first field (ID in new table)
                FieldValue = .Fields(i)
                If Left(FieldValue, 8) = "Question" Then
                    FieldName = "Question"
                    FieldValue = Mid(.Fields(i), 10)    ' Number after question
                Else
                    FieldValue = .Fields(i)
                    OutSet.MoveLast
                    OutSet.Edit
                End If
                TestInt = CInt(FieldValue)              ' Gives an error for the field name fields
                OutSet.Fields(FieldName) = Nz(FieldValue)
                OutSet.Update
NextField:
            Next i
            .MoveNext
            OutSet.Requery
        Loop
        .Close
        Set InSet = Nothing
    End With
    
    
ChangeLayout_Exit:
    Exit Function
    
ChangeLayout_Err:
    If Err = 13 Then                    ' Failed to convert text to number
        FieldName = FieldValue
        Resume NextField
    ElseIf Err = 3020 Then              ' No value
        Resume NextField
    Else
        MsgBox Err & " " & Err.Description
    End If
    
End Function

Phil

[Oralloy]

Shawn29316,

Does PhilOfWalton's example get you off of the ground?

Oralloy