1 table, 2 columns calculate consecutive days (1 Viewer)

[Satin]

Good Afternoon,

I currently have a table with Customers with the following columns:

CustomerName
StartDate
EndDate
ResNumber - this is unique to each stay

This table will have hundreds of rows, as each customer has the ability to have multiples start and end dates and the days between can arranges from 1 to 30+ days. Each month I need to know who has stayed 30 days or longer. I will import this data monthly I was able to do this with multiple queries (5 to be exact). 1 for if 1 stay is 30+ days or longer, if 2 stays are 14 days or longer, if 3 stays are 10 days or longer, if 4 stays for 8 days or longer, and if 5 or more stays are 7 days or longer.

I figure I need to run a query off each query (and that is ok) for the second part. I need to see only those who have visited consecutively for 30 or more days.

I have uploaded my test access to see if this is even possible or will I have to manually check for consecutive days. Currently I do this in excel with different formulas but I have to still look at it manually for the consecutive days. I am trying to see if Access can make it more mainstream or faster.

I will have the next 2 days off and wont check any responses please don't be discouraged with me if I don't respond quickly. Any input is greatly appreciated.

Thank you,
Satin

 

[theDBguy]

Hi. Not sure if this is what you mean, but maybe take a look at this article.

A practical use of a Cartesian Query

As promised in my previous blog on Cartesian Query , I will now try to describe in detail one example of a practical use of a Cartesian Quer...

thedbguy.blogspot.com

 

[Satin]

Thank you. I will read up on Cartesian Query and see if this may be what I can use.

 

[June7]

Not clear to me what you are trying to accomplish.

Guest Eight, Eight has a 35-day visit out of 8 visits. This record does not show in query 1 but it is in query 5orMore. So these 5 queries only consider how many visits, not how long they are?

If you want all records where visit is 30+ days, that should be possible with one query.

 

[Satin]

Not clear to me what you are trying to accomplish.

Guest Eight, Eight has a 35-day visit out of 8 visits. This record does not show in query 1 but it is in query 5orMore. So these 5 queries only consider how many visits, not how long they are?

If you want all records where visit is 30+ days, that should be possible with one query.

I was trying to get it all in 1 query but I just couldn't figure out how. I need all customers who stayed 30+ consecutive days no matter if they have 1 or 20 reservations. However, some customers can have 5 stays but have gaps so they are not considered staying 30+ days. I hope this makes sense.

 

[June7]

Yeah, took another look at your data and realized you might be wanting that. That will likely require a VBA custom function.

 

[Satin]

Yeah, took another look at your data and realized you might be wanting that. That will likely require a VBA custom function.

I will look into this too. I would continue it in Excel but I am trying to teach someone else how to do it and they don't understand excel and formulas. So it makes it more challenging for us both. But if I could set access up where all they have to do is import and run queries I feel it would be easier and if they "break" the access. I can resend them mine and tada they can run it again.

 

[June7]

Guest Four records show overlapping periods - OUT on 3/9 and next record IND on 3/7 - why? Was this an error or will this be valid data?
Consider:

Function ConCnt(strGuest As String) As Integer
Dim rs As DAO.Recordset, dteOUT As Date, intDays As Integer
Set rs = CurrentDb.OpenRecordset("SELECT GuestName, IND, OUT, DateDiff('d',[IND],[OUT]) AS NuDays " & _
                                 "FROM TSW WHERE [Type]='Stay' AND GuestName='" & strGuest & "' ORDER BY IND")
If Not rs.EOF Then
    ConCnt = rs!nudays
    Do While Not rs.EOF
        dteOUT = rs!OUT
        rs.MoveNext
        If Not rs.EOF Then
            If dteOUT = rs!IND Then
                ConCnt = ConCnt + rs!nudays
            End If
        End If
    Loop
End If
End Function

Call in query:
SELECT DISTINCT GuestName, ConCnt([GuestName]) AS DayCount
FROM TSW
WHERE (((ConCnt([GuestName]))>29));

 

[arnelgp]

you have "many" info showing on your query (1-4).
when you only required: guess name, no of times visited, total duration of stay.
see Query1.
good luck with your test.

 

[Satin]

Guest Four records show overlapping periods - OUT on 3/9 and next record IND on 3/7 - why? Was this an error or will this be valid data?
Consider:

Function ConCnt(strGuest As String) As Integer
Dim rs As DAO.Recordset, dteOUT As Date, intDays As Integer
Set rs = CurrentDb.OpenRecordset("SELECT GuestName, IND, OUT, DateDiff('d',[IND],[OUT]) AS NuDays " & _
                                 "FROM TSW WHERE [Type]='Stay' AND GuestName='" & strGuest & "' ORDER BY IND")
If Not rs.EOF Then
    ConCnt = rs!nudays
    Do While Not rs.EOF
        dteOUT = rs!OUT
        rs.MoveNext
        If Not rs.EOF Then
            If dteOUT = rs!IND Then
                ConCnt = ConCnt + rs!nudays
            End If
        End If
    Loop
End If
End Function

Call in query:
SELECT DISTINCT GuestName, ConCnt([GuestName]) AS DayCount
FROM TSW
WHERE (((ConCnt([GuestName]))>29));

I will absolutely try this. unfortunately, the dates can be all messed up like they are in the example.

 

[Satin]

you have "many" info showing on your query (1-4).
when you only required: guess name, no of times visited, total duration of stay.
see Query1.
good luck with your test.

thank you so much.

 

[bastanu]

@june
I think this line If dteOUT = rs!IND Then should read If dteOUT >= rs!IND Then to allow for the bad data example for guest four.

Cheers,

 

[June7]

@bastanu
Agree, which is why I asked OP if data is valid. Unfortunately, overlapping periods complicates because this results in wrong continuous days count. Overlapping dates will be counted twice. My code does not deal with that. Now I am thinking my code actually counts the end/start date twice.