Summarize columns where a value is entered

[hbrems]

Dear all,

I'm trying to make something that looks like this:

PRODUCTS
--------------------------------------------------------------
| fldCode | fldItem1 | fldItem2 | fldItem3 | ... | fldItem50 |
--------------------------------------------------------------
| ARA010  |    1     |          |          |     |     4     |
| ARB011  |          |      2   |     2    |     |           |
--------------------------------------------------------------

into this:

PRODUCTS
-------------------------------
| fldCode | Items     | Count |
-------------------------------
| ARA010  | fldItem1  |   1   |
| ARA010  | fldItem50 |   4   |
| ARB011  | fldItem2  |   2   |
| ARB011  | fldItem3  |   2   |
-------------------------------

The database I have to work with is not properly normalized, I can't change that unfortunately. Normally ITEMS would have a many-to-many relationship with PRODUCTS.

I have somewhat of a solution which uses VBA to:
- dlookup the record with fldCode value (fldCode = PK)
- loop through the columns and build a string that contains column names that are not empty

SELECT p.Code, GetDetails(nz(p.Code, ""))
FROM Products p
IN "C:\MyDB.mdb"

Returns this:

---------------------------------
| fldCode | Items               |
---------------------------------
| ARA010  | fldItem1; fldItem50 |
| ARA011  | fldItem2; fldItem3  |
---------------------------------

I'm wondering if either format can be accomplished with pure SQL (maybe multiple queries). One of the major problems is that I need to retrieve the field names from the PRODUCTS table. However, if it helps I can create a new table with all the existing field names (they are static anyway).

Any help is much appreciated.

 

[DCrake]

I suspect you need to go down the Union query route.

Select Field1 As Fld, "Field1" As fName From Table

Union Select All Field2 , "Field2" From Table

.....


Union Select All Field50 , "Field50" From Table

This will transpose the data vertically

 

[hbrems]

I suspect you need to go down the Union query route.

Yep, this will provide the results I requested. Will let you know how fast this is when I'm finished with the query. It's 50 columns and 30000 rows...

 

[hbrems]

It takes 7 seconds, not bad.

 

[DCrake]

Not bad results, glad you resolved the problem.

 

[hbrems]

Now I'm trying to shape this:

-------------------------------
| fldCode | Items     | Count |
-------------------------------
| ARA010  | fldItem1  |   1   |
| ARA010  | fldItem50 |   4   |
| ARB011  | fldItem2  |   2   |
| ARB011  | fldItem3  |   2   |
-------------------------------

Into this:

---------------------------------
| fldCode | Items               |
---------------------------------
| ARA010  | fldItem1; fldItem50 |
| ARA011  | fldItem2; fldItem3  |
---------------------------------

I was thinking of something like a subquery where you would concatenate the results. But I don't think such a function is available in Access SQL?

SELECT Products.Code, (SELECT * FROM ProductItems pi WHERE pi.Code = p.Code)
FROM Primary_Transformants p

 

[hbrems]

Solved this in VBA. The code will open 2 record sets and copy related row values from 1 rs as a concatenated field value in the second recordset:

Public Function GetItems() As String

' Connection variables
Dim cn As ADODB.Connection
Dim rsTempItemSummary As ADODB.Recordset
Dim rsTempAllData As ADODB.Recordset
Dim sql As String

' Method variables
Dim code As String
Dim items As String

' Debug variables
Dim d As Date

On Error GoTo ErrorHandler
    
    ' Start timer
    d = DateTime.Now()
    
    ' Get Records
    Set cn = CurrentProject.Connection
    Set rsTempItemSummary = New ADODB.Recordset
    Set rsTempAllData = New ADODB.Recordset
    sql = "SELECT Code, Item FROM TempItemSummary ORDER BY Code"
    rsTempItemSummary.Open sql, cn, adOpenForwardOnly, adLockReadOnly
    sql = "SELECT Code, Items FROM TempAllData ORDER BY Code"
    rsTempAllData.Open sql, cn, adOpenKeyset, adLockOptimistic
    
    ' Process records
    If Not rsTempItemSummary.BOF And Not rsTempItemSummary.EOF Then
        code = rsTempItemSummary.Fields(0)
        Items = ""
        Do While Not rsTempItemSummary.EOF
            If code = rsTempItemSummary.Fields(0) Then
                Items = Items & rsTempItemSummary.Fields(1) & ";"
                rsTempItemSummary.MoveNext
            Else
                rsTempAllData.Find ("Code = #" & code & "#")
                rsTempAllData.Fields(1) = Items
                Items = ""
                code = rsTempItemSummary.Fields(0)
            End If
        Loop
    End If
    
    rsTempAllData.UpdateBatch
    
    ' Return results
    GetItems = CStr(DateDiff("s", d, DateTime.Now))
    
    ' Clean up
    rsTempItemSummary.Close
    rsTempAllData.Close
    Set rsTempItemSummary = Nothing
    Set rsTempAllData = Nothing
    Set cn = Nothing

Exit Function

ErrorHandler:
    GetItems = "Something went wrong!!!"

End Function