Display continuous form vertically

[GFullington]

TRIED A PIVOT TABLE, BUT ADDS TOTALS AND DISPLAYS THE COLUMNS HORIZONTALLY.

IN ESSENCE, i WANT THE ROWS TO BE VERTICAL INSTEAD OF HORIZONTAL.

 

[PHV]

Why not using a SELECT query ?

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

 

[GFullington]

how can I use a select query to display a row vertically?

 

[MajP]

can you describe your table fields and show some rough data of how it should look? My guess is probably a crosstab query.

 

[GFullington]

worked with the crosstab query, no luck so far. Here is some additional info;
Data colums in the table are as follows;

CustId InvoicePeriod Balance CAP BilledAmt
111 10/1/2009 1.00 1.00 1.00
111 9/1/2009 2.00 2.00 2.00
111 8/1/2009 3.00 3.00 3.00 111 7/1/2009 4.00 400.00 400.00

I would like the form to display;

cust id 111

invoice dt 10/1/2009 9/1/2009 8/1/2009 7/1/2009
Balance 1.00 2.00 3.00 4.00
CAP 1.00 2.00 3.00 400.00
BilledAmt 1.00 2.00 3.00 400.00

 

[dhookom]

You need to first create a normalizing union query like:

SELECT CustID, InvoicePeriod, "Balance" as Item,[Balance] as Amt
FROM tblWithNoName
UNION ALL
SELECT CustID, InvoicePeriod, "CAP",[CAP]
FROM tblWithNoName
UNION ALL
SELECT CustID, InvoicePeriod, "BilledAmt",[BilledAmt]
FROM tblWithNoName;

Then create a crosstab from the union query with CustID and Item as the Row Headings, Format(InvoicePeriod,"Short Date") as the Column Heading, and Sum of Amt as the value.


Duane
Hook'D on Access
MS Access MVP

 

[GFullington]

Thanks! I will give it a try.

 

[GFullington]

Works Great!!!
I am now trying to get the invoice date columns to display in descending order left to right. Tried an order by clause in both the union and cross tab qeuries with no success.
Any ideas?

 

[dhookom]

Your dynamic columns will only display in order of their text values. You can change this by using the Column Headings property. Otherwise, if this is for a report, you can check the reports forum FAQs for a dynamic monthly crosstab report.

Duane
Hook'D on Access
MS Access MVP

 

[GFullington]

In the help for the crosstab column headings properties, it is looking for the name of the column. In my case, the names are actual dates that can change for each customer id.

How do I reference and sort the column names in the ColumnHeadings property?

 

[dhookom]

If your date column headings are dynamic and you want to display the results in a form and you want them in descending order, you would need to use code to modify the SQL property of your saved crosstab query.

Your dates look monthly. Is this always the case? Do you always want to display a specific number of months or days?

Are you sure you need to display this in a form and not a report?

Is it important to have them display descending?

Duane
Hook'D on Access
MS Access MVP

 

[GFullington]

Thanks.
I may also pursue writing the column labels and data to a table and sort them somehow. I will also need to create a dynamic.

The dates are always monthly.
I am displaying the data in a form.
The number of months to display is dynamic, but in most cases, the number will be the same.
It is important to display descending. (recent dates first)

 

[dhookom]

Based on your requirements, you will need to write some code to dynamically build your SQL statement for your crosstab.

Assuming your crosstab is like this:

TRANSFORM Sum(Amt) AS SumOfAmt
SELECT CustID, Item
FROM qselFullington
GROUP BY CustID, Item
PIVOT InvoicePeriod;

Your code might look like this:

Public Function CreateXTBQueryFullington()
    Dim strSQL As String
    Dim strColumnHeadings As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    strSQL = "SELECT DISTINCT InvoicePeriod FROM qselFullington ORDER BY InvoicePeriod DESC"
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL)
    With rs
        .MoveFirst
        Do Until .EOF
            strColumnHeadings = strColumnHeadings & rs(0) & ","
            .MoveNext
        Loop
        .Close
    End With
    Set rs = Nothing
    'get rid of the last ","
    strColumnHeadings = Left(strColumnHeadings, Len(strColumnHeadings) - 1)
    strSQL = "TRANSFORM Sum(qselFullington.Amt) AS SumOfAmt " & _
        "SELECT qselFullington.CustID, qselFullington.Item " & _
        "from qselFullington " & _
        "GROUP BY qselFullington.CustID, qselFullington.Item " & _
        "PIVOT qselFullington.InvoicePeriod " & _
        " IN (" & strColumnHeadings & ");"
    db.QueryDefs("qxtbFullington").SQL = strSQL
    Set db = Nothing
End Function

Duane
Hook'D on Access
MS Access MVP