cross tab

G

Guest

I have a table that reflects placement date and tranaction date along with
the tranaction detail of course. I ran a query reflecting "Placement Date"
minus "Tranaction Date" for aging purposes. My question is regarding
breaking the results into a cross tab by 30, 60, 90, 120+. I need help
figuring out the expression for each and then how to place results into a
cross tab. FYI, I'm self tought so step by step would be great. Thanks.
 
J

John W. Vinson

I have a table that reflects placement date and tranaction date along with
the tranaction detail of course. I ran a query reflecting "Placement Date"
minus "Tranaction Date" for aging purposes. My question is regarding
breaking the results into a cross tab by 30, 60, 90, 120+. I need help
figuring out the expression for each and then how to place results into a
cross tab. FYI, I'm self tought so step by step would be great. Thanks.
Click to expand...

Put a calculated field in the query:

Age: Switch(DateDiff("d", [Transaction Date], [Placement Date]) <= 30, "<30",
DateDiff("d", [Transaction Date], [Placement Date]) <= 60, "30",
DateDiff("d", [Transaction Date], [Placement Date]) <= 90, "60",
DateDiff("d", [Transaction Date], [Placement Date]) <= 120, "90",
True, "120+")

Use this field (which will have values <30, 30, 60, 90, 120+) as the Column
Header for your crosstab.

John W. Vinson [MVP]
 
G

Guest

I'm confused. when placing that into my query my query reflects <30 for all,
which is not the case for all. Also, I need help with the cross tab. I'v
never done this before. I'd like the end result to look like this:

Loan Agency Placement Trans Journal Tranaction Total
# Fund Code Date Date Date Type Payment
30 60 90 120+

Sorry I'm truly not that technical and any assistance would be great.


John W. Vinson said:
I have a table that reflects placement date and tranaction date along with
the tranaction detail of course. I ran a query reflecting "Placement Date"
minus "Tranaction Date" for aging purposes. My question is regarding
breaking the results into a cross tab by 30, 60, 90, 120+. I need help
figuring out the expression for each and then how to place results into a
cross tab. FYI, I'm self tought so step by step would be great. Thanks.
Click to expand...

Put a calculated field in the query:

Age: Switch(DateDiff("d", [Transaction Date], [Placement Date]) <= 30, "<30",
DateDiff("d", [Transaction Date], [Placement Date]) <= 60, "30",
DateDiff("d", [Transaction Date], [Placement Date]) <= 90, "60",
DateDiff("d", [Transaction Date], [Placement Date]) <= 120, "90",
True, "120+")

Use this field (which will have values <30, 30, 60, 90, 120+) as the Column
Header for your crosstab.

John W. Vinson [MVP]
Click to expand...
 
J

John W. Vinson

I'm confused. when placing that into my query my query reflects <30 for all,
which is not the case for all. Also, I need help with the cross tab. I'v
never done this before. I'd like the end result to look like this:

Loan Agency Placement Trans Journal Tranaction Total
# Fund Code Date Date Date Type Payment
30 60 90 120+

Sorry I'm truly not that technical and any assistance would be great.
Click to expand...

Well, I'm confused too then. Bear in mind that you have not posted the
structure of your table, nor the SQL view of the query that you're using.
Please do and I or someone will try to help.

John W. Vinson [MVP]
 
G

Guest

Sorry. I alway wondered where the SQL statment was via access. Told you I
was new to Access. Anyway, here you go. Thanks so much.

SELECT [Master_Edata Result].MASTER2_Fund, [Master_Edata
Result].[MASTER2_Loan Number], [Master_Edata Result].[Agency Code],
[Master_Edata Result].[Placement Date], [Master_Edata Result].[Placement
Balance], [Master_Edata Result].Edata_Combined_tbl_Fund, [Master_Edata
Result].[Edata_Combined_tbl_Loan Number], [Master_Edata Result].[Transaction
Date], [Master_Edata Result].[Journal Date], [Master_Edata
Result].[Transaction Type], [Master_Edata Result].[Total Payment],
[Master_Edata Result].Expr1, Switch(DateDiff("d",[Transaction
Date],[Placement Date])<=30,"<30",DateDiff("d",[Transaction Date],[Placement
Date])<=60,"30",DateDiff("d",[Transaction Date],[Placement
Date])<=90,"60",DateDiff("d",[Transaction Date],[Placement
Date])<=120,"90",True,"120+") AS Age
FROM [Master_Edata Result];
 
J

John W. Vinson

Sorry. I alway wondered where the SQL statment was via access. Told you I
was new to Access. Anyway, here you go. Thanks so much.

SELECT [Master_Edata Result].MASTER2_Fund, [Master_Edata
Result].[MASTER2_Loan Number], [Master_Edata Result].[Agency Code],
[Master_Edata Result].[Placement Date], [Master_Edata Result].[Placement
Balance], [Master_Edata Result].Edata_Combined_tbl_Fund, [Master_Edata
Result].[Edata_Combined_tbl_Loan Number], [Master_Edata Result].[Transaction
Date], [Master_Edata Result].[Journal Date], [Master_Edata
Result].[Transaction Type], [Master_Edata Result].[Total Payment],
[Master_Edata Result].Expr1, Switch(DateDiff("d",[Transaction
Date],[Placement Date])<=30,"<30",DateDiff("d",[Transaction Date],[Placement
Date])<=60,"30",DateDiff("d",[Transaction Date],[Placement
Date])<=90,"60",DateDiff("d",[Transaction Date],[Placement
Date])<=120,"90",True,"120+") AS Age
FROM [Master_Edata Result];

