Transpose Query

[sxschech]

Hello,

I have created a summary query that contains one row with 36 calculated cols. I would like to transpose this so that there are two Cols. The only way, so far that seems it might work is to do a make table and then create a union query with 36 select statements. I tried doing a union query directly off the select query, but after about 10 Union Selects, the query errored out "too many open tables". I then did a make table and went a bit further, but before I end up with 36 union selects and a possible encounter with another error message, thought I would check if anyone has an idea that might work more reasonably (additional reason for looking at other options is would like flexibility if field names need to change or if summary fields are added removed from source calculated query). See example:

How it looks
[tt]
Total Group1 Group2 AvgGroup1 AvgGroup2....
81 35 46 125.9 82.1


How it should look

Total 81
Group1 35
Group2 46
AvgGroup1 125.9
AvgGroup2 82.1
...
[/tt]

 

[dhookom]

It might help if we could see your original data/records/structure since your "How it looks" apparently is a query.

If your table is more like a spreadsheet, you may have to use several append queries to a temporary normalized table.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[sxschech]

The original data were in a table as a listing of records which I then summarized using the query. The query had many if statements in order to calculate the totals and averages based on different combinations of fields. Below is a sample layout. Field names changed for readability.
[tt]
DocNum CostIncr Cost Decr Type Group ... ...
34-AB9 50 0 I WXE ... ...
34-BQ2 0 25 I WXE
56-R9A 50 0 II CNA

--------------------------
Sample of Calc Results
--------------------------
Total 3
Total_WXE 2
Total_CNA 1 (See below for sample if statement)
Total_Cost_Incr 100
Total_Type_I_Cost_Incr 50
Total_Type_II_Cost_Incr 50
Total_WXE_Cost_Incr 50
Total_CNA_Cost_Incr 50
Total_Cost_Decr 25
...


(These are sample if statements that calculate the fields)
Total_CNA: (SELECT Count(*)
FROM qryLog
WHERE qryLog.Group = "CNA")

CNA_Cost_Incr: (SELECT Sum(qryLog.costincr)
FROM qryLog
WHERE qryLog.Group="CNA")
[/tt]

Additional calculated fields rely on the combination of several fields like summing up only those that are both CNA and Type I.

 

[coachdan]

If I understand you correct, you can select the data and copy. Open a new worksheet and do Paste Special/Transpose.

coachdan32

http://www.malehsbaseball.com

 

[sxschech]

That is exactly what I have been doing. However, my reason for asking about this is that I may need to hand this over to someone else and was hoping it could be a simple matter of telling them to click on this query to get the result rather than have to do some extra steps.

 

[MichaelRed]

Although I would recommend a more thorough study of your basic processes to avoid using such a procedure, the following will (within some limits) take a RecordSet and generate a new table with the traditional "Transposition".

Note that the transposition is done on the FIRST field in the source Table. While some may be able to modify the code to select an alternate column, I would recommend that most simply reartrange their source recordsets to place the desired piviot field first in the field sequence.

