Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.
MrHeynow

MrHeynow

Technical User
Jan 17, 2009
47
US
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
 
Sort by date Sort by votes
Hi,

Your example has no Date/Time of 7/18/2017 10:11:00 AM for AccountID 100000001004.

How would anyone find it?

However, in your example there would be for AccountID 100000001004, Date/Time 7/18/2017 10:05:00 AM, AssignedTO 3632.

Don't know what you mean by, "ranges for multiple dates associated with single record". Any record has ONE Date/Time value.

However any specific AccountID may have multiple rows, like AccountID 100000001004, that has 7 rows.

So I don't understand what you need.


Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Upvote 0 Downvote
It would be nice if you would:
1. Format your data so it would be easier to see what you have:

[pre]
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
[/pre]
and said...
2. "Above is what I have, below is what I would like to create:"

(Show an example of your desired outcome based on the data above)


---- Andy

There is a great need for a sarcasm font.
 
Upvote 0 Downvote
Thanks for your comments Skip and Andy

Any AccountID can be assigned any number of times and there about 150k lines currently.I think I need to create ranges between the the each date and time and AccountID to help me to query whom an account was assigned at any given time.

I have two examples 100000001004 and 100000001104. The issue I'm wrestling with, there are other events that happen while the AccountID is assigned in which I would like to know to whom it was assigned when the event happened. Let's say a request or activity happened on AccountID 100000001004 at 7/18/2017 10:11:00 AM to whom was the account was assigned?

Three columns are present in the query, I cant seem paste as a table so I used <> separate

I would like be able to query select event's AccountIDs and select dates and times to establish to whom it was assigned when said events happened.... I hope this helps Rob

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




 
Upvote 0 Downvote
I think I need to create ranges between the the each date and time and AccountID to help me to query whom an account was assigned at any given time.
Click to expand...

No! You don’t need anthing other than your table and the criteria values to find who was assigned at any particular time.

If you are doing a query, then...
Code: 
Select AssignedTo
From [i]YourTableName[/i]
Where AccountID=“100000001004” And AssgDate=#7/18/2017 10:11:00 AM#;

It’s often the case, however, that an exact time is not available. In such cases you might use a criteria like this to show all times for a particular date...
Code: 
Select AssignedTo, AssgDate
From [i]YourTableName[/i]
Where AccountID=“100000001004” 
  And AssgDate=>#7/18/2017# And AssgDate<#7/19/2017#;



Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Upvote 0 Downvote
To format your data, use [tt][ignore][PRE} ... [/PRE] [/ignore][/tt]TGML tags

"a request or activity happened on AccountID [blue]100000001004[/blue] at [green]7/18/2017 10:11:00 AM[/green] to whom was the account was assigned?"

If your table is called Activity, you could do:
[tt]
Select AssignedTO
From Activity
Where AccountID = [blue]100000001004[/blue]
And AssgDate <= #[green]7/18/2017 10:11:00 AM[/green]#
Order By AssgDate DESC
[/tt]
And first record is what you want.


---- Andy

There is a great need for a sarcasm font.
 
Upvote 0 Downvote
i'm needing to query about 25K AccountID's with various dates and times against these 150k Assigned dates and times. And wanting to know to whom it was assigned to for each date and time. Rob
 
Upvote 0 Downvote
Your question is still vague.

Do you have a list of 25,000 distinct AccountIDs for which you have one or more Date/Times to find the AssignedTo? Or what?

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Upvote 0 Downvote
What is the answer to the question of who is assigned to AccountID 100000001004 at 7/18/2017 10:11:00 AM?

Code: 
[tt]100000001004	<>	7/18/2017 10:05	<>	3632
100000001004	<>	7/18/2017 10:13	<>	95590[/tt]

is the answer 3632 since the next record is not assigned until 10:13 to 95590?
 
Upvote 0 Downvote
What happens in vagueness, stays in vagueness!

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Upvote 0 Downvote
Yes and likely to grow, both are.

One table/query that has the accounts assigned to various AssignedTO's at various dates and times in one table/query (150K) Lets call it "All_Assgnd_1"

