How 2 do trend analysis of daily error logs in Access

[Guest]

Hi!

I am a relatively inexperienced user of Access having only worked with it
once beofre, a long time ago. I was given the advice that the task that I
want to do would be better attempted with Access than with Excel.

I receive a table daily consisting of 25 columns, a variable amount of rows
(as it it a daily fault report) of which the first row is a header row. One
of the columns, the eigth one, contains a codes reflecting the type of fault
that occurred. There is a finite list of such codes.

The faultcodes in column 8 of the daily report needs to be totalled so that
I can report on how many of each time of fault occured on a daily basis. Then
the toatls of each day for each type of fault has to be summarized and
totalled on a weekly, a monthly and finally yearly basis.

These totals need to be compiled in a report showing the trends not only in
a table (actual amounts) but it must also tabulate a graph showing the trend
over time. (Weekly or monthly.)

Since this is a daily task - and very time consuming, it has to be automated
so that one only has to browse for and select the daily report (that I
receive) and then press a button to run the macro. The result should be that
the graphs get updated and then there should be an option to print the result.

From what I've read on this site I have seen that several Excel
tools/functions could possibly be used to achieve this: DCount to do the
totalling, VLookups for extraction, and a macro program to run through all of
these tasks for the user.

However I'm not really sure where to start right now in Access.

Any comments, suggestions and references to helpful information would be
greatly

appreciated.

 

[Simon Pollock]

To get you started:

Design the table which will contain your imported data with appropriate
column names and corresponding data types.

Add a date column, so that you can distinguish your different sets of
imported data.

Add an autonumber column as a unique primary key.

Run the report wizard on this table.

 

[Guest]

Rochelle,

As Simon mentioned, your first step is to create the Access table that has
the structure similiar to your Excel spreadsheet. You could do this
initially by importing the Excel spreadsheet into Access (File -> Get
External -> Import from the main menu).

Once you have it imported, open the table in design view, add the FaultDate
(don't use [Date] as a field name because it is a reserved word) and an ID
(autonumber) field. Delete any of the fields from the Excel spreadsheet that
you won't ever use. Then delete all of the data from the table.

Next, you will want to link the Excel table to Access (File -> Get External
-> Link). Eventually, you will want to automate this so that each day all
you have to do is select the file to link and the application will link the
file and import the data into your table.

Once the Excel file is linked, create a query to import the Excel data into
your table. To do this, select the Query -> New option and select your Excel
linked speadsheet. Drag each of the fields from your linked Excel
spreadsheet (the ones you didn't delete in the first step above) into the
query grid. Add another column, give it a title and date data that looks
like:

FaultDate: Date()

Although instead of Date() you may need to actually enter the date value if
the data is from another day. In that case, the data would look like:

FaultDate: #8/23/2007#

Now, change this query to an Append query, select the Access table from the
popup. When you do this, the names of the matching fields should show up in
the Append To row of the grid. Make sure that there is a field name in the
Append To row for each column of data, then run the query. This will append
the data to your table.

Gotta go, more to follow.