reporting peak usage - gotten as far as basic running total

[Gelcys]

I feel as though I’m banging my head against a wall trying to get something done – Access is good at some parts and Excel at others. What I’m starting with is a txt file that I need to import weekly or monthly into either and repeat the same process, to continue to add to a monthly report so I can analyze usage of licenses from a log file.

The log file looks like this:
Date time user feature_used checkedin_or_ch eckedout
01/01/10 8:00 am Kathy view OUT
01/01/10 8:30 am Mark produce OUT
01/01/10 9:30 am Vickie produce OUT
01/01/10 9:45 am Mark produce IN
01/01/10 11:00 am Kathy view IN
01/01/10 11:30 am Vickie produce IN

It’s cumbersome to read – but I need to derive peak usage from it.
View = 1 at 8:00 am
Produce = 2 at 9:30 am

I have been able to import to Access. I’ve been able to make a query that updates the table so that every Out=1 and every IN=-1, and have been able to make a running query to have a running total… but for all, and not per feature. If I filter 1 feature, it’s still looking at the original table to derive the running total so I’m at the point where I’m ready to make a new query for each feature. There may be an easier way, or I may be on the right track but any help is appreciated!

[Rabbit]

I'm just going off the top of my head but if you cross join the table with itself and keep only the ones where the out from one table falls between the out from the other tables, you could then do a count grouping by feature_used and whatever date dimension you want to use.

[Gelcys]

thank you for your response Rabbit - that sounded like chinese to me, but I'm willing to try anything. I'll start by looking up cross joining. Further details are appreciated.