Public Function basCreXposeTbl(strSource As String, strTarget As String) As Long

    Dim dbs As DAO.Database
    Dim rstSrc As DAO.Recordset
    Dim tblNew As TableDef
    Dim fldNew As DAO.Field
    Dim MyNewFld As String
    Dim MyFld() As MyFldType
    Dim FldLen() As String

    Dim Idx As Long
    Dim Jdx As Long
    Dim Kdx As Long
    Dim MyFldLen As Integer


    On Error GoTo ErrExit

    Set dbs = CurrentDb()
    Set rstSrc = dbs.OpenRecordset(strSource)

    'Get a valid record count from the Source Table
    
    Idx = rstSrc.RecordCount
    ReDim MyFld(Idx)
    ReDim FldLen(Idx)
    rstSrc.MoveFirst

    'First field name Derived from the Field Name of Field(0)
    MyFld(0).FldName = basValdName(rstSrc.Fields(0).Name)

    'Remaiining field names from field(0) values
    Idx = 1
    While Not rstSrc.EOF

        MyFld(Idx).FldName = basValdName(rstSrc.Fields(0).Value)

        rstSrc.MoveNext
        Idx = Idx + 1
    Wend

    'Here with a supposed set of field names in an array.  Need to _
     check for dups and adjust to make sure there are none
    Idx = 0
    While Idx <= UBound(MyFld) - 1
        Kdx = 1

        Jdx = Idx + 1
        While Jdx <= UBound(MyFld) - 1

            If (MyFld(Jdx).FldName = MyFld(Idx).FldName) Then
                'Aparent Dup - so add a suffix
                MyFld(Jdx).FldName = MyFld(Jdx).FldName & "_" & Trim(Str(Kdx))
                Kdx = Kdx + 1
            End If

            Jdx = Jdx + 1
        Wend

        Idx = Idx + 1
    Wend

    'Here to get the field Types and (for Text fields) the Sizes
    Idx = 1
    rstSrc.MoveFirst
    While Not rstSrc.EOF
        MyFldLen = 1
        Jdx = 1

        While Jdx <= rstSrc.Fields.Count - 1

            If (Not IsNull(Len(Trim(rstSrc.Fields(Jdx))))) Then
                MyFldLen = Len(Trim(rstSrc.Fields(Jdx)))
                If (MyFldLen > MyFld(Idx).FldSize) Then
                    MyFld(Idx).FldSize = MyFldLen
                End If
            End If

            Jdx = Jdx + 1
        Wend

        Idx = Idx + 1
        rstSrc.MoveNext
    Wend

    ' Create a new table to hold the transposed data.
    Set tblNew = dbs.CreateTableDef(strTarget)

    'Create a field for each record in the original table, with _
     field names derived from the First Column of the Source Recordset.

    Idx = 0
    While Idx <= UBound(MyFld)

        With tblNew
            .Fields.Append .CreateField(MyFld(Idx).FldName, dbText, MyFld(Idx).FldSize)
        End With

        Idx = Idx + 1
   Wend

    'Actually Save the New table with the fields (all are text!)
    dbs.TableDefs.Append tblNew

    'Show the new table in the dbWindow
    RefreshDatabaseWindow

    Set rstSrc = Nothing
    Set dbs = Nothing

ErrExit:
    basCreXposeTbl = Err

End Function

MichaelRed

 

[sxschech]

Michael,
Thank you for your response. When I tried to compile, it errored on a couple of items. Do I need additional code or to modify in some way?
[tt]
Code Error Message
----------------------------------------
Dim MyFld() As MyFldType User defined type not defined

basValdName Sub or Function not defined

[/tt]
Do I need to change MyFldType to String or Integer or Date?
is basValdName a function that you created, but didn't include or do I need to set a reference?

 

[dhookom]

sxschech,
You must set a reference to the DAO object library in Tools->References.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[sxschech]

My database was originally set up with references for DAO when I first created the db about 9 months ago. It is using DAO 3.6 object library and appears before ADO 2.1 in the list of references. This db is in Access 2000 running on Win 2000.

 

[dhookom]

I was off regarding my response. Either there is an error in the code or MichaelRed isn't sharing other modules.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[MichaelRed]

I, too, was a bit hasty in the response. The entire module is more appropiate than the single routine:

Option Compare Database
Option Explicit

    Type MyFldType
        FldName As String
        FldType As String
        FldSize As Integer
    End Type
Function basXpose(strSource As String, Optional ByVal strTarget As Variant)

    Dim dbs As DAO.Database
    Dim rstSrc As DAO.Recordset
    Dim rstDest As DAO.Recordset
    Dim Idx As Integer
    Dim Jdx As Integer
    Dim Kdx As Integer
    Dim Ldx As Integer
    Dim MyStatus As Long
    Dim MyDest As String
    
    On Error GoTo ErrExit

    If (IsMissing(strTarget)) Then
        MyDest = strSource & "Xpose"
     Else
        MyDest = strTarget
    End If

    Set dbs = CurrentDb()
    Set rstSrc = dbs.OpenRecordset(strSource)

    'Seperate routine to create the table
    MyStatus = basCreXposeTbl(strSource, MyDest)
    If (MyStatus <> 0) Then
        MsgBox "Err(" & MyStatus & ") " & Error(Err), vbCritical, "File Creation Error"
        GoTo ErrExit
    End If

    'Open the New table, to populate it's fields with the source recordset records
    rstSrc.MoveFirst
    Set rstDest = dbs.OpenRecordset(MyDest, dbOpenDynaset)

    'Fill each Field of the tblNew (rstDest) _
     with a record from the recordset in the input argument (rstSrc).
    Idx = 1
    While Idx < rstSrc.Fields.Count         '(1 to 11)
        Jdx = 0
        rstSrc.MoveFirst
       'Begin with the second field, because the first field _
        already contains the field names.


        With rstDest
           .AddNew

            If (Jdx = 0) Then
                .Fields(Jdx) = rstSrc.Fields(Idx).Name
                Jdx = Jdx + 1
            End If

            Do While Jdx < rstDest.Fields.Count - 1         '(1 to 128)
                .Fields(Jdx) = Trim(rstSrc.Fields(Idx).Value)
                rstSrc.MoveNext
                If (rstSrc.EOF = True) Then
                   Exit Do
                End If
                Jdx = Jdx + 1
            Loop

            .Update
        End With

        Idx = Idx + 1

    Wend

    Set rstSrc = Nothing
    Set rstDest = Nothing
    dbs.Close

   Exit Function

