Re: how to match time ranges for multiple records in a query?

mike wrote:
> Hi Bob,
>
> Here's a sample table:
>
> ID userID day timeStart timeEnd
> 7 6 FRI 11:00 AM 01:30 PM
> 1 1 MON 12:00 PM 01:00 PM
> 3 2 MON 12:30 PM 01:30 PM
> 6 5 THU 07:00 PM 11:00 PM
> 4 3 TUE 12:15 PM 01:00 PM
> 8 2 TUE 12:00 PM 02:00 PM
> 2 1 WED 03:00 PM 06:30 PM
> 5 4 WED 05:00 PM 07:00 PM
> 9 5 WED 04:00 PM 08:00 PM
> 10 2 WED 01:00 PM 10:00 PM
>
> userID's 1 and 2 overlap between 12:30 and 1:00 on MON
> userID's 4 and 8 overlap between 12:15 and 1:00 on TUE
> userID's 1,4,5,2 overlap between 4:00 and 6:30 on WED

None of this addresses the meaning of "maximum of X users that can meet at
the same time" or "maximum overlap"
>
> I want to make a query that works in access 2000 and ms SQL that
> returns the userIDs and the overlap range similar to the statements
>
> above. Any idea how to do it?
>
> -Mike

I'm still not clear about what data needs to be returned. Your original
question mentioned "maximum of X" so it sounds as if some sort of count is
needed?

Let me put it this way: Given that your table looks as above, you run the
query. What does the returned recordset look like? Or, more t the point:
what do you want the recordset to look like? Recordsets are tabular in
nature, so it's easier to visualize the requirements if you present the
desired output in that tabular format.

Bob Barrows


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.


.

Relevant Pages

  • Re: Use a recordset inside a Function?
    ... PS - there's no client side code or script block on the page ... ANY line that references the recordset generates the error. ... >> Bob Barrows ... >> response by posting to the newsgroup. ...
    (microsoft.public.inetserver.asp.general)
  • RE: Password
    ... fields in tblUsers - USERID and PWORD ... "Dave Hawks" wrote: ... Simpley create a WHERE statement that looks at UserID and Password ... If BOF and EOF then '(This tests if there are any records in the recordset) ...
    (microsoft.public.access.formscoding)
  • Re: Data truncated in text box on form
    ... Bob, ... Dim rs As Recordset ... Set DB = CurrentDb ...
    (microsoft.public.access.forms)
  • RE: Password
    ... I created the form from the UserId table and inserted your code on the click ... Set oRS = New ADODB.Recordset ... "Dave Hawks" wrote: ... If BOF and EOF then '(This tests if there are any records in the recordset) ...
    (microsoft.public.access.formscoding)
  • Re: Use a recordset inside a Function?
    ... Code that creates the recordset is in a script block at the top of the page ... > Bob Barrows ... >> function GetRecords() ...
    (microsoft.public.inetserver.asp.general)