LATEST date older than 45 days....

[BobNTN]

I need to find the LATEST date in field Recdate that is 45 days or older in a query. I have tried <Date()-45, etc. but it returns all dates not just the last one. Does this require a range of dates to do this ? If not, how would I id the last date input ?
Thanks

 

[pbaldy]

Try

SELECT Max(DateField)
FROM TableName
WHERE DateField < Date()-45

 

[BobNTN]

As usual, Thanks Paul.

 

[BobNTN]

I got to thinking about this and now have a question for opinions.
Based on the above, this would be showing only actual received dates of payments that are 45 days or more old. What IF there was no payment at all by a customer ? Shouldn't that be listed also ?
Opinions ?

 

[gemma-the-husky]

this will only return the highest date that is at least 45 days old

if there is no matching date (or no date at all) it will return an empty result

 

[BobNTN]

If I only have the Payments table open in the query, it won't show non-payments since they aren't there. I would have to also open the customer table and reference the CID in it. Correct ?

Just tried it, it ONLY shows dates 45 days or older but does not list non-payments.

Here is the sql

>SELECT TblCustInfo.CID, TblCustInfo.Name, Max(TblPayments.Recdate) AS MaxOfRecdate, TblPayments.Creditamt
FROM TblCustInfo LEFT JOIN TblPayments ON TblCustInfo.CID = TblPayments.CID
WHERE (((TblPayments.Recdate)<Date()-45))
GROUP BY TblCustInfo.CID, TblCustInfo.Name, TblPayments.Creditamt;<

 

[BobNTN]

If I only have the Payments table open in the query, it won't show non-payments since they aren't there. I would have to also open the customer table and reference the CID in it. Correct ?

Just tried it, it ONLY shows dates 45 days or older but does not list non-payments.

Here is the sql

SELECT TblCustInfo.CID, TblCustInfo.Name, Max(TblPayments.Recdate) AS MaxOfRecdate, TblPayments.Creditamt
FROM TblCustInfo LEFT JOIN TblPayments ON TblCustInfo.CID = TblPayments.CID
WHERE (((TblPayments.Recdate)<Date()-45))
GROUP BY TblCustInfo.CID, TblCustInfo.Name, TblPayments.Creditamt;

In fact, this shows ALL payment dates older than 45 days for each customer and does not list any with NO payments. I tried "Last" instead of Max but does the same thing.

Any ideas how to list only the last payment and all non-payments ?

 

[gemma-the-husky]

i see what you mean

you need another query

--------
stasrt another query with the custometr table, and this maxdate query, and join the customer id's together.

right click the join and select "all customers and only ... " - this should give a left join (at least i think its a left join - i rarely look at the SQL)- with an arrow form the customers table to the query

NOW you can use THIS query to get waht you want

 

[BobNTN]

i see what you mean

you need another query

--------
stasrt another query with the custometr table, and this maxdate query, and join the customer id's together.

right click the join and select "all customers and only ... " - this should give a left join (at least i think its a left join - i rarely look at the SQL)- with an arrow form the customers table to the query

NOW you can use THIS query to get waht you want

tried that
still shows ALL payments beyond 45 days

 

[BobNTN]

I think the problem is what I am needing from two different tables PLUS a query to do what I need.

TblCustInfo
I need CID and Name

TblPayments
I need RecDate(from query) and Creditamt

Then in a query or queries
I need the LAST payment date (Recdate) 45 days or more ago - ONLY the last and listing any CID that has NO payment at all.

Does this make sense ?

 

[gemma-the-husky]

i didnt realise you hadnt sorted that bit yet, to get just the last payment

is your query

SELECT TblCustInfo.CID, TblCustInfo.Name, Max(TblPayments.Recdate) AS MaxOfRecdate, TblPayments.Creditamt
FROM TblCustInfo LEFT JOIN TblPayments ON TblCustInfo.CID = TblPayments.CID
WHERE (((TblPayments.Recdate)<Date()-45))
GROUP BY TblCustInfo.CID, TblCustInfo.Name, TblPayments.Creditamt;

visual, or a sql string

-----
if its visual it should be easy to play around with unitl you get what you want

 

[BobNTN]

sql I think - from the query design.

 

[Brianwarnock]

It might be easier if we had your Db, but try changing your WHERE clause to

HAVING (((Max(TblPayments.Recdate))<Date()-45) OR (((Max(TblPayments.Recdate)) Is Null))

this is usually after the Groupby I think.

Brian

 

[BobNTN]

It might be easier if we had your Db, but try changing your WHERE clause to

HAVING (((Max(TblPayments.Recdate))<Date()-45) OR (((Max(TblPayments.Recdate)) Is Null))

this is usually after the Groupby I think.

Brian

Here is what I have now:

SELECT TblCustInfo.CID, TblCustInfo.Name, Max(TblPayments.Recdate) AS MaxOfRecdate
FROM TblCustInfo LEFT JOIN TblPayments ON TblCustInfo.CID = TblPayments.CID
GROUP BY TblCustInfo.CID, TblCustInfo.Name
HAVING (((Max(TblPayments.Recdate))<Date()-45 Or (Max(TblPayments.Recdate)) Is Null));

This gives all the nulls or no payment customers but only the LAST payment of the first record in TblPayments
I've got 3000 records in the db - I'll have to clean it out and zip it.

 

[Brianwarnock]

OK took a longer think on this and realised that in addressing the Nulls had made a silly mistake the SQL should be

SELECT TblCustInfo.CID, TblCustInfo.Name, Max(TblPayments.Recdate) AS MaxOfRecdate
FROM TblCustInfo LEFT JOIN TblPayments ON TblCustInfo.CID = TblPayments.CID
WHERE ((TblPayments.Recdate)<Date()-45 Or (TblPayments.Recdate) Is Null)
GROUP BY TblCustInfo.CID, TblCustInfo.Name;

Brian

 

[namliam]

Make an actual subselect, which is kindoff hard and messy....

or do this in a couple of steps... To make it easy...
Query1
Get the max date per customer something like:

Select CID, Max(recdate) as MaxRecDate
from tblPayments
where TblPayments.Recdate<Date()-45

Query2
Join back Query1 to your tblPayments to pick up the actual payments.
Which goes something like so:

select <insert needed fields from tblPayments>
From tblPayments Join Query1 on tblPayments.CID = Query1.CID
                            and tblpayments.recdate = query1.MaxRecdate

Query3
Join query2 to tblCustInfo to get the proper recordset.
Something like:

Select <Needed fields for the output>
From tblCustInfo left join Query2 on tblCustInfo.CID = Query2.CID

Brian's query goes broke on customers that do have payments but still within the 45 day period as the "tblPayments.Recdate" is not null, but rather >Date()-45.

An integrated -all in one- query would be messy, tho not impossible....

 

[Brianwarnock]

Brian's query goes broke on customers that do have payments but still within the 45 day period as the "tblPayments.Recdate" is not null, but rather >Date()-45.

An integrated -all in one- query would be messy, tho not impossible....

I see what you are saying, recdates >=date()-45 with payments but none before, therefore require a null return. I would do a simple select with the same where clause as my original and then take that in as the right hand side of the join of my query.

Brian