ErrExit:

   Select Case Err

      Case 3010
         MsgBox "The table " & strTarget & " already exists."

      Case 3078
         MsgBox "The table " & strSource & " doesn't exist."

        Case 3191   'Camnnot define field more than once
         MsgBox "Camnnot define field more than once"

      Case Else
         MsgBox CStr(Err) & " " & Err.Description
   End Select

   Exit Function

End Function
Public Function basValdName(strIn As String) As String

    'To remove Invalid Characters and Spaces from "proposed" field Names

    Dim Idx As Long
    Dim Jdx As Long
    Dim blnVldChr As Boolean
    Dim MyChr As String * 1
    Dim strOut As String
    Dim InVldChrs(30) As String * 1
    Dim blnWhtSpc As Boolean

    InVldChrs(0) = "."
    InVldChrs(1) = "/"
    InVldChrs(2) = "\"
    InVldChrs(3) = "&"
    InVldChrs(4) = "^"
    InVldChrs(5) = "%"
    InVldChrs(6) = "*"
    InVldChrs(7) = "("
    InVldChrs(8) = ")"
    InVldChrs(9) = "!"

    InVldChrs(10) = "@"
    InVldChrs(11) = "#"
    InVldChrs(12) = "$"
    InVldChrs(13) = "<"
    InVldChrs(14) = ">"
    InVldChrs(15) = "?"
    InVldChrs(16) = "+"
    InVldChrs(17) = " "
    InVldChrs(18) = "{"
    InVldChrs(19) = "}"

    InVldChrs(20) = "["
    InVldChrs(21) = "]"
    InVldChrs(22) = "|"
    InVldChrs(23) = Chr(34)
    InVldChrs(24) = Chr(39)
    InVldChrs(25) = ""
    InVldChrs(26) = ""
    InVldChrs(27) = ""
    InVldChrs(28) = ""
    InVldChrs(29) = ""

    InVldChrs(30) = ""


    
    Idx = 1
    While Idx <= Len(strIn)

        MyChr = Mid(strIn, Idx, 1)
        If (Idx = 1) Then
            If (IsNumeric(MyChr)) Then
                strOut = "_" & MyChr
            End If
        End If

        Jdx = 0
        blnVldChr = True
        Do While Jdx <= UBound(InVldChrs)

            If (MyChr = InVldChrs(Jdx)) Then
                blnVldChr = False
                blnWhtSpc = True
                Exit Do
            End If

            Jdx = Jdx + 1
        Loop

        If (blnVldChr = True) Then
            If (blnWhtSpc = True) Then
                strOut = strOut & UCase(MyChr)
                blnWhtSpc = False
             Else
                strOut = strOut & MyChr
            End If
        End If

        Idx = Idx + 1
    Wend

    basValdName = strOut

