Question...

[Guest]

I have an Access table (Access 2000) that contains phonelogs. These logs are
compiled with Date (dd/mm/yyyy), Time of Day (hh:mm:ss), Start of
conversation (dd/mm/yyyy hh:mm:ss), Duration (hh:mm:ss).

I want to know how many lines (we have 10) are occupied in a particular
timeslot. For instance, if PersonA started his conversation at 27/10/2004
09:01:00 to 09:02:33 and PersonB at 27/10/2004 09:02:00 to 09:10:22 then they
occupied 2 lines for a brief moment as they times clashed.

How can I do these stats using SQL or programming please?

skc

 

[Nikos Yannacopoulos]

Skc,

I've done something similar in the past; the logic involved a union query
and some VBA code. The Union query is something like:

SELECT PhoneLog.Start As EventTime, "On" AS Event
FROM tblPhoneLog

UNION SELECT [Start]+[Duration] AS [End], "Off" As Event
FROM tblPhoneLog

This will return line on-and-offs as independent events, like:

EventTime Event
27/10/04 09:01:00 On
27/10/04 09:02:00 On
27/10/04 09:02:33 Off
27/10/04 09:10:22 Off

as per your example.

Now, the code opens this query (ordered on EventTime) as a recordset, and
goes through the records. As it does so, it checks the value of field Event
on each record, and increases the number of an active line counter by one
for every On, and decreases it by one for every Off. Each time it finds an
On it compares the current value of active lines to another variable (max
active lines) and replaces it if greater. So, at the end of the loop through
the records, the latter holds the max number of active lines. The code would
look something like:

Sub Count_Max_Active_Lines()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim MaxLines As Long
Dim ConcLines As Long
Dim strSQL As String
ConcLines = 0
MaxLines = 0
Set db = CurrentDb
strSQL = "SELECT * FROM qryEvents ORDER BY EventTime"
'you can impose some filter on strSQL, or on the original Union query
Set rst = db.OpenRecordset(strSQL)
rst.MoveFirst
Do Until rst.EOF
If rst.Fields(1) = "On" Then
ConcLines = ConcLines + 1
If ConcLines > MaxLines Then
MaxLines = ConcLines
End If
Else
ConcLines = ConcLines - 1
End If
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set db = Nothing
Debug.Print MaxLines
'code to do what you want with MaxLines
End Sub

Note: you need a proper DAO reference for this code to run.

HTH,
Nikos

 

[Nikos Yannacopoulos]

Skc,

Call Start and Duration fields should be Date/Time type, so that's fine
(Duration must be Date/Time as well, there is no Time field type; don't be
mislead by the fact it only contains the time part!).

Now, in your SQL expression: enclose field names in square brackets, not
double quotes. Double quotes are only used for text strings in calculated
fields (On and Off in our case). So, the syntax should be:

SELECT [Call Start] As EventTime, "On" As Event FROM Query3

UNION SELECT [Call Start]+[Duration] As End, "Off" As Event FROM Query3

Note: what you call the fields in the second SELECT (or any subsequent one,
for that matter) in a Union query is immaterial (as long as you don't use
reserved words and invalid characters). The fields in the query results will
be named after the corresponding field name in the first SELECT.

HTH,
Nikos

Nikos,

I followed everthing you have said below. My code is:

SELECT "Call Start" As EventTime, "On" As Event From Query3
UNION Select ["Call Start"]+["Duration"] As [End], "Off" As Event From Query3.

THe <"field"> has inverted commas in. The "Call Start" field and "Duration"
are Date/Time and Time. Should these both be the same?

skc

Skc,

How did you do it? To make a union query, you start to make a new query in
design view, add no table to the query and switch to SQL view, where you
actually type the SQL expression for it. Is this what you did? Did you get
the table and field names right? It could be that a fieldname is wrong in
the first SELECT statement, so it doesn't return any records. If you can't
find and fix the problem, post back the SQL expression in your query and the
actual table and field names for help.

HTH,
Nikos

Hi

Can you explain your Union Query. I have applied it, and all my EventTime
fields are Off.

skc

:

Skc,