In another table/query that have AccountID's with an event with various Dates and times (25) lets call it "All_Act1_Events"

In which I need to know what was the AssignedTO based on "All_Assgnd_1" had it at the time the "All_Act1_Events" event(s)happened

Rob

 
Upvote 0 Downvote
MajP

Yes exactly, 3632, since the next record is not assigned until 10:13 to 95590. Rob
 
Upvote 0 Downvote
Code: 
Select aa1.AssignedTo, aa1.AssgDate, aa1.AccountID
From All_Assgnd_1 aa1, All_Act_Events aae
Where aa1.AccountID=Aae.AccountID
  And aa1.AssgDate=Aae.aae.AssgDate

You might also post an example of All_Assgnd_1 and All_Act_Events with the results that you expect from your example.

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Upvote 0 Downvote
I may be interpreting this differently. But If the range is that time between each record then and the assigned to of the range is the starting assignedto
Code: 
SELECT 
 A.AcctID, 
 A.AssignedTo, 
 A.AssgnDate AS StartRange, 
(Select min (b.AssgnDate) as EndRange from Acctinfo as B where A.acctID = b.acctID and B.assgnDate > a.assgnDate) AS EndRange
FROM 
 acctinfo AS A
ORDER BY 
 A.AcctID, A.AssgnDate;

This give me the results
Code: 
[tt]AcctID	AssignedTo	StartRange	EndRange
100000001004	3614	9/9/2016 11:29	11/7/2016 3:28:00 PM
100000001004	-1	11/7/2016 15:28	7/18/2017 10:05:00 AM
100000001004	3632	7/18/2017 10:05	7/18/2017 10:13:00 AM
100000001004	95590	7/18/2017 10:13	7/18/2017 11:29:00 AM
100000001004	-1	7/18/2017 11:29	7/25/2017 3:16:00 PM
100000001004	3551	7/25/2017 15:16	10/30/2017 12:10:00 PM
100000001004	-1	10/30/2017 12:10	
100000001104	38189	9/9/2016 11:34	11/7/2016 3:28:00 PM
100000001104	-1	11/7/2016 15:28	7/18/2017 10:05:00 AM
100000001104	3632	7/18/2017 10:05	7/18/2017 10:13:00 AM
100000001104	95590	7/18/2017 10:13	7/18/2017 11:29:00 AM
100000001104	-1	7/18/2017 11:29	7/25/2017 3:16:00 PM
100000001104	3551	7/25/2017 15:16	12/11/2017 3:09:00 PM
100000001104	-1	12/11/2017 15:09	[/tt]

So for 004 then 3632 owns any records for times between 10:05 and 10:13.
So you would use the above query call it qryRanges with your other table that has accountIDs and times. Join the other table by accountID and the where clause would be "where someTimeField between qryRanges.StartRange and qryRanges.EndRange. This would tell you that 004 10:11 is assigned to 3632. I do not know how to handle something that happens at the end of the range. For example for 004 the last record is 10/30 12:10. If you have something in your other table after 12:10 you would have to write a different query to handle that.
 
Upvote 0 Downvote
So if you had info in another table like this called acctinfo2
Code: 
[tt]AcctID	AssgnDate
100000001004	7/18/2017 10:11
100000001004	7/26/2017 10:29
100000001004	10/31/2017 10:30[/tt]

You can figure out the assigned to by (qryAssigned)
Code: 
SELECT qryRanges.AcctID, qryRanges.AssignedTo, qryRanges.StartRange, qryRanges.EndRange, acctInfo2.AssgnDate AS SearchDate
FROM qryRanges INNER JOIN acctInfo2 ON qryRanges.AcctID = acctInfo2.AcctID
WHERE (((acctInfo2.AssgnDate) Between [qryRanges].[StartRange] And [qryRanges].[EndRange]));
Code: 
[tt]AcctID	AssignedTo	StartRange	EndRange	SearchDate
100000001004	3632	7/18/2017 10:05	7/18/2017 10:13:00 AM	7/18/2017 10:11
100000001004	3551	7/25/2017 15:16	10/30/2017 12:10:00 PM	7/26/2017 10:29[/tt]

