Query Help

R

Renetta

Please help - I need to run a query between two tables

The two tables are linked by the
[Account#]. The date formate for both tables [GranceDate] and [PaymentDate]
are in Date/Time formate.

What I need is:

In the Grace_tbl return all accounts [Account#] along with their grace
ending date [GraceDate] and from the Payments_tbl return only those matching
accounts [Account#]with a [Payment] and [PaymentDate] range of 0-30, 31-60
and 61-90

Sample qry below - I need to limite it to only those payments that fall into
the aging listed above.

Grace_tbl [Client] Grace_tbl [Fund] Grace_tbl [Account #] Grace_tbl [Grace
Date] Payment_tbl [Account #] Payment_tbl [Payment Date] Payment_tbl [Payment]
581 97 111111111 12/1/2004 111111111 9/29/2005 1,489.00
581 97 111111111 12/1/2004 111111111 2/1/2005 50.00
581 97 111111111 12/1/2004 111111111 2/18/2005 50.00
581 97 111111111 9/1/2004 111111111 9/29/2005 1,489.00
581 97 111111111 9/1/2004 111111111 2/1/2005 50.00
581 97 111111111 9/1/2004 111111111 2/18/2005 50.00
581 97 2222222222 12/1/2005 2222222222 9/4/2007 43.49
581 97 2222222222 12/1/2005 2222222222 9/4/2007 43.49
581 97 2222222222 12/1/2005 2222222222 8/28/2006 43.49
581 97 2222222222 12/1/2005 2222222222 8/7/2007 86.98
581 97 2222222222 12/1/2005 2222222222 10/9/2006 43.49
581 97 2222222222 12/1/2005 2222222222 5/1/2006 43.49
581 97 2222222222 12/1/2005 2222222222 12/31/2007 43.49
581 97 2222222222 12/1/2005 2222222222 3/22/2006 43.49
581 97 2222222222 12/1/2005 2222222222 4/4/2007 86.98
581 97 2222222222 12/1/2005 2222222222 11/30/2007 43.49
581 97 2222222222 12/1/2005 2222222222 11/5/2007 43.49
581 97 2222222222 12/1/2005 2222222222 6/5/2006 43.49
581 97 2222222222 12/1/2005 2222222222 2/6/2006 43.49
581 97 2222222222 12/1/2005 2222222222 1/2/2006 43.49
581 97 2222222222 12/1/2005 2222222222 7/23/2007 86.98
581 97 2222222222 12/1/2005 2222222222 11/6/2006 86.98
581 97 2222222222 12/1/2005 2222222222 12/4/2006 86.98
581 97 2222222222 12/1/2005 2222222222 7/13/2006 43.49
581 97 2222222222 12/1/2005 2222222222 7/28/2006 86.96
581 97 2222222222 12/1/2005 2222222222 1/25/2007 43.49
581 97 2222222222 12/1/2005 2222222222 2/28/2006 86.98

Thanks.
 
R

Renetta

Thank you so much.

SELECT AccountMasterFinalNew_tbl.Field11 AS Client,
AccountMasterFinalNew_tbl.Field12 AS Fund, AccountMasterFinalNew_tbl.Field13
AS [Account #], AccountMasterFinalNew_tbl.GraceEndingDate AS [Grace Date],
Payments_Final.[Loan Number], DateSerial(Left([Transaction
Date],4),Mid([Transaction Date],5,2),Right([Transaction Date],2)) AS
TransDate, Payments_Final.[Journal Date], Payments_Final.[Transaction Type],
Payments_Final.[Total Payment], Payments_Final.Balance
FROM AccountMasterFinalNew_tbl LEFT JOIN Payments_Final ON
AccountMasterFinalNew_tbl.Field13 = Payments_Final.[Loan Number];


bhicks11 via AccessMonster.com said:
Post the SQL code please. You get it in SQL view in the query editor.

Bonnie

http://www.dataplus-svc.com
Please help - I need to run a query between two tables

The two tables are linked by the
[Account#]. The date formate for both tables [GranceDate] and [PaymentDate]
are in Date/Time formate.

What I need is:

In the Grace_tbl return all accounts [Account#] along with their grace
ending date [GraceDate] and from the Payments_tbl return only those matching
accounts [Account#]with a [Payment] and [PaymentDate] range of 0-30, 31-60
and 61-90

Sample qry below - I need to limite it to only those payments that fall into
the aging listed above.

Grace_tbl [Client] Grace_tbl [Fund] Grace_tbl [Account #] Grace_tbl [Grace
Date] Payment_tbl [Account #] Payment_tbl [Payment Date] Payment_tbl [Payment]
581 97 111111111 12/1/2004 111111111 9/29/2005 1,489.00
581 97 111111111 12/1/2004 111111111 2/1/2005 50.00
581 97 111111111 12/1/2004 111111111 2/18/2005 50.00
581 97 111111111 9/1/2004 111111111 9/29/2005 1,489.00
581 97 111111111 9/1/2004 111111111 2/1/2005 50.00
581 97 111111111 9/1/2004 111111111 2/18/2005 50.00
581 97 2222222222 12/1/2005 2222222222 9/4/2007 43.49
581 97 2222222222 12/1/2005 2222222222 9/4/2007 43.49
581 97 2222222222 12/1/2005 2222222222 8/28/2006 43.49
581 97 2222222222 12/1/2005 2222222222 8/7/2007 86.98
581 97 2222222222 12/1/2005 2222222222 10/9/2006 43.49
581 97 2222222222 12/1/2005 2222222222 5/1/2006 43.49
581 97 2222222222 12/1/2005 2222222222 12/31/2007 43.49
581 97 2222222222 12/1/2005 2222222222 3/22/2006 43.49
581 97 2222222222 12/1/2005 2222222222 4/4/2007 86.98
581 97 2222222222 12/1/2005 2222222222 11/30/2007 43.49
581 97 2222222222 12/1/2005 2222222222 11/5/2007 43.49
581 97 2222222222 12/1/2005 2222222222 6/5/2006 43.49
581 97 2222222222 12/1/2005 2222222222 2/6/2006 43.49
581 97 2222222222 12/1/2005 2222222222 1/2/2006 43.49
581 97 2222222222 12/1/2005 2222222222 7/23/2007 86.98
581 97 2222222222 12/1/2005 2222222222 11/6/2006 86.98
581 97 2222222222 12/1/2005 2222222222 12/4/2006 86.98
581 97 2222222222 12/1/2005 2222222222 7/13/2006 43.49
581 97 2222222222 12/1/2005 2222222222 7/28/2006 86.96
581 97 2222222222 12/1/2005 2222222222 1/25/2007 43.49
581 97 2222222222 12/1/2005 2222222222 2/28/2006 86.98

Thanks.
Click to expand...
Click to expand...
 
R

Renetta

Yep, I have a table that list all accounts with a Grace Ending Date. FYI,
Grace Ending date reflets when an account falls into repayment status. And I
have a table that reflect all payments made from the beginning of time.
Approx. 200,000+. I need to ident. those accounts/payments that were made on
time. Meaning within 30, 60, 90 days. I though all I need is an expression
for each 0 to 30, 31 to 60, 61 to 90 and 91+ to get the results then force
the data into a cross table qry.

Lori
 
R

Renetta

Okay, I realized how messy the SQL looked so below is a new one. I cleaned
up the two tables pulling all payments, however I still need to run an age
using the AccountMasterFinalNew_tbl.Grace_EndingDate against the
Payment.[Payment_Trans Date].

SELECT AccountMasterFinalNew_tbl.Grace_Name,
AccountMasterFinalNew_tbl.Grace_Client, AccountMasterFinalNew_tbl.Grace_Fund,
AccountMasterFinalNew_tbl.[Grace_Account #],
AccountMasterFinalNew_tbl.Grace_EndingDate, Payment.Payment_Fund,
Payment.[Payment_Loan Number], Payment.[Payment_Trans Date],
Payment.[Payment_Transaction Type], Payment.[Payment_Total Payment],
Payment.Payment_Balance
FROM AccountMasterFinalNew_tbl LEFT JOIN Payment ON
AccountMasterFinalNew_tbl.[Grace_Account #] = Payment.[Payment_Loan Number];

Does this make more sense?

Lori

bhicks11 via AccessMonster.com said:
Hi Renetta,

If I understant correctly, you can run another query against the query below
and limit the results as you mentioned.

Bonnie

http://www.dataplus-svc.com
Thank you so much.

SELECT AccountMasterFinalNew_tbl.Field11 AS Client,
AccountMasterFinalNew_tbl.Field12 AS Fund, AccountMasterFinalNew_tbl.Field13
AS [Account #], AccountMasterFinalNew_tbl.GraceEndingDate AS [Grace Date],
Payments_Final.[Loan Number], DateSerial(Left([Transaction
Date],4),Mid([Transaction Date],5,2),Right([Transaction Date],2)) AS
TransDate, Payments_Final.[Journal Date], Payments_Final.[Transaction Type],
Payments_Final.[Total Payment], Payments_Final.Balance
FROM AccountMasterFinalNew_tbl LEFT JOIN Payments_Final ON
AccountMasterFinalNew_tbl.Field13 = Payments_Final.[Loan Number];
Post the SQL code please. You get it in SQL view in the query editor.
Click to expand...
[quoted text clipped - 49 lines]
Thanks.
Click to expand...
Click to expand...
Click to expand...
 
R

Renetta

Okay, I wrote an expression:

Days Paid: DateDiff("d",[Grace_EndingDate],[Payment_Trans Date])

I got the numbers now how do I write the expression to break out the 0-30,
31-60, 61-90

Thanks.
 
R

Renetta

This is what I was looking for.

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

Maybe benif. to others.

bhicks11 via AccessMonster.com said:
Now that you have a number (did you look at the resultant data set) group it
in a report.

Bonnie

http://www.dataplus-svc.com
Okay, I wrote an expression:

Days Paid: DateDiff("d",[Grace_EndingDate],[Payment_Trans Date])

I got the numbers now how do I write the expression to break out the 0-30,
31-60, 61-90

Thanks.
Please help - I need to run a query between two tables
Click to expand...
[quoted text clipped - 43 lines]
Thanks.
Click to expand...
Click to expand...
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

WCG Stats Friday 01 September 2023 3
Advanced problem using auto-number and datediff to find readmissio 1
WCG Stats Tuesday 07 February 2023 3
Date Expressions & Calculations 3
query flat file data for most recent data 3
WCG Stats Monday 18 January 2021 1
Need query help 14
WCG Stats Saturday 20 February 2021 1

Top