How to show dates as columns

[netrusher]

I have a current need to display the results of a query
in a particular way. The results are needed as follows:

Date
WU Totals
Leak Totals
Pct

I need the dates (range) to show across as columns headings

I need the WU totals as one row below. One total for each
date column.

I need the Leak totals as one row below the WU totals. One
total for each date column.

I need the pct totals as one row below. One total for each
date column.

I have tried everything I know. I have tried crosstabs etc.
Can anyone help me with this?

Date 5/1/08 5/2/08 5/3/08 5/4/08 5/5/08
WUTot 3 6 9 10 3
LKTot 1 3 6 5 1
Pct 33% 50% 66% 50% 33%

I can obtain each number separately and combine. I am
willing to try anything to get this accomplished.

Let's say I just get the WUTotals. The dates all go down
as rows and the totals are beside them. How can I turn
it around where the dates are columns. Any help will be
appreciated.

 

[PHV]

What is wrong with a CrossTab query ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BigRed1212]

If you want to present your query output in a Web page you could use a scripting language to format the output in a table.

Run the individual queries and write them as individual table rows.

 

[netrusher]

This is not for a webpage. It is for export to Excel. I
tried everything with crosstab and could not get the
results to look like above.

 

[PHV]

display the results of a query
Which query ? With which actual result ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[netrusher]

Below is the crosstab I currently have. This will not
give me the results I am looking for as show in my
first posting. Thanks for any help.

TRANSFORM Sum(LeaksPct.Percent) AS SumOfPercent
SELECT LeaksPct.[WU Totals], LeaksPct.[Leak Totals]
FROM LeaksPct
GROUP BY LeaksPct.[WU Totals], LeaksPct.[Leak Totals]
ORDER BY LeaksPct.Date
PIVOT LeaksPct.Date;

 

[netrusher]

This give me the Dates across as columns which I want.
It then gives me the WU totals and Leak totals columns
with the totals in each column. I would like these totals
to be under the date columns one for each date.

 

[PHV]

What is the actual result with the data provided in your expected result (18 Jun 08 13:06)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

There is an FAQ faq703-5466 that describes how to create a crosstab with monthly intervals as column headings. You could use the same idea substituting daily rather than monthly.

Duane
Hook'D on Access
MS Access MVP

 

[netrusher]

dhookom,

Thanks for the tip. I looked at that and have no idea how
to turn it into what I am looking for. I do not want a report. I want the results in a query. Thanks again.

 

[netrusher]

PHV,

I posted the query and the results are as stated. Any advice?

 

[dhookom]

I thought you wanted the results to go to Excel. Some versions of Access allow reports to be exported to Excel.

Actually I would expect the best results could be attained by creating a pivot table in Excel that is linked to an Access table or query.

Duane
Hook'D on Access
MS Access MVP

 

[netrusher]

Duane,

I will export the final product to excel. I just want
to get the query to look correctly in access if possible.

 

[dhookom]

Create a crosstab and query it into Excel. You may need to create a DSN of your MDB first. Then in Excel, you can display the live data. I just did this with the [Quarterly Orders by Product] crosstab from Northwind.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

the results are as stated
So, where is the problem ?
It's not clear to me what is the actual result of your posted query vs the expected result, sorry.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[netrusher]

Thanks Duane,

I have tried as I put above to create a crosstab query
and have failed miserably. I am open to your help.

 

[dhookom]

It isn't apparent what your current table structure or data look like.

Duane
Hook'D on Access
MS Access MVP

 

[Golom]

I started with this

[tt]tblDT
TheDate WUTot LKTot Pct
01/01/2008 10 20 25.5
01/02/2008 14 16 27
01/03/2008 22 6 52.5
01/04/2008 18 54 26.3
[/tt]
And then ran this

Sub FlipTable()
    Dim db                          As DAO.Database
    Dim tdf                         As DAO.TableDef
    Dim rs                          As DAO.Recordset
    Dim rn                          As DAO.Recordset
    Dim FieldName                   As String
    Dim SQL                         As String

    Set db = DAO.DBEngine(0).OpenDatabase(".\ChapsMast.mdb")
    
    ' Get rid of the table if it already exists.
    On Error Resume Next
    db.Execute "DROP TABLE tblDateColumns"
    
    ' Create the new table
    Set tdf = db.CreateTableDef("tblDateColumns")

    ' Insert the columns into the table
    tdf.Fields.Append tdf.CreateField("RecType", dbText, 5) ' WUTot, LKTot, Pct
    tdf.Fields.Append tdf.CreateField("RecSeq", dbInteger)  ' 1, 2, 3 to allow ordering
    Set rs = db.OpenRecordset("Select DISTINCT TheDate From tblDR Order By 1")
    
    ' Build an SQL statement to use for the new table.
    SQL = "SELECT [RecType]"
    Do Until rs.EOF
        tdf.Fields.Append tdf.CreateField(Format(rs![TheDate], "yyyy-mm-dd"), dbSingle)
        SQL = SQL & ", [" & Format(rs![TheDate], "yyyy-mm-dd") & "]"
        rs.MoveNext
    Loop
    SQL = SQL & " From tblDateColumns Order By [RecSeq]"
    
    ' Append the table to the database
    db.TableDefs.Append tdf

    ' Create the rows that you need.
    db.Execute "INSERT INTO tblDateColumns (RecType, RecSeq) VALUES('WUTot',1)"
    db.Execute "INSERT INTO tblDateColumns (RecType, RecSeq) VALUES('LKTot',2)"
    db.Execute "INSERT INTO tblDateColumns (RecType, RecSeq) VALUES('Pct',3)"
    
    ' Open the source table
    Set rs = db.OpenRecordset("Select TheDate, WUTot, LKTot, Pct From tblDR Order By 1")
    
    ' ... and the destination table
    Set rn = db.OpenRecordset("Select * From tblDateColumns")

    Do Until rs.EOF
        
        ' Identify the field to receive the values.
        FieldName = Format(rs![TheDate], "yyyy-mm-dd")

        With rn
            ' Insert WUTot Values
            .FindFirst "RecType = 'WUTot'"
            .Edit
            .Fields(FieldName).Value = rs![WUTot]
            .Update

            ' Insert LKTot Values
            .FindFirst "RecType = 'LKTot'"
            .Edit
            .Fields(FieldName).Value = rs![LKTot]
            .Update

            ' Insert Pct Values
            .FindFirst "RecType = 'Pct'"
            .Edit
            .Fields(FieldName).Value = rs![Pct]
            .Update
        End With

        rs.MoveNext
    Loop
    
    Debug.Print SQL
End Sub

and ended up with this

[tt]tblDateColumns
RecType 2008-01-01 2008-01-02 2008-01-03 2008-01-04
WUTot 10 14 22 18
LKTot 20 16 6 54
Pct 25.5 27 52.5 26.3
[/tt]

Is that what you're looking for?

 

[dhookom]

I would not use any code to get this to Excel. If Golom's structure is accurate, create a union query to normalize the data:

SELECT TheDate, WUTot as TheVal, "a_WUTot" as RowHead
FROM tblDT
UNION ALL
SELECT TheDate, LKTot, "b_LKTot"
FROM tblDT
UNION ALL
SELECT TheDate, Pct, "c_Pct"
FROM tblDT

Then create a crosstab based on the above query that uses TheDate as the Column Heading, RowHead as the Row Heading, and Sum of TheVal as the Value. Link to this crosstab query from Excel.

Duane
Hook'D on Access
MS Access MVP