calculation..? (1 Viewer)

[dgoulston]

help
ok heres the prob,

i have a log on date and an log off date
most calls range from 1 min to 1hr duration

i want to make a graph that shows users/time

so that the bottom axis is in minutes and shows at each minute how many users were online..

i am baffeled on how to go about doin this. i have tried various ways and just got lost. so please help

thank you in advance..

anymore info required i will be glad to provide,

DAL

 

[ColinEssex]

Hi

If you have a logOn DATE and logOff DATE - how do you know what time of day it is?

Are you wanting to divide the day up into minutes?

I've only been at work half an hour - not awake yet - forgive me if I'm being stupid

Col

 

[Rich]

Have you tried using the wizard?

 

[ColinEssex]

Hi Rich

Will the wizard wake me up?

Col

 

[Rich]

Trying to get it to work will

 

[dgoulston]

no wizard is no good at this point

need somekinda calculation first..

becasue both field need to be used as the bottom axis
(start time) (end time)
because if a user logs on i want it to add 1 person at that time and keep that one person on the line graph until the time they log off.. etc for all users.. so i have a graph not showing who loged on but how many people were online at a time.


thanx in advance

DAL

 

[Rich]

Use a totals query for the graphs record source then.
GroupBy minutes and count the users?

 

[dgoulston]

totals query?

ok wat i want is the graph date to look like is this:

noofuser.........user1.............user2.............user3
1 user.............10:10
2 users...........10:20.............10:20
2 users...........10:30.............10:30
1 user...................................10:40
2 users.................................10:50...........10:50
2 users.................................11:00...........11:00
1 user.......................................................11:10

etc...
so they overlap
all i have is the user start time and end time so for example all i have for user1 is 10:10 and 10:20..

the graph would look like this
|
|
|_ -- -- _ -- -- _
|_________________

so is there a way i can do this... dont forget all the figures i have are start/stop.. eg. for user 2 all i have is start:10:20 and stop 11:00

is there a way.

thank you for your help and im sorry if i have not made my prob clear or misunderstood u.

DAL

 

[Rich]

I suspect you'll need a crosstab query to output the type of result your looking for.

 

[dgoulston]

Function endusers(a, b)
Dim x, y As Integer
Dim currentdate As Date
Dim s As Integer
Dim dbs As Database, rst As Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("do not edit this table", dbOpenDynaset)

s = DatePart("d", a)
currentdate = DateAdd("d", a, currentdate)
s = DatePart("h", a)
currentdate = DateAdd("h", s, currentdate)

For x = 1 To 24
If x > 0 Then
currentdate = DateAdd("h", 1, currentdate)
End If
For y = 1 To 60
currentdate = DateAdd("n", 1, currentdate)
If a < currentdate Then
If b > currentdate Then
With rst
.AddNew
![Date] = currentdate
![Dateb] = a
.Update
End With
End If
End If
Next y
Next x

rst.Close
End Function



what am i doin wrong? this should output all the right times to the table. the data im using is as follows

starttime............endtime
8:28......................8:29
8:29......................8:30
8:30......................8:30
8:33......................8:39
8:45......................8:49
8:50......................8:55
8:08......................8:15
8:08......................8:15
8:19......................8:24
8:16......................8:27
8:59......................9:00
8:33......................9:00
8:50......................9:03

this data should return 77 values into the new table.. but its returning 172.. why???

ok dont think i can provide any more info than that, oh yes the code is run by a query. :- exp1: endusers(starttime,endtime)

is there a better way to do it?

thank you
DAL

 

[simongallop]

So long as you aren't wanting that little table of users but only the graph then:

Create a table with all the time slots that you want to look at

Create a query where you have the time slot table (where the fields are called Start and End). Also show the user table but with no joins. Now write the following code (where StartTime and EndTime are from table User)

SU: IIf([StartTime]<=[End] And [EndTime]>=[Start],[User],0)

This will show each user in a slot. To count it might be easier to repeat the formula and swap User for 1 then you can run another query to sum it all up.

HTH

 

[dgoulston]

sorry mate ur code is of no use.. it does not do anything for this problem at all..

i have solved it my self.. and its workin fine.. will be back tomorrow with more problems more than likely...

thankyou everyone who helped
DAL

 

[Rich]

Why not share the solution, which is the object of the forum

 

[dgoulston]

the finished code!

oh soz i diddnt say did i, that code i posted above worked. it was jus access fucking bout

all u need with it is a delete query so that u can delete all the information in the table it creates before u run the code. and a query to run the code. it does not write anything to the query but it was the easyist way i knew of doin it (using a query). it writes all the date to a table called "do not edit this table". all u need in ur table is a date column with date value.

this is the finished code:

Function endusers(a, b)
Dim x, y, z As Integer
Dim currentdate As Date
Dim s As Integer
Dim dbs As Database, rst As Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("do not edit this table", dbOpenDynaset)

s = DatePart("d", a)
currentdate = DateAdd("d", a, currentdate)
s = DatePart("h", a)
s = s - 1
currentdate = DateAdd("h", s, currentdate)

For x = 1 To 24
If x > 0 Then
currentdate = DateAdd("h", 1, currentdate)
End If
For y = 1 To 60
If y > 0 Then
currentdate = DateAdd("n", 1, currentdate)
End If
'For z = 0 To 60
'if z > 0 then
'currentdate = DateAdd("s", 1, currentdate)
'end if
If a < currentdate Then
If b > currentdate Then

With rst
.AddNew
![Date] = currentdate
.Update
End With
End If
End If
'Next z
Next y
Next x

rst.Close
End Function



and u can take out the ' and it will be more precist and do the test per second. the varible a is the start date and b the end date.

and then just make a report that uses the date column of the new table and counts dates on side axis and has dates in mins or hours on the bottom axis.

hope this helps anyone who has my problem.

DAL