End Function
Public Function basCreXposeTbl(strSource As String, strTarget As String) As Long

    Dim dbs As DAO.Database
    Dim rstSrc As DAO.Recordset
    Dim tblNew As TableDef
    Dim fldNew As DAO.Field
    Dim MyNewFld As String
    Dim MyFld() As MyFldType
    Dim FldLen() As String

    Dim Idx As Long
    Dim Jdx As Long
    Dim Kdx As Long
    Dim MyFldLen As Integer


    On Error GoTo ErrExit

    Set dbs = CurrentDb()
    Set rstSrc = dbs.OpenRecordset(strSource)

    'Get a valid record count from the Source Table
    
    Idx = rstSrc.RecordCount
    ReDim MyFld(Idx)
    ReDim FldLen(Idx)
    rstSrc.MoveFirst

    'First field name Derived from the Field Name of Field(0)
    MyFld(0).FldName = basValdName(rstSrc.Fields(0).Name)

    'Remaiining field names from field(0) values
    Idx = 1
    While Not rstSrc.EOF

        MyFld(Idx).FldName = basValdName(rstSrc.Fields(0).Value)

        rstSrc.MoveNext
        Idx = Idx + 1
    Wend

    'Here with a supposed set of field names in an array.  Need to _
     check for dups and adjust to make sure there are none
    Idx = 0
    While Idx <= UBound(MyFld) - 1
        Kdx = 1

        Jdx = Idx + 1
        While Jdx <= UBound(MyFld) - 1

            If (MyFld(Jdx).FldName = MyFld(Idx).FldName) Then
                'Aparent Dup - so add a suffix
                MyFld(Jdx).FldName = MyFld(Jdx).FldName & "_" & Trim(Str(Kdx))
                Kdx = Kdx + 1
            End If

            Jdx = Jdx + 1
        Wend

        Idx = Idx + 1
    Wend

    'Here to get the field Types and (for Text fields) the Sizes
    Idx = 1
    rstSrc.MoveFirst
    While Not rstSrc.EOF
        MyFldLen = 1
        Jdx = 1

        While Jdx <= rstSrc.Fields.Count - 1

            If (Not IsNull(Len(Trim(rstSrc.Fields(Jdx))))) Then
                MyFldLen = Len(Trim(rstSrc.Fields(Jdx)))
                If (MyFldLen > MyFld(Idx).FldSize) Then
                    MyFld(Idx).FldSize = MyFldLen
                End If
            End If

            Jdx = Jdx + 1
        Wend

        Idx = Idx + 1
        rstSrc.MoveNext
    Wend

    ' Create a new table to hold the transposed data.
    Set tblNew = dbs.CreateTableDef(strTarget)

    'Create a field for each record in the original table, with _
     field names derived from the First Column of the Source Recordset.

    Idx = 0
    While Idx <= UBound(MyFld)

        With tblNew
            .Fields.Append .CreateField(MyFld(Idx).FldName, dbText, MyFld(Idx).FldSize)
        End With

        Idx = Idx + 1
   Wend

    'Actually Save the New table with the fields (all are text!)
    dbs.TableDefs.Append tblNew

    'Show the new table in the dbWindow
    RefreshDatabaseWindow

    Set rstSrc = Nothing
    Set dbs = Nothing

ErrExit:
    basCreXposeTbl = Err