notice how the last record is not found that is because there is a null for endrange after 10/30 12:10 for account 004. I would write a separate query to pick up these cases and union the two resulting queries.

So that query would be (qryAssignedEnd):
Code: 
[tt]SELECT qryRanges.AcctID, qryRanges.AssignedTo, qryRanges.StartRange, qryRanges.EndRange, acctInfo2.AssgnDate AS SearchDate
FROM qryRanges INNER JOIN acctInfo2 ON qryRanges.AcctID = acctInfo2.AcctID
WHERE (((qryRanges.EndRange) Is Null) AND ((acctInfo2.AssgnDate)>[qryRanges].[StartRange]));[/tt]

This finds the missing record
Code: 
[tt]AcctID	AssignedTo	StartRange	EndRange	SearchDate
100000001004	-1	10/30/2017 12:10		10/31/2017 10:30[/tt]

There may be a way to build the where statement to do this in one query.
 
Upvote 0 Downvote
MaJP

At the end of the range it should be treated as through today or now. I think you got something here I will work with this over the next couple days. I truly appreciate your and Skip's efforts and this site. Rob
 
Upvote 0 Downvote
I believe that second query would work for the end of the range. So your final solution is simply
select * from qryAssigned
Union
Select * from qryAssignedEnd

where qryAssigned and qryAssignedEnd are the queries
Code: 
SELECT qryRanges.AcctID, qryRanges.AssignedTo, qryRanges.StartRange, qryRanges.EndRange, acctInfo2.AssgnDate AS SearchDate
FROM qryRanges INNER JOIN acctInfo2 ON qryRanges.AcctID = acctInfo2.AcctID
WHERE (((acctInfo2.AssgnDate) Between [qryRanges].[StartRange] And [qryRanges].[EndRange])); 

SELECT qryRanges.AcctID, qryRanges.AssignedTo, qryRanges.StartRange, qryRanges.EndRange, acctInfo2.AssgnDate AS SearchDate
FROM qryRanges INNER JOIN acctInfo2 ON qryRanges.AcctID = acctInfo2.AcctID
WHERE (((qryRanges.EndRange) Is Null) AND ((acctInfo2.AssgnDate)>[qryRanges].[StartRange]));

 
Upvote 0 Downvote
MaJP

Darn, I cannot get this code to complete, its shows the first 30 rows but if I try to goto last record or try to make a table, it hangs

SELECT
A.AcctID,
A.AssignedTo,
A.AssgnDate AS StartRange,
(Select min (b.AssgnDate) as EndRange from Acctinfo as B where A.acctID = b.acctID and B.assgnDate > a.assgnDate) AS EndRange
FROM
acctinfo AS A
ORDER BY
A.AcctID, A.AssgnDate;

 
Upvote 0 Downvote
Subqueries in access tend to run slow. Without seeing this, I cannot help. However, you could do this in code and make a table of ranges.
Build table TblRanges
AcctID
StartRange
EndRange
AssignedTo

My AcctID is a text and AssignedTo is a long, so you will have to modify the parenthesis if that differs

Loop your table and populate the ranges.
Code: 
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 = "SELECT acctinfo.AcctID, acctinfo.AssgnDate, acctinfo.AssignedTo FROM acctinfo ORDER BY acctinfo.AcctID, acctinfo.AssgnDate"
  'open forward only for performance
  Set RS_Source = CurrentDb.OpenRecordset(StrSql, dbOpenForwardOnly)

  Do While Not RS_Source.EOF
    CurrentAcct = RS_Source!acctID
    StartRange = RS_Source!assgnDate
    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!acctID
       If NextAcct <> CurrentAcct Then
        EndRange = Now
       Else
        EndRange = RS_Source!assgnDate
       End If
    End If
    StrSql = "Insert into TblRanges (AcctID,StartRange,EndRange,AssignedTo) values ('" & CurrentAcct & "',"
    StrSql = StrSql & SQLDate(StartRange) & "," & SQLDate(EndRange) & "," & AssignedTo & ")"
    Debug.Print StrSql
    CurrentDb.Execute StrSql
  Loop
