Create An Aging Report with Two Values in the Crosstab Query

[koolwaters]

I am trying to create an aging report that gives the sum of transactions by customer as well as the number of transactions between 0-30 Days 31-60 days 61-90 days and over 90 days.

I am able to create a crosstab query which gives the sum of transactions by customer for the different bands but am unable to get the count of transactions.

Can anyone suggest how to accomplish this? My finished report should look similar to the format below.

Excel 2010
	A	B	C	D	E	F	G	H	I
1		0-30 Days		31-60 Days		61-90 Days		> 90 Days
2		# of Transactions	Revenue	# of Transactions	Revenue	# of Transactions	Revenue	# of Transactions	Revenue
3	Customer 1	6	25,000.00	4	68,000.00	1	85,000.00	16	173,854.00
4	Customer 2	4	68,000.00	1	85,000.00	16	173,854.00	6	25,000.00
5	Customer 3	16	173,854.00	6	25,000.00	4	68,000.00	1	85,000.00
Sheet1

Thanks for any assistance.

Cheers!

 

[CyrusTheVirus]

And where are you pulling all of this info from?

Unless I'm not understanding, we have nothing to go on here...

What is your starting point?

 

[koolwaters]

The information is stored in tables in a database. The crosstab is based on a select query that pulls the required fields.

 

[koolwaters]

Hi,

No one has any suggestions on accomplishing this?

 

[xenou]

Hi, you can't do this in one cross tab. You need to create two cross tabs as your starting point. Then join them in a second step in order to "combine" them and make them look like one query.

 

[koolwaters]

Thanks for the response. I have already created two cross tab queries and then used a select query to pull the data together but I was having an issue so I was looking for an alternative.

When I add new records, the select query does not automatically pull in the new data. For example, when the query was created, I had 0-30 days and 31-60 days. As I add new test records, the select query did not include the additional columns that fell between 61-90 days and >90 days. To get past this, I used all of the fields in each cross tab query in my select query but it became tedious when creating a report on the select query.

 

[xenou]

For example, when the query was created, I had 0-30 days and 31-60 days. As I add new test records, the select query did not include the additional columns that fell between 61-90 days and >90 days. To get past this, I used all of the fields in each cross tab query in my select query but it became tedious when creating a report on the select query.

Will this stabilize - i.e., going forward you will now have a full range of data in the report for 0-30, 31-60, 61-90, and 90+ days?

 

[koolwaters]

Correct. It will

 

[xenou]

Alright. So ... it's okay now then?

 

[koolwaters]

Yes. I used the asterisk in the select query that pulls the fields from both cross tab queries, . I created the report with the required column headings and have used the Nz function to display zeroes in my report so the report is displayed as it should.

Thanks