How create ranges for multiple dates associated with single record or AccountID in a table or query 1

[MrHeynow]

Is there a way to create ranges for multiple dates associated with single record or AccountID from a query/table so that one could later evaluate a date of another table or query to to say that AccountID 100000001004 at 7/18/2017 10:11:00 AM was AssignedTo 3632.

Three columns in the current table

AccountID AssgDate AssignedTO
100000001004 10/30/2017 12:10:00 PM -1
100000001004 11/7/2016 3:28:00 PM -1
100000001004 7/18/2017 10:05:00 AM 3632
100000001004 7/18/2017 10:13:00 AM 95590
100000001004 7/18/2017 11:29:00 AM -1
100000001004 7/25/2017 3:16:00 PM 3551
100000001004 9/9/2016 11:29:00 AM 3614
100000001104 11/7/2016 3:28:00 PM -1
100000001104 12/11/2017 3:09:00 PM -1
100000001104 7/18/2017 10:05:00 AM 3632
100000001104 7/18/2017 10:13:00 AM 95590
100000001104 7/18/2017 11:29:00 AM -1
100000001104 7/25/2017 3:16:00 PM 3551
100000001104 9/9/2016 11:34:00 AM 38189

I have had no luck in googling and securing an answer how to complete my quest, comments suggestions welcomed

 

[MajP]

I assume you mean

SELECT AccountID, AssgDate, AssignedTO, FROM All_Assgnd_1 ORDER BY AccountID, AssgDate

To make it simpler build the above query and save it as qryAssgDates. make sure it runs. Then change it to simply
StrSql = "qryAssgDates"

also limit qryAssgDates to a few records with a where statement so you do not try to test on 150k records.

Another way to test it would be to add this line of code

CurrentDb.Execute StrSql
exit sub
Loop
End Sub

That way you can test if it creates one record. If it does, then you can remove the "exit sub" to allow it to loop

 

[MrHeynow]

Thanks so much [bold] MajP[/bold]

Okay so I did create the suggested query and limited it to 100 records and with the code pasted at the end of this post, it returns 16 records but the first time it was and the last time assigned not each time it was assigned ...Rob

For example for these AccountId's
[pre]
AccountID AssgDate AssignedTO
100000000904 11/7/2016 3:28:00 PM -1
100000000904 4/20/2017 3:14:00 PM 3551
100000000904 5/16/2017 1:06:00 PM 3593
100000000904 7/18/2017 10:05:00 AM 3632
100000000904 7/18/2017 10:13:00 AM 95590
100000000904 9/5/2017 4:24:00 PM -1
100000000904 9/9/2016 11:31:00 AM 36678
100000001004 10/30/2017 12:10:00 PM -1
100000001004 11/7/2016 3:28:00 PM -1
100000001004 7/18/2017 10:05:00 AM 3632
100000001004 7/18/2017 10:13:00 AM 95590
100000001004 7/18/2017 11:29:00 AM -1
100000001004 7/25/2017 3:16:00 PM 3551
100000001004 9/9/2016 11:29:00 AM 3614
100000001104 11/7/2016 3:28:00 PM -1
100000001104 12/11/2017 3:09:00 PM -1
100000001104 7/18/2017 10:05:00 AM 3632
100000001104 7/18/2017 10:13:00 AM 95590
100000001104 7/18/2017 11:29:00 AM -1
100000001104 7/25/2017 3:16:00 PM 3551
[/pre]

But only returns 3 records with the first AssignedTo and the first StartRange and the last EndRange
[pre]
AccountID StartRange EndRange AssignedTo
100000000904 11/7/2016 3:28:00 PM 4/20/2017 3:14:00 PM -1
100000001004 10/30/2017 12:10:00 PM 11/7/2016 3:28:00 PM -1
100000001104 11/7/2016 3:28:00 PM 12/11/2017 3:09:00 PM -1
[/pre]

Code [pre]
Public Sub FillRanges()
Dim RS_Source As DAO.Recordset
Dim StrSql As String
Dim NextAcct As String 'modify if this is a number
Dim CurrentAcct As String
Dim StartRange As Date
Dim EndRange As Date
Dim AssignedTo As Long
'Need to make sure in proper order of Account and assign date
StrSql = "qryAssgDates"
'open forward only for performance
Set RS_Source = CurrentDb.OpenRecordset(StrSql, dbOpenForwardOnly)

Do While Not RS_Source.EOF
CurrentAcct = RS_Source!AccountID
StartRange = RS_Source!AssgDate
AssignedTo = RS_Source!AssignedTo
'move to next record
RS_Source.MoveNext
'if no more records
If RS_Source.EOF Then
EndRange = Now
NextAcct = CurrentAcct
Else
'an account change
NextAcct = RS_Source!AccountID
If NextAcct <> CurrentAcct Then
EndRange = Now
Else
EndRange = RS_Source!AssgDate
End If
End If
StrSql = "Insert into TblRanges (AccountID,StartRange,EndRange,AssignedTo) values ('" & CurrentAcct & "',"
StrSql = StrSql & SQLDate(StartRange) & "," & SQLDate(EndRange) & "," & AssignedTo & ")"
Debug.Print StrSql
CurrentDb.Execute StrSql
Loop
End Sub
[/pre]

 

[MajP]

You set AccountID as a primary key or indexed it not to allow duplicates, therefore it has to be unique. Every other record for that accountID fails after the first one. Unlike running an update query from the user interface, running from code will not provide you an error if the update does not go through because of violating a constraint.

 

[MrHeynow]

I did? How ? and can I alter that?

 

[MajP]

You built a table TblRanges correct?
AcctID
StartRange
EndRange
AssignedTo

Is AcctID a primary key? Does it allow duplicates?

 

[MrHeynow]

I see, ok I remedied the primary key issue [bold]thanks[/bold] now I get an error
[pre]

Run-Time error'94':
Invalid use of Null

Which happens at "AssignedTo = RS_Source!AssignedT" of this snippet of code

Do While Not RS_Source.EOF
CurrentAcct = RS_Source!AccountID
StartRange = RS_Source!AssgDate
AssignedTo = RS_Source!AssignedTo
'move to next record
RS_Source.MoveNext
'if no more records
If RS_Source.EOF Then
EndRange = Now
NextAcct = CurrentAcct
[/pre]

It did get to 38,203 records prior to stopping thought!

 

[MajP]

you may need to add some error checking. It appears you have records without AssignedTo. So what do you want to do in that case?

AccountID	AssgDate	        AssignedTO
100000000904	11/7/2016 3:28:00 PM	-1
100000000904	4/20/2017 3:14:00 PM

I would find these first and fix them. Query your records for where AssignedTO is Null. Or your code could assign a bogus ID
AssignedTo = NZ(RS_Source!AssignedTo,9999)

This code will also bomb if your AccountID or AssgDate is null.

 

[MrHeynow]

[bold]Majp[/bold]

Thank so very much for your help! Testing for a bit but looking good...