End Sub
Function SQLDate(varDate As Variant) As String
    'Purpose:    Return a delimited string in the date format used natively by JET SQL.
    'Argument:   A date/time value.
    'Note:       Returns just the date format if the argument has no time component,
    '                or a date/time format if it does.
    'Author:     Allen Browne. allen@allenbrowne.com, June 2006.
    If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
        Else
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
        End If
    End If
End Function

The insert query would look like this
Code: 
Insert into TblRanges (AcctID,StartRange,EndRange,AssignedTo) values ('100000001004',#07/18/2017 11:29:00#,#07/25/2017 15:16:00#,-1)
And my final table looks like
Code: 
[tt]
ID	AcctID	StartRange	EndRange	AssignedTo
1	100000001004	9/9/2016 11:29:00 AM	11/7/2016 3:28:00 PM	3614
2	100000001004	11/7/2016 3:28:00 PM	7/18/2017 10:05:00 AM	-1
3	100000001004	7/18/2017 10:05:00 AM	7/18/2017 10:13:00 AM	3632
4	100000001004	7/18/2017 10:13:00 AM	7/18/2017 11:29:00 AM	95590
5	100000001004	7/18/2017 11:29:00 AM	7/25/2017 3:16:00 PM	-1
6	100000001004	7/25/2017 3:16:00 PM	10/30/2017 12:10:00 PM	3551
7	100000001004	10/30/2017 12:10:00 PM	1/11/2018 12:32:20 PM	-1
8	100000001104	9/9/2016 11:34:00 AM	11/7/2016 3:28:00 PM	38189
9	100000001104	11/7/2016 3:28:00 PM	7/18/2017 10:05:00 AM	-1
10	100000001104	7/18/2017 10:05:00 AM	7/18/2017 10:13:00 AM	3632
11	100000001104	7/18/2017 10:13:00 AM	7/18/2017 11:29:00 AM	95590
12	100000001104	7/18/2017 11:29:00 AM	7/25/2017 3:16:00 PM	-1
13	100000001104	7/25/2017 3:16:00 PM	12/11/2017 3:09:00 PM	3551
14	100000001104	12/11/2017 3:09:00 PM	1/11/2018 12:32:20 PM	-1
[/tt]
 
Upvote 0 Downvote
Stops at "Set RS_Source = CurrentDb.OpenRecordset(StrSql, dbOpenForwardOnly)" missing a table?

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 = "SELECT All_Assgnd_1.AccountID, All_Assgnd_1.AssgDate, All_Assgnd_1.AssignedTO FROM acctinfo ORDER BY All_Assgnd_1.AccountID, All_Assgnd_1.AssgDate"
'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!AcctID
If NextAcct <> CurrentAcct Then
EndRange = Now
Else
EndRange = RS_Source!assgnDate
End If
End If
StrSql = "Insert into TblRanges (AcctID,StartRange,EndRange,AssignedTo) values ('" & CurrentAcct & "',"
StrSql = StrSql & SQLDate(StartRange) & "," & SQLDate(EndRange) & "," & AssignedTo & ")"
Debug.Print StrSql
CurrentDb.Execute StrSql
Loop
End Sub
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

JimStrand
  • Locked
  • Question
Crosstab query - returning 1 record per week instead of 1 record
Replies
1
Views
274
JimStrand
JimStrand
btamsgn
  • Locked
  • Question
how to create a crosstab query 1
Replies
1
Views
467
dhookom
dhookom
datajockey123
  • Locked
  • Question
Note / Information box with date and time stamp for each entery
Replies
2
Views
277
dhookom
dhookom
JK Dutta
  • Locked
  • Question
requet for form an access query for report
Replies
2
Views
465
dhookom
dhookom
serino
  • Locked
  • Question
Delete From Table
Replies
1
Views
273
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top