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.
 
B

bhicks11 via AccessMonster.com

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...
 
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...
 
B

bhicks11 via AccessMonster.com

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]
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
 
B

bhicks11 via AccessMonster.com

Sorry Renetta,

To make sure I understand: if there is a grace ending date (what is the
field name) you want to show how many days difference between Grace Ending
Date and what is the name of the other field?

Bonnie

http://www.dataplus-svc.com
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
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...
 
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.
 
B

bhicks11 via AccessMonster.com

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...
 
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

Advanced problem using auto-number and datediff to find readmissio 2
Left Join 0
How to build subquery on aggregate query 0
Need query help 14
query flat file data for most recent data 3
no. of months worked between two dates, etc... 0
Keep unique values 1
Sequence number 6

Top