Click to expand...

Maybe I'm misunderstanding the date difference: if the [Placement Date] is
later than the [Transaction Date], reverse them in all the DateDiffs.

Then change the query into a Crosstab query using the query wizard. Use
MASTER2_Loan Number, Agency Code, etc. as the Row Header values, Age as the
Column Header, and Sum(Total Payment) as the value.

John W. Vinson [MVP]
 
G

Guest

I'll try that. Thanks.

John W. Vinson said:
Sorry. I alway wondered where the SQL statment was via access. Told you I
was new to Access. Anyway, here you go. Thanks so much.

SELECT [Master_Edata Result].MASTER2_Fund, [Master_Edata
Result].[MASTER2_Loan Number], [Master_Edata Result].[Agency Code],
[Master_Edata Result].[Placement Date], [Master_Edata Result].[Placement
Balance], [Master_Edata Result].Edata_Combined_tbl_Fund, [Master_Edata
Result].[Edata_Combined_tbl_Loan Number], [Master_Edata Result].[Transaction
Date], [Master_Edata Result].[Journal Date], [Master_Edata
Result].[Transaction Type], [Master_Edata Result].[Total Payment],
[Master_Edata Result].Expr1, Switch(DateDiff("d",[Transaction
Date],[Placement Date])<=30,"<30",DateDiff("d",[Transaction Date],[Placement
Date])<=60,"30",DateDiff("d",[Transaction Date],[Placement
Date])<=90,"60",DateDiff("d",[Transaction Date],[Placement
Date])<=120,"90",True,"120+") AS Age
FROM [Master_Edata Result];

John W. Vinson said:
On Sun, 24 Jun 2007 06:15:00 -0700, Renetta

I'm confused. when placing that into my query my query reflects <30 for all,
which is not the case for all. Also, I need help with the cross tab. I'v
never done this before. I'd like the end result to look like this:

Loan Agency Placement Trans Journal Tranaction Total
# Fund Code Date Date Date Type Payment
30 60 90 120+

Sorry I'm truly not that technical and any assistance would be great.
Click to expand...
Click to expand...

Maybe I'm misunderstanding the date difference: if the [Placement Date] is
later than the [Transaction Date], reverse them in all the DateDiffs.

Then change the query into a Crosstab query using the query wizard. Use
MASTER2_Loan Number, Agency Code, etc. as the Row Header values, Age as the
Column Header, and Sum(Total Payment) as the value.

John W. Vinson [MVP]
Click to expand...
 
G

Guest

After changing the Placement date and the Transaction date it worked. Thanks
so much!

John W. Vinson said:
Sorry. I alway wondered where the SQL statment was via access. Told you I
was new to Access. Anyway, here you go. Thanks so much.

SELECT [Master_Edata Result].MASTER2_Fund, [Master_Edata
Result].[MASTER2_Loan Number], [Master_Edata Result].[Agency Code],
[Master_Edata Result].[Placement Date], [Master_Edata Result].[Placement
Balance], [Master_Edata Result].Edata_Combined_tbl_Fund, [Master_Edata
Result].[Edata_Combined_tbl_Loan Number], [Master_Edata Result].[Transaction
Date], [Master_Edata Result].[Journal Date], [Master_Edata
Result].[Transaction Type], [Master_Edata Result].[Total Payment],
[Master_Edata Result].Expr1, Switch(DateDiff("d",[Transaction
Date],[Placement Date])<=30,"<30",DateDiff("d",[Transaction Date],[Placement
Date])<=60,"30",DateDiff("d",[Transaction Date],[Placement
Date])<=90,"60",DateDiff("d",[Transaction Date],[Placement
Date])<=120,"90",True,"120+") AS Age
FROM [Master_Edata Result];

John W. Vinson said:
On Sun, 24 Jun 2007 06:15:00 -0700, Renetta

I'm confused. when placing that into my query my query reflects <30 for all,
which is not the case for all. Also, I need help with the cross tab. I'v
never done this before. I'd like the end result to look like this:

Loan Agency Placement Trans Journal Tranaction Total
# Fund Code Date Date Date Type Payment
30 60 90 120+

Sorry I'm truly not that technical and any assistance would be great.
Click to expand...
Click to expand...

Maybe I'm misunderstanding the date difference: if the [Placement Date] is
later than the [Transaction Date], reverse them in all the DateDiffs.

Then change the query into a Crosstab query using the query wizard. Use
MASTER2_Loan Number, Agency Code, etc. as the Row Header values, Age as the
Column Header, and Sum(Total Payment) as the value.

John W. Vinson [MVP]
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Forms with parameters to pass to cross-tab queries 3
Dymanic Cross-Tab Report 2
Error on a cross-tab query 1
Calculating data from records results of a cross tab query 2
Cross Tab Report 3
Percent calculation based on cross-tab query 6
Data in Cross-Tab format: needs to be written in Table Format 2
Maturity Date Calculation 1

Top