I've done something similar in the past; the logic involved a union query
and some VBA code. The Union query is something like:

SELECT PhoneLog.Start As EventTime, "On" AS Event
FROM tblPhoneLog

UNION SELECT [Start]+[Duration] AS [End], "Off" As Event
FROM tblPhoneLog

This will return line on-and-offs as independent events, like:

EventTime Event
27/10/04 09:01:00 On
27/10/04 09:02:00 On
27/10/04 09:02:33 Off
27/10/04 09:10:22 Off

as per your example.

Now, the code opens this query (ordered on EventTime) as a

recordset,
and

goes through the records. As it does so, it checks the value of

field
Event

on each record, and increases the number of an active line counter

by
one

for every On, and decreases it by one for every Off. Each time it

finds
an

On it compares the current value of active lines to another variable (max
active lines) and replaces it if greater. So, at the end of the loop through
the records, the latter holds the max number of active lines. The

code
would

look something like:

Sub Count_Max_Active_Lines()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim MaxLines As Long
Dim ConcLines As Long
Dim strSQL As String
ConcLines = 0
MaxLines = 0
Set db = CurrentDb
strSQL = "SELECT * FROM qryEvents ORDER BY EventTime"
'you can impose some filter on strSQL, or on the original Union query
Set rst = db.OpenRecordset(strSQL)
rst.MoveFirst
Do Until rst.EOF
If rst.Fields(1) = "On" Then
ConcLines = ConcLines + 1
If ConcLines > MaxLines Then
MaxLines = ConcLines
End If
Else
ConcLines = ConcLines - 1
End If
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set db = Nothing
Debug.Print MaxLines
'code to do what you want with MaxLines
End Sub

Note: you need a proper DAO reference for this code to run.

HTH,
Nikos

I have an Access table (Access 2000) that contains phonelogs.

These
logs

are
compiled with Date (dd/mm/yyyy), Time of Day (hh:mm:ss), Start of
conversation (dd/mm/yyyy hh:mm:ss), Duration (hh:mm:ss).

I want to know how many lines (we have 10) are occupied in a particular
timeslot. For instance, if PersonA started his conversation at 27/10/2004
09:01:00 to 09:02:33 and PersonB at 27/10/2004 09:02:00 to

09:10:22
then

they
occupied 2 lines for a brief moment as they times clashed.

How can I do these stats using SQL or programming please?

skc

 

[Nikos Yannacopoulos]

Skc,

While in the VB editor screen, go Tools > References and see if there is a
"Microsoft DAO 3.XX Object Library" among the checked ones at the top of the
list. If not, scroll down to find it and check it. You may find several,
like 3.51and 3.6; in that case. if you are running A2K or later 3.6 is
appropriate; if you are running A97 it's 3.51.

HTH,
Nikos

nikos,

I go the On, Off to work. What do you mean by proper "DAO reference for the
code to run"?

skc

Skc,

How did you do it? To make a union query, you start to make a new query in
design view, add no table to the query and switch to SQL view, where you
actually type the SQL expression for it. Is this what you did? Did you get
the table and field names right? It could be that a fieldname is wrong in
the first SELECT statement, so it doesn't return any records. If you can't
find and fix the problem, post back the SQL expression in your query and the
actual table and field names for help.

HTH,
Nikos

Hi

Can you explain your Union Query. I have applied it, and all my EventTime
fields are Off.

skc

:

Skc,

I've done something similar in the past; the logic involved a union query
and some VBA code. The Union query is something like:

SELECT PhoneLog.Start As EventTime, "On" AS Event
FROM tblPhoneLog

UNION SELECT [Start]+[Duration] AS [End], "Off" As Event
FROM tblPhoneLog

This will return line on-and-offs as independent events, like:

EventTime Event
27/10/04 09:01:00 On
27/10/04 09:02:00 On
27/10/04 09:02:33 Off
27/10/04 09:10:22 Off

as per your example.

Now, the code opens this query (ordered on EventTime) as a

recordset,
and

goes through the records. As it does so, it checks the value of

field
Event

on each record, and increases the number of an active line counter

by
one

for every On, and decreases it by one for every Off. Each time it

finds
an

