sql statment to show multiple phone calls at the same time.

[James]

anyone know how to write a sql statment that will list all phone calls when
other people were using the phone too? i.e. select all phone calls when
there was more than 1 call at the same time. i have a table with phone
records, it has start and stop times for the calls...

 

[Guest]

Post an example of your data as I could follow what you said.

 

[Guest]

anyone know how to write a sql statment that will list all phone calls when
other people were using the phone too? i.e. select all phone calls when
there was more than 1 call at the same time. i have a table with phone
records, it has start and stop times for the calls...

A "Self Join" query can do this.

Create a query adding the calls table to the query window TWICE. Don't join
the tables. Access will alias the second instance by adding a "1" to the name
- Calls and Calls1 for example.

As a criterion on Calls1.StartTime put

= Calls.StartTime AND <= Calls.EndTime

Put the same criterion, down one line so it uses OR logic, on Calls1.EndTime

On the Primary Key of Calls1 (CallID, I'll guess) put

Calls.CallID

so that the call doesn't find itself, and so that you don't see the same
pair of calls twice (with roles reversed).

 

[James]

ID calldate CallEndDate
1 5/12/06 2:46 PM 5/12/06 2:46 PM
2 5/12/06 2:46 PM 5/12/06 2:51 PM
3 5/12/06 2:51 PM 5/12/06 2:51 PM
4 5/12/06 3:06 PM 5/12/06 3:09 PM
5 5/12/06 3:09 PM 5/12/06 3:10 PM
6 5/12/06 3:10 PM 5/12/06 3:10 PM
7 5/12/06 3:25 PM 5/12/06 3:30 PM
8 5/12/06 3:31 PM 5/12/06 3:32 PM
9 5/12/06 3:44 PM 5/12/06 3:45 PM
10 5/12/06 3:47 PM 5/12/06 3:50 PM
11 5/12/06 3:53 PM 5/12/06 3:54 PM
12 5/12/06 3:58 PM 5/12/06 3:59 PM
13 5/12/06 3:59 PM 5/12/06 3:59 PM
14 5/12/06 4:00 PM 5/12/06 4:00 PM
15 5/12/06 3:58 PM 5/12/06 4:01 PM
16 5/12/06 4:06 PM 5/12/06 4:09 PM
17 5/12/06 4:06 PM 5/12/06 4:10 PM

 

[James]

heres what my query looks like, but it doesn't seem to work correclty. it
just repeats the same records over and over. maybe using a count() somewhere
would help?

SELECT calls_1.calldate, calls_1.ID
FROM calls, calls AS calls_1
WHERE (((calls_1.calldate)>=[calls].[calldate] And
(calls_1.calldate)<=[calls].[callenddate]) AND ((calls_1.ID)=[calls].[id]))
OR (((calls_1.calldate)>=[calls_1].[calldate] And
(calls_1.calldate)<=[calls_1].[callenddate]));

 

[Douglas J. Steele]

See whether my Febuary, 2005 "Access Answers" column in Pinnacle
Publication's "Smart Access" is of any use to you. You can download the
column (and sample database) for free at
http://www.accessmvp.com/djsteele/SmartAccess.html

 

[James]

Thank You!

See whether my Febuary, 2005 "Access Answers" column in Pinnacle
Publication's "Smart Access" is of any use to you. You can download the
column (and sample database) for free at
http://www.accessmvp.com/djsteele/SmartAccess.html

 

[Guest]

Hi James,

Here is my interpretation of John Vinson's suggestion, using table and field
names that you indicated:

First possibility
SELECT Calls.ID, Calls.calldate, Calls.callenddate
FROM Calls, Calls AS Calls_1
WHERE (((Calls_1.calldate)>=Calls.calldate
And (Calls_1.calldate)<=Calls.callenddate)
And ((Calls_1.ID)>Calls.ID))
Or (((Calls_1.callenddate)>=Calls.calldate
And (Calls_1.callenddate)<=Calls.callenddate)
And ((Calls_1.ID)>Calls.ID));


Using the data you supplied, this query returns the following records:
ID calldate callenddate
1 5/12/2006 2:46:00 PM 5/12/2006 2:46:00 PM
2 5/12/2006 2:46:00 PM 5/12/2006 2:51:00 PM
4 5/12/2006 3:06:00 PM 5/12/2006 3:09:00 PM
5 5/12/2006 3:09:00 PM 5/12/2006 3:10:00 PM
12 5/12/2006 3:58:00 PM 5/12/2006 3:59:00 PM
12 5/12/2006 3:58:00 PM 5/12/2006 3:59:00 PM
16 5/12/2006 4:06:00 PM 5/12/2006 4:09:00 PM


Second possibility
SELECT Calls_1.ID, Calls_1.calldate, Calls_1.callenddate
FROM Calls, Calls AS Calls_1
WHERE (((Calls_1.ID)>[Calls].[ID])
AND ((Calls_1.calldate)>=[Calls].[calldate]
And (Calls_1.calldate)<=[Calls].[callenddate]))
OR (((Calls_1.ID)>[Calls].[ID])
AND ((Calls_1.callenddate)>=[Calls].[calldate]
And (Calls_1.callenddate)<=[Calls].[callenddate]));

Using the data you supplied, this query returns the following records:
ID calldate callenddate
2 5/12/2006 2:46:00 PM 5/12/2006 2:51:00 PM
3 5/12/2006 2:51:00 PM 5/12/2006 2:51:00 PM
5 5/12/2006 3:09:00 PM 5/12/2006 3:10:00 PM
6 5/12/2006 3:10:00 PM 5/12/2006 3:10:00 PM
13 5/12/2006 3:59:00 PM 5/12/2006 3:59:00 PM
15 5/12/2006 3:58:00 PM 5/12/2006 4:01:00 PM
17 5/12/2006 4:06:00 PM 5/12/2006 4:10:00 PM



I think I like a marriage of the two possibilities, as in this union query.
A union query produces a read only recordset, so that might not be acceptable
to you:

SELECT Calls.ID, Calls.calldate, Calls.callenddate
FROM Calls, Calls AS Calls_1
WHERE (((Calls_1.calldate)>=[Calls].[calldate]
And (Calls_1.calldate)<=[Calls].[callenddate])
AND ((Calls_1.ID)>[Calls].[ID]))
OR (((Calls_1.callenddate)>=[Calls].[calldate]
And (Calls_1.callenddate)<=[Calls].[callenddate])
AND ((Calls_1.ID)>[Calls].[ID]))

UNION

SELECT Calls_1.ID, Calls_1.calldate, Calls_1.callenddate
FROM Calls, Calls AS Calls_1
WHERE (((Calls_1.ID)>[Calls].[ID])
AND ((Calls_1.calldate)>=[Calls].[calldate]
And (Calls_1.calldate)<=[Calls].[callenddate]))
OR (((Calls_1.ID)>[Calls].[ID])
AND ((Calls_1.callenddate)>=[Calls].[calldate]
And (Calls_1.callenddate)<=[Calls].[callenddate]));


Using the data you supplied, this last union query returns the following
records:
ID calldate callenddate
1 5/12/2006 2:46:00 PM 5/12/2006 2:46:00 PM
2 5/12/2006 2:46:00 PM 5/12/2006 2:51:00 PM
3 5/12/2006 2:51:00 PM 5/12/2006 2:51:00 PM
4 5/12/2006 3:06:00 PM 5/12/2006 3:09:00 PM
5 5/12/2006 3:09:00 PM 5/12/2006 3:10:00 PM
6 5/12/2006 3:10:00 PM 5/12/2006 3:10:00 PM
12 5/12/2006 3:58:00 PM 5/12/2006 3:59:00 PM
13 5/12/2006 3:59:00 PM 5/12/2006 3:59:00 PM
15 5/12/2006 3:58:00 PM 5/12/2006 4:01:00 PM
16 5/12/2006 4:06:00 PM 5/12/2006 4:09:00 PM
17 5/12/2006 4:06:00 PM 5/12/2006 4:10:00 PM



Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

heres what my query looks like, but it doesn't seem to work correclty. it
just repeats the same records over and over. maybe using a count() somewhere
would help?

SELECT calls_1.calldate, calls_1.ID
FROM calls, calls AS calls_1
WHERE (((calls_1.calldate)>=[calls].[calldate] And
(calls_1.calldate)<=[calls].[callenddate]) AND ((calls_1.ID)=[calls].[id]))
OR (((calls_1.calldate)>=[calls_1].[calldate] And
(calls_1.calldate)<=[calls_1].[callenddate]));

A "Self Join" query can do this.

Create a query adding the calls table to the query window TWICE. Don't
join
the tables. Access will alias the second instance by adding a "1" to the
name
- Calls and Calls1 for example.

As a criterion on Calls1.StartTime put


Put the same criterion, down one line so it uses OR logic, on
Calls1.EndTime

On the Primary Key of Calls1 (CallID, I'll guess) put


so that the call doesn't find itself, and so that you don't see the same
pair of calls twice (with roles reversed).