End Function
Public Function basXferPlanInfo(strTblIn As String, strTblOut As String) As Boolean

    Dim dbs As DAO.Database
    Dim rstSrc As DAO.Recordset
    Dim rstDest As DAO.Recordset

    Dim Idx As Integer
    Dim Jdx As Integer
    Dim Kdx As Integer
    Dim strSQl As String
    Dim MyFld As Variant
    Dim MySrcFld As String
    Dim MyDestFld As String

    strSQl = "Delete * from " & strTblOut & ";"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQl
    DoCmd.SetWarnings True

    Set dbs = CurrentDb
    Set rstSrc = dbs.OpenRecordset(strTblIn, dbOpenDynaset)
    Set rstDest = dbs.OpenRecordset(strTblOut, dbOpenDynaset)

    While Not rstSrc.EOF        'The Whole magillia

        Idx = 0

        With rstDest

            .AddNew

                While Idx < rstSrc.Fields.Count
                    MySrcFld = rstSrc.Fields(Idx).Name      'Get the name of the source field

                    'Seperate routine to return the content of "Boolean" fields _
                     (Could by "Y", "N", Other String, oor Null
                    If (rstDest(MySrcFld).Type = dbBoolean) Then
                        MyFld = basChkBlnContent(rstSrc("Company"), rstSrc("ServicePlan"), MySrcFld)
                     Else
                        'Not Boolean, just copy it
                        rstDest(MySrcFld) = rstSrc(MySrcFld)
                        GoTo NoBool
                    End If

                    If (Not IsNull(MyFld)) Then
                        'Something there, Where to Place it?
                        If (MyFld = "N") Then       'Is it a No?
                            rstDest(MySrcFld) = False
                         Else
                            rstDest(MySrcFld) = Null
                        End If
                    End If
                        
                    If (MyFld = "Y") Then
                        rstDest(MySrcFld) = True
                    End If
                    If (Len(MyFld) > 1) Then
                        rstDest(MySrcFld & "Desc") = MyFld
                    End If

NoBool:

                    Idx = Idx + 1       'rstSrc Field Index

                Wend        'Idx (Index of rstDest.Fields.Count)

            .Update

        End With            'rstSrc

        rstSrc.MoveNext         'Next Source Record

    Wend

End Function
Public Function basChkBlnContent(ByVal MyCpny As String, _
                                 ByVal MyPln As String, _
                                 Optional ByVal MyFld As Variant) As Variant

    Dim dbs As DAO.Database
    Dim rstSrc As DAO.Recordset

    Set dbs = CurrentDb
    Set rstSrc = dbs.OpenRecordset("tblSvcPlnXpose", dbOpenDynaset)

    'Routine to check for "boolean" fields which actually contains Text. _
     Where this occurs, Return the Text in the source recordset field, _
     otherwise, return Null.  The Calling procedure will set the corresponding _
     Text field to the Value returned.  The Calling procedure is further _
     responsible for the setting of the boolean field, which is based on _
     wheather there is ANY text in the "boolean" field, and what it is.

    Dim Idx As Integer
    Dim Jdx As Integer
    Dim blnFldMatch As Boolean
    Dim strCrit As String
    Dim Tmp As Variant
    Dim Quo As String * 1

    Quo = Chr(34)

    'This array is for the few "boolean" fields in [rstSrc] _
     which (unfortunatly) include text.  The Text (other than Y/N) _
     needs to be transfered to the Filed with the Name "FldsNew" & Desc _
     (e.g. the Text would go into [CirculatorPumpDesc] and the (boolean _
     field [CirculatorPump] is set to "Y" (true).
    Dim FldsNew(8) As String
    FldsNew(0) = "CirculatorPump"
    FldsNew(1) = "CirculatorRelays"
    FldsNew(2) = "EletricalWiring"
    FldsNew(3) = "HotWaterHeaterTankAndCoil"
    FldsNew(4) = "PressureReliefValves"
    FldsNew(5) = "PurgingValve"
    FldsNew(6) = "ZoneDampers"
    FldsNew(7) = "ZoneMotors"
    FldsNew(8) = "ZoneValve"

    Jdx = 0
    Do While Jdx <= UBound(FldsNew)

        If (MyFld) = FldsNew(Idx) Then
            blnFldMatch = True
            Exit Do
        End If

        Jdx = Jdx + 1

    Loop

    strCrit = "Company = " & Quo & MyCpny & Quo & " and " & "ServicePlan = " & Quo & MyPln & Quo
    rstSrc.FindFirst strCrit

    If (rstSrc.NoMatch) Then
        'Error. Just Bail
        GoTo ErrExit
    End If

    If (Jdx > UBound(FldsNew)) Then
        'No match to the bad boys, set return and exit
        Tmp = rstSrc(MyFld)
        GoTo NormExit
    End If

    'Here because there IS a match
    If (IsNull(rstSrc(MyFld))) Then
        Tmp = Null
        GoTo NormExit
    End If

    If (IsMissing(rstSrc(MyFld))) Then
        Tmp = Null
        GoTo NormExit
    End If

    If (Len(rstSrc(MyFld)) > 0) Then
        Tmp = Trim(rstSrc(MyFld))
        GoTo NormExit
    End If

NormExit:

    If (IsEmpty(Tmp)) Then
        Tmp = rstSrc(MyFld)
    End If

    basChkBlnContent = Tmp

ErrExit:
    Exit Function

End Function

MichaelRed

 

[sxschech]

Michael,

Thanks for the code, we're almost there. The transposition worked fine for the first col, but the second col, which holds the numeric data (Number - Long Integer in source table) is blank. Is there some other step I have missed?

 

[MichaelRed]

hmmmmmmmmmmmmmmmmmmmmmm ... mmmmmmmmmmmmmmmmmmmmmmmmm ...

mmmmmmmmmmmmmmmmmmmm ... mmmmmmmmmmmmmmmmmmmmmmmmm

Don't know quite (exactly) what you did. I only developed the process as part of an exercise involving larger manipulations. I have only "tested" it in that context. Within these confines, it works -with a few notable exceptions. The largest (notable exception) is that it cannot (more or less obviously I think) cannot create a transposed table with more than 255 fields (columns).

Other issues may also be involved, but without some other information, I do not see any reason the propcess wouldn't be generically applicable to (modestly sized) general purpose tables. You CAN get some different field names than might be expected, as the process attempts to weed out illegal characters for field names which reside in the data of the first field.

Since I am currently pretty deep into the snapping critters within the swamp of work day events, I cannot provide a lot of additional help, so any direct requsets (to me) would need to have fairly specific issues specified and be accompanied by relevant samples of the data sets in a format which could readily be copied and entered into a live source. I find that this can be accomplished easily within the constraints of Tek-Tips by exporting sample data to a CDF.

Otherwise, there are others lurking about here (in Tek-Tips) who should be more than capable of understanding the processes involved and, perhaps, be willing to shepard you through the variances between your needs and the current capabilities of this code.





MichaelRed

 

[sxschech]

Michael,

Really appreciate your efforts. If you take the original example and plug into a table:

[tt]
Total Group1 Group2 AvgGroup1 AvgGroup2
81 35 46 125.9 82.1
[/tt]

Data values are numeric. I also tried changing data values to text and result was the same.

When I run your code, which I did by hitting Ctrl+G and then typing [tt]? basxpose("table1","trans")[/tt] the result is:
[tt]
Header Row Total _881
Data Rows Group1
Group2
AvgGroup1
AvgGroup2

This is how it should look

Total 81
Group1 35
Group2 46
AvgGroup1 125.9
AvgGroup2 82.1

I also tried putting a dummy first col.
Total GroupTotal Group1 Group2 AvgGroup1 AvgGroup2
Summary 81 35 46 125.9 82.1

Which resulted in:
Header Row Total Summary
Data Rows Group Total
Group1
Group2
AvgGroup1
AvgGroup2
[/tt]

Hopefully the data set is simple enough to work with to resolve the issue and sorry for taking too much of your time on this subject.

 

[MichaelRed]

Your "example" is -perhaps TOO simple.

To Transpose, requires more 'data'. The "Original" field List (First ROW of the Recordset) becomes the First Column, except (or course you knew this part) for the Piviot column (in my simplistic case the first column). The remainder of the (Unique) records fromt he first column (field) are (Obviously?) used as the field names (column names) for the remaining records. 'Some' adjustment is often made tot he field names to assure that they are "legal" field names for a recordset.

I rather arbitrarily appended a few non-sense records to your example just for illustration:


first, the "table" with the non-sense records added:

Total	Group1	Group2	AvgGroup1	AvgGroup2
81	35	46	125.9	82.1
48	33	17	28	39
421	93	66	12	100

Then the transposition thereof:

Total	_881	_448	_4421
Group1	35	33	
Group2	46	17	
AvgGroup1	125.9	28	
AvgGroup2	82.1	39

So, as you previously noted, The original field names are now the record's first field (excepting again -of course- the piviot element)

So the field names are 'derived' from the piviot column and are 'reasonably' (perhaps) identified as "_881" from "81", "_448" corresponding to "48" and so forth.

Perhaps the easier soloution in your situation would be to use Excel as an OLE server to perform this task.



MichaelRed

 

[sxschech]

Thanks everyone for your ideas. I received a suggestion, which I tried that seems to work. I am now using a union query with D functions such as Dsum and Dcount and am able to produce the list of 36 items in a vertical format. Granted, it takes 45 seconds versus the original query's 15 seconds, but at least now I don't have to transpose it in excel to make the results vertical. The only strange thing, perhaps my lack of understanding about the d functions, is that the Davg did not give the same result as dividing the total sum by the total count. The expected result is 17190.65, but Davg produced 35308.36

[tt]
This was formula used in Original Query:
Ave Cost Increase: Sum([cost_increase_amount])/Count(*) 22915130 / 1333 = 17190.65

Using Davg Calculation:
Ave Cost Increase: val(Davg("cost_increase_amount","qryLog")) = 35308.36672 {Not sure of input values so can only show result}

For the meantime, I am doing the following in order to get the desired result:
Ave Cost Increase: val(Dsum("cost_increase_amount","qryLog"))/val(DCount("Doc_num","qryLog")) = 22915130 / 1333 = 17190.65
[/tt]

 

[pbrodsky]

if you have any nulls, dcount("doc_num") is not necessarily the same as dcount("cost_increase_amount")

 

[sxschech]

Thanks pbrodsky for pointing out the nulls, that was the reason the totals did not match. I fixed the query to convert null to 0 and then the expected amount displayed using the davg function. Would have been nice if Access gave an error message instead of the wrong amount when nulls are present.

 

[pbrodsky]

It's not an error - that is the way nulls are supposed to work.