On it compares the current value of active lines to another variable (max
active lines) and replaces it if greater. So, at the end of the loop through
the records, the latter holds the max number of active lines. The

code
would

look something like:

Sub Count_Max_Active_Lines()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim MaxLines As Long
Dim ConcLines As Long
Dim strSQL As String
ConcLines = 0
MaxLines = 0
Set db = CurrentDb
strSQL = "SELECT * FROM qryEvents ORDER BY EventTime"
'you can impose some filter on strSQL, or on the original Union query
Set rst = db.OpenRecordset(strSQL)
rst.MoveFirst
Do Until rst.EOF
If rst.Fields(1) = "On" Then
ConcLines = ConcLines + 1
If ConcLines > MaxLines Then
MaxLines = ConcLines
End If
Else
ConcLines = ConcLines - 1
End If
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set db = Nothing
Debug.Print MaxLines
'code to do what you want with MaxLines
End Sub

Note: you need a proper DAO reference for this code to run.

HTH,
Nikos

I have an Access table (Access 2000) that contains phonelogs.

These
logs

are
compiled with Date (dd/mm/yyyy), Time of Day (hh:mm:ss), Start of
conversation (dd/mm/yyyy hh:mm:ss), Duration (hh:mm:ss).

I want to know how many lines (we have 10) are occupied in a particular
timeslot. For instance, if PersonA started his conversation at 27/10/2004
09:01:00 to 09:02:33 and PersonB at 27/10/2004 09:02:00 to

09:10:22
then

they
occupied 2 lines for a brief moment as they times clashed.

How can I do these stats using SQL or programming please?

skc

 

[Jamie Collins]

(e-mail address removed) (Jamie Collins) wrote ...

There is a slight flaw in the design: an end date would've be more
appropriate but no big deal because the end date can be derived using
start date and duration.

Just re-reading that, I seem to have given the wrong emphasis. I meant
to say, it's a shame the data is not collected with a start and an
end. If my pone bill is anything to go by, phone logs are commonly
shown with a start and duration. Better go with the reality of the
available raw data than try to calculate on the fly (that's what VIEWs
are for!)

Jamie.

--

 

[Jamie Collins]

Now, in your SQL expression: enclose field names in square brackets, not
double quotes. Double quotes are only used for text strings in calculated
fields (On and Off in our case).

Double quotes is ANSI standard SQL; square brackets is proprietary.
They may both be used in for an alias name that contains space and
other illegal characters in a name but perhaps double quotes may be
considered 'superior' because it is portable.

Note: what you call the fields in the second SELECT (or any subsequent one,
for that matter) in a Union query is immaterial (as long as you don't use
reserved words and invalid characters).

In your example, On is a column name; whether it is an expression with
an alias in a VIEW or a column in a table is irrelevant as far as the
SQL parser is concerned. The only reason you need to put On in
brackets/quotes is that On is a reserved word in Jet 4.0 and ODBC
(among others) and vague, hence a poor choice for a column name. You
went against your own advice about avoiding reserved words!

BTW I still can't see that your 'loop through a recordset' approach is
better than a pure SQL solution. Care to explain you reasoning?

Thanks,
Jamie.

--

 

[Nikos Yannacopoulos]

Jamie,

To begin with, I never claimed my solution is superior. I offered one
solution, not the best... so I don't feel I have much to explain.
I never cared to argue with you, even though I didn't appreciate your tone
in (quote):

If you can't get the results you need from a database using SQL then
either the schema is wrong or you are not trying hard enough - looping
through a recordset is like waving a white flag <g>.

I am not an SQL expert, yet I provided a working solution.

On the matter at hand: "On" and "Off" are values assigned to column Event
(this is the alias), not column names. I don't know if it looks any
different to the eyes of an SQL expert, but that's how it works in Access.

Regards,
Nikos

 

[Jamie Collins]

I didn't appreciate your tone
in (quote):

Members of the MS Access community, see:

http://www.mvps.org/access/netiquette.htm

(quote):
"Look for Smileys , grins <g>, and other "Emoticons".
When you see one, the preceding statement was not meant to be really
serious, don't take it as such."

Jamie.

--