date ranges

[Renetta]

I am looking to write an expression, but I'm not quite sure how to do this.
What I need is:

return all records with a "Trnas Date" received within 30, 60, 90 day of the
"Grance Ending Date"

Thanks.

 

[Bob Barrows [MVP]]

I am looking to write an expression, but I'm not quite sure how to do
this. What I need is:

return all records with a "Trnas Date" received within 30, 60, 90 day
of the "Grance Ending Date"

I'm not sure how to interpret this. Some questions I have are:

Are [Trnas Date] and [Grance Ending Date] both fields in the same table?
Are they both Date/Time fields?
Do you want the records returned to be identified as to which time frame
they were received in? A record returned within 30 days is also a record
returned within 90 days, isn't it? So are you asking to identify records
returned in 0-30,31-60 and 61-90 day time frames? How do you wish this to be
displayed in the results?

What you should do is show us a few rows of sample data in tabular format,
then show us the desired results that you wish to obtain from that sample
data, also in tabular format (queries return results in tabular format, so
it really helps when designing a query to see how you want that result to
look).

 

[Renetta]

Yes, 2 seperate tables linked together. And yes I am looking for 0-30, 31-60
and 61-90. The dates are in a date formate of 00/00/00.

the Grace Ending Table list all accounts with a Grace Ending Date and the
other table list all accounts with payments and dates. I need to pull all
payments within the 0-30, 31-60 and 61-90 of the Grace Ending Date.

Thanks for such a quick response. Your assistance is so appreciated!

 

[Renetta]

If you just show me how to do the 0-30 day I can do the rest. I'm thinking
I'll need 3 seperate expressions and then complete a cross tab qry for all.
Thanks again.

 

[Bob Barrows [MVP]]

Yes, 2 seperate tables linked together.

Well, then, the answer to my question would be "No", wouldn't it?

And yes I am looking for
0-30, 31-60 and 61-90. The dates are in a date formate of 00/00/00.

Date format is irrelevant, unless the dates are stored in Text fields. Are
they?
Please tell me the data types, not the data formats. I suspect they are
Date/Time fields, but would prefer you to confirm this instead of relying on
a guess.

the Grace Ending Table list all accounts with a Grace Ending Date and
the other table list all accounts with payments and dates. I need to
pull all payments within the 0-30, 31-60 and 61-90 of the Grace
Ending Date.

OK, but I still don't know how you want the results to appear. I could make
a guess based on how I might like to see such results, but that risks
getting involved in a long drawn-out exchange of
hows-this-no-that's-not-what-I-want posts. Let's forestall this right now.
Please provide a few rows of sample data in tabular format - you don't have
to show all the fields: in fact, I would prefer you don't - just show a
couple fields (preferably the key fields) along with the two "date" fields.

Then show, again in tabular format, the results you wish to obtain from the
proposed query. Do not describe the results (which is all you have done up
to this point) without also _showing_ them.

I'm not trying to be mean: I am trying to help you in as efficient a manner
as possible. I will say that going through this exercise will show you the
first step in thinking a problem like this through to a successful
conclusion.

 

[Bob Barrows [MVP]]

Again, that depends on how you want the results to be returned. Your mention
of "cross tab" makes me thankful I did not waste any time building based on
my guess as to how the results should look.

Please read my previous replies and show me sample data and desired results.

 

[Renetta]

Sorry, two tables Grace and Payment. 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

Account # Grace Pymnt 0-30 31-60 61-90
xxxxxxxxx xx/xx/xx xx/xx/xx ##.## ##.## ##.##
xx/xx/xx xx/xx/xx ##.##

I hope this helps.

 

[Bob Barrows [MVP]]

Sorry, two tables Grace and Payment. 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

Account # Grace Pymnt 0-30 31-60 61-90
xxxxxxxxx xx/xx/xx xx/xx/xx ##.## ##.## ##.##
xx/xx/xx xx/xx/xx ##.##

We're almost there. All that is left is to tell me what those ##.### things
represent.
Again, a few rows of sample data would be very helpful.

 

[Renetta]

Grace_tbl [Grace Date] Payment_tbl [Loan Number] Payment_tbl
[TransDate] Payment_tbl [Journal Date] Payment_tbl [Transaction
Type] Payment_tbl [Total Payment] Payment_tbl [Balance]
12/1/2004 111111111 9/29/2005 20050930 Cash Payment 1,489.00 (10.04)
12/1/2004 111111111 2/1/2005 20050228 Cash Payment 50.00 1,481.25
12/1/2004 111111111 2/18/2005 20050228 Cash Payment 50.00 1,431.25
9/1/2004 111111111 9/29/2005 20050930 Cash Payment 1,489.00 (10.04)
9/1/2004 111111111 2/1/2005 20050228 Cash Payment 50.00 1,481.25
9/1/2004 111111111 2/18/2005 20050228 Cash Payment 50.00 1,431.25
12/1/2005 2222222222 9/4/2007 20070930 Cash Payment 43.49 3,310.92
12/1/2005 2222222222 9/4/2007 20070930 Cash Payment 43.49 3,310.92
12/1/2005 2222222222 8/28/2006 20060831 Cash Payment 43.49 3,770.09
12/1/2005 2222222222 8/7/2007 20070831 Cash Payment 86.98 3,340.49
12/1/2005 2222222222 10/9/2006 20061031 Cash Payment 43.49 3,742.31
12/1/2005 2222222222 5/1/2006 20060531 Cash Payment 43.49 3,923.00
12/1/2005 2222222222 12/31/2007 20071231 Cash Payment 43.49 3,221.47
12/1/2005 2222222222 3/22/2006 20060331 Cash Payment 43.49 3,950.03
12/1/2005 2222222222 4/4/2007 20070430 Cash Payment 86.98 3,500.34
12/1/2005 2222222222 11/30/2007 20071130 Cash Payment 43.49 3,251.41
12/1/2005 2222222222 11/5/2007 20071130 Cash Payment 43.49 3,281.23
12/1/2005 2222222222 6/5/2006 20060630 Cash Payment 43.49 3,895.86
12/1/2005 2222222222 2/6/2006 20060228 Cash Payment 43.49 4,047.07
12/1/2005 2222222222 1/2/2006 20060131 Cash Payment 43.49 4,073.59
12/1/2005 2222222222 7/23/2007 20070731 Cash Payment 86.98 3,413.25
12/1/2005 2222222222 11/6/2006 20061130 Cash Payment 86.98 3,686.40
12/1/2005 2222222222 12/4/2006 20061231 Cash Payment 86.98 3,614.78
12/1/2005 2222222222 7/13/2006 20060731 Cash Payment 43.49 3,868.60
12/1/2005 2222222222 7/28/2006 20060731 Cash Payment 86.96 3,797.76
12/1/2005 2222222222 1/25/2007 20070131 Cash Payment 43.49 3,586.35
12/1/2005 2222222222 2/28/2006 20060228 Cash Payment 86.98 3,976.95

 

[Renetta]

Here's the actual data sample - ignore the prior one. Thanks again.

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

 

[Bob Barrows [MVP]]

Here's the actual data sample - ignore the prior one. Thanks again.

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

And if the query works as desired, what should the results look like, based
on this sample data?

 

[Bob Barrows [MVP]]

Here's the actual data sample - ignore the prior one. Thanks again.

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

Here is a start. I'm bowing out of this thread.

SELECT q.AcctNum, q.GraceDate, q.PayDate,
q.Payment, DateDiff("d",[gracedate],[paydate]) AS PayDateDiff
FROM QueryThatJoinsTables AS q
WHERE (((DateDiff("d",[gracedate],[paydate]))<91));

 

[Renetta]

well we could push the other payments over into a 91+ column make it a
complete aging. That would work. That way I capture everything incase my
boss ends up wanting to see that detail as well. It started out only wanting
those within 30 day!

Lori

Here's the actual data sample - ignore the prior one. Thanks again.

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

And if the query works as desired, what should the results look like, based
on this sample data?


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

 

[Renetta]

a total aging to answer your question. Thanks again.

Here's the actual data sample - ignore the prior one. Thanks again.

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

And if the query works as desired, what should the results look like, based
on this sample data?


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"