 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / June 2007cross tab
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. >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. 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] 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. > >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" [quoted text clipped - 15 lines] > > John W. Vinson [MVP] >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 [quoted text clipped - 5 lines] > >Sorry I'm truly not that technical and any assistance would be great. 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] 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]; > >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 [quoted text clipped - 11 lines] > > John W. Vinson [MVP] >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. [quoted text clipped - 22 lines] >> > >> >Sorry I'm truly not that technical and any assistance would be great. 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] I'll try that. Thanks. > >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. [quoted text clipped - 31 lines] > > John W. Vinson [MVP] After changing the Placement date and the Transaction date it worked. Thanks so much! > >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. [quoted text clipped - 31 lines] > > John W. Vinson [MVP] |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.