Modify Crosstab Query Output in a Report

[jsccorps]

My initial Select query results in:

CustID Date #Sales Amount
100 9/13/2004 7 350.00
103 9/13/2004 9 100.00
103 9/14/2004 4 200.00
106 9/13/2004 2 150.00
106 9/14/2004 8 300.00

The Crosstab query results in:

CustID SumofSales SumOfAmount 9/13/2004 9/14/2004
100 7 350.00 350.00
103 13 300.00 100.00 200.00
106 10 450.00 150.00 300.00

I would like my report to arrange the sub amounts in descending order. The
Date headings would be replaced with "Sub_Amounts in Descending Order". In
this example, I would like the report to appear as:

CustID Sales TotalAmount Sub_Amounts in Descending Order
100 10 100.00 100.00
103 13 300.00 200.00
100.00
106 8 450.00 300.00
150.00

Any ideas?

 

[George Nicholson]

-Use something like the following in a Select query to get a "Rank within
Group" value.
The 5th field "Rank" & the Dcount function is key. The ORDER BY isn't
necessary to have the correct calculation performed, but is is necessary if
you want to confirm it for yourself

-Build your CrossTab off of that query using Rank as your ColumnHeading
(rather than Date)

SELECT datXtabData.CustID, datXtabData.Date, datXtabData.Sales,
datXtabData.Amount, DCount("[CustID]","datxtabData",CLng([CustID]) &
"=CLng([CustID]) And " & [Amount] & "<[Amount]")+1 AS Rank
FROM datXtabData
ORDER BY datXtabData.CustID, datXtabData.Amount DESC;

And if anyone has an in-one approach that doesn't require Dcount, I'd love
to hear it.

HTH,

 

[jsccorps]

HTH

Getting close. I use the SQL that you suggested. However, in the select
query, the values in all the fields are good, except the Rank field which has
#Error all the way down.

Any ideas why this is happening?

-Use something like the following in a Select query to get a "Rank within
Group" value.
The 5th field "Rank" & the Dcount function is key. The ORDER BY isn't
necessary to have the correct calculation performed, but is is necessary if
you want to confirm it for yourself

-Build your CrossTab off of that query using Rank as your ColumnHeading
(rather than Date)

SELECT datXtabData.CustID, datXtabData.Date, datXtabData.Sales,
datXtabData.Amount, DCount("[CustID]","datxtabData",CLng([CustID]) &
"=CLng([CustID]) And " & [Amount] & "<[Amount]")+1 AS Rank
FROM datXtabData
ORDER BY datXtabData.CustID, datXtabData.Amount DESC;

And if anyone has an in-one approach that doesn't require Dcount, I'd love
to hear it.

HTH,
--
George Nicholson

Remove 'Junk' from return address.

My initial Select query results in:

CustID Date #Sales Amount
100 9/13/2004 7 350.00
103 9/13/2004 9 100.00
103 9/14/2004 4 200.00
106 9/13/2004 2 150.00
106 9/14/2004 8 300.00

The Crosstab query results in:

CustID SumofSales SumOfAmount 9/13/2004 9/14/2004
100 7 350.00 350.00
103 13 300.00 100.00 200.00
106 10 450.00 150.00 300.00

I would like my report to arrange the sub amounts in descending order.
The
Date headings would be replaced with "Sub_Amounts in Descending Order".
In
this example, I would like the report to appear as:

CustID Sales TotalAmount Sub_Amounts in Descending Order
100 10 100.00 100.00
103 13 300.00 200.00
100.00
106 8 450.00 300.00
150.00

Any ideas?

 

[jsccorps]

Thanks. the following is clarification on what i need.

Have the following data that I need to rank

ID SalesDate Sales Amount
103 9/13/2004 5 200
103 9/14/2004 8 30
104 9/3/2004 12 50
104 9/14/2004 11 350
105 9/13/2004 10 70

Want to rank by ID and Amount . In other words, rank amount within ID.
This is the desired
result:

ID SalesDate Sales Amount Rank
103 9/13/2004 5 200 1
103 9/14/2004 8 30 2
104 9/3/2004 12 50 2
104 9/14/2004 11 350 1
105 9/13/2004 10 70 1

Any ideas?

MichaelDJ

-Use something like the following in a Select query to get a "Rank within
Group" value.
The 5th field "Rank" & the Dcount function is key. The ORDER BY isn't
necessary to have the correct calculation performed, but is is necessary if
you want to confirm it for yourself

-Build your CrossTab off of that query using Rank as your ColumnHeading
(rather than Date)

SELECT datXtabData.CustID, datXtabData.Date, datXtabData.Sales,
datXtabData.Amount, DCount("[CustID]","datxtabData",CLng([CustID]) &
"=CLng([CustID]) And " & [Amount] & "<[Amount]")+1 AS Rank
FROM datXtabData
ORDER BY datXtabData.CustID, datXtabData.Amount DESC;

And if anyone has an in-one approach that doesn't require Dcount, I'd love
to hear it.

HTH,
--
George Nicholson

Remove 'Junk' from return address.

My initial Select query results in:

CustID Date #Sales Amount
100 9/13/2004 7 350.00
103 9/13/2004 9 100.00
103 9/14/2004 4 200.00
106 9/13/2004 2 150.00
106 9/14/2004 8 300.00

The Crosstab query results in:

CustID SumofSales SumOfAmount 9/13/2004 9/14/2004
100 7 350.00 350.00
103 13 300.00 100.00 200.00
106 10 450.00 150.00 300.00

I would like my report to arrange the sub amounts in descending order.
The
Date headings would be replaced with "Sub_Amounts in Descending Order".
In
this example, I would like the report to appear as:

CustID Sales TotalAmount Sub_Amounts in Descending Order
100 10 100.00 100.00
103 13 300.00 200.00
100.00
106 8 450.00 300.00
150.00

Any ideas?