Data Macro to analyze Audit trail information

Hannes Zerner
Hannes Zerner used Ask the Experts™
on
Hey,

I am looking for a Macro / DB Structure, where I can import a predefined raw data format which includes start and stop dates for individual User entries being enabled for various services (including Hierarchies of Services).

This Data base is supposed to provide me Usage Statistics per month and if applicable applying the hierarchies.

Can somebody point me to the right direction / area where I could find something for this?

Thank you.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & Systems Engineer

Commented:
I think some sample data with a mini guide would be most helpful...

Author

Commented:
Columns would be something like:

User ID ; Service subscribed ; Start Date of Service ; End Date of Service
1234; Service 1 ; 01/01/2010 ; 12/31/2011
1234; Service 2 ; 01/01/2010 ; 12/31/2011
4321; Service 1 ; 01/01/2010 ; 12/31/2011
4321; Service 2 ; 01/01/2010 ; 12/31/2010

In this instance, service 1 is included in Service 1 and if for the same period both services are assigned per user, only the higher one is supposed to be counted.

For this example the following is the expected output
For each of the months between 1/1/2010-12/31/2010 I am expecting Output 2 Users for Service 2
For each of the months between 1/1/2011-12/31/2011 I am expecting Output 1 User for Service 1 and 1 User for Service 2.

In general it should be a relatively simple counting mechanism, but I am uncertain about the hierarchies.
Software & Systems Engineer
Commented:
Check the attached database...query1 & query5 should give you the desired results...
HZ.accdb

Author

Commented:
This is a good starting point. Thank you.
John TsioumprisSoftware & Systems Engineer

Commented:
If you study the queries you will see that it can also do analysis per month...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial