Hello , my first post\\

I am currently stuck in finding solution to my database problem. I have to create database that counts total downtime of an object.

The principle is simple there are 2 main events that are entered manualy in database it is starting date of event and finish date of event[ lets call it downtime]

Next one is that I have to get out from these start/finish events 2 results
first : total downtime, simple as Tfinish-Tstart
second: downtime depending on wether object was accesable for client or not. HERE starts the big thinking

Object is lets say accesable monday - friday from 8AM till 20 PM and Saturday - Sunday from 12AM till 18PM

So +/- mathematical part I can figure out, but Is it possible to impliment such a time filtering in access ?

If yes how could aproximetly design of database look like ?

appriciate any help, can post more detailed info if need

Hi -

Would you describe your fields (datetime or text) and provide an example of how your date/times are entered.

Bob

are you saying you only want to find that portion of the events time, that corresponds to the users time window?

Object is lets say accesable monday - friday from 8AM till 20 PM and Saturday - Sunday from 12AM till 18PM

I assume your allotted periods are stored in a table of some sort - so you may need to do a maketable to generate the userlogin periods for the time frame under review - ultimately you need a table that has the following columns


username date permittedstart permittedend
jim 12/3/09 0800 2000
jim 13/3/09 1000 2000
jim 14/3/09 1200 2000
jim 15/3/09 1300 2000

note if a time window can straddle a midnight, you will need a different slightly more complex structure

then use this in a query, and have a function that takes your start/finsih time sessions and compares it with the downtime table to identify uptime/downtime periods.

this will certainly be a bit fiddly to get right, but not too hard