How to select records when date is less than 30 days apart

[Therat]

I have a large database filled with customer records. Some customers come one time. Other customers come 50 times at year. I want to find all customers that have records that are less than 30 days apart so I can ultimately see the types of products they buy. How do I write the query?

I attached a picture of a sample database if it helps. I am open to any suggestions! FYI: the 30 day (+/-) field doesn't exist but I would like the query results to build it.


Thanks,

 

[pbaldy]

You could use this method to get the previous visit, then DateAdd() to find the number of days difference:

http://allenbrowne.com/subquery-01.html#AnotherRecord

 

[plog]

In general, you use the DMax function (http://www.techonthenet.com/access/functions/domain/dmax.php) and DMin function (http://www.techonthenet.com/access/functions/domain/dmin.php) to get the closest dates (next higher and next lower) to the date of each record. Then you use the DateDiff function (http://www.techonthenet.com/access/functions/date/datediff.php) to find the difference between the current record's date and its Dmin/Dmax, if either of those is <=30 you flag it.

Give it a shot, work at it incrementally and post back here any issues you encounter. have

 

[Therat]

I am not a heavy SQL user and need a little more hand holding. Can you show a picture of the approach? Or, be as simple as possible...

I tried Dmax/min but am not sure if/how to use it to compare to each customer's closest prior and post record. If I could figure that out, then I could do a date diff. Could you look at my picture and help me write the criteria?

 

[plog]

You didn't provide a table name, so I assumed the data you posted was from 'YourTableNameHere'. You will need to change every appearnce of that in the below code to the actual table/query name.

SELECT *, DMin("[DischDate]", "YourTableNameHere", "[Customer ID #]='" & [Cusomter ID #] & "' AND DischDate>" & [DischDate])) AS NextVisitDate
FROM YourTableNameHere

That will create a column title 'NextVisitDate' which will show the visit that occurs for that Customer after the one you are viewing. I probably screwed up the Date criteria, so when it doesn't work, look into fixing that.

 

[Therat]

Re: Almost there... IDEAL Layout Included...

This didn't work yet. I've attached a picture of the sample result of the NextVisitDate criteria you provided and also a picture of my ideal layout. Can I get your help with the criteria for both LastVisitDate and NextVisitDate? Once I have these two columns, I can easy do a Datediff and IIF statement to arrive at my 30 Day Flag column.

 

[plog]

Did you put the date criteria on the end of it? What's the SQL look like that you have?

 

[Therat]

Code:
SELECT DMin("[DischDate]","Customer30DayFlag","[CustomerID]='" & [CustomerID] & "' AND DischDate>" & [DischDate]) AS NextVisitDate, *
FROM Customer30DayFlag;

 

[plog]

Try this:

SELECT DMin("[DischDate]","Customer30DayFlag","[CustomerID]='" & [CustomerID] & "' AND [DischDate]>#" & [DischDate] & "#") AS NextVisitDate, *
FROM Customer30DayFlag;


I think you need # after the variable when using dates. I always screw that part up.

 

[namliam]

There is no really good way of doing this other than with a DCount, which kindoff is a bad idea to use that in a query since it will slow down the results significantly

You want to do it recursively? i.e. in your samplle you are highlighting both 13 oct and 10 Nov ....

 

[plog]

There is no really good way of doing this other than with a DCount

My method won't use a Dcount. It will use a Dmin and a Dmax, which are still a Domain function and will be slow, but this thing is going to be slow no matter what. He's got to compare a lot of records to a lot of other records.

Stay with me Therat, we will get there.

 

[namliam]

I think the best way to go about this is "on data entry"
To find the previous record (one time) and tag this record now added as "revisit"

Should be relatively quick vba if you just want to hard-update the (now historic) table which will likely be faster than the query itself, using Dcount or dmax/dmin etc.

dim rst as dao.recordset
dim prevDate as date
dim PrevFlagged as boolean
prevdate = #01/01/1900#
PrevFlagged = false
set rst = currentdb.openrecordset("Select * from yourtable order by yourfields")
do while not rst.eof
    if rst!VisitDate - prevdate <30 then
        if not prevflagged then
            rst.MovePrevious ' Make sure the previous is flagged first
        endif
        rst.edit
        rst!Revisit30Days = true 
        rst.update
        PrevFlagged = True
    else
        PrevFlagged = false
    endif
    prevdate = rst!Visitdate
    rst.movenext
loop

*** Disclaimer ***
Complete aircode, thrown together on the forum, untested and without all that good stuff.