Possible in Access? System status reporting

[Sergdor]

Good Afternoon and thank you for your assistance.

I am new to access and typically log most of the information im about to describe in excel. A coworker thought access might be a better option and since I have little experience in access I wanted to ask if/how I can do the following.

I support a system that has a list of possible alarms and those alarms get reported to us on a quarterly basis. They also report the stauts of the system interfaces when degraded/inoperable. I want to take their report and add all the details to access using a form the will allow me to log all of the information reported each quarter.

I was able to get the general system information such as serial numbers. system software version, etc logged in a form and the next step would be to create a method where I can add a list of alarms to the record. The end goal is I pull this data and use it to determine common failures and areas we need to improve our training efforts to better prepare the system users.

Example of a report


System A (general system status reported serial numbers, date of report, software versions, etc)
The following alarms
5 active alarms when report was conducted
alarm a, alarm 2, alarm x, alarm 5, alarm 8 ( I have a list of possible system alarms the system can display)
Interface status ( I have a list of possible interfaces)
3 interfaces degraded, 4 interfaces inactive (will need to list the specific interface that is down in the database)

Is it possible to add all of this information into an access form to all for easy logging of the information?

The end goal is to be able to pull the report for that system and look at the previous alarms reported and compare those overtime from report to report. we have about 100 total systems we track at the moment and though excel is working I would like to see what access type options are available.

Thank you for your assistance.

[Micron]

If you can imagine it, it can be done. The trick is to do it right, and most people coming from Excel to Access fall flat right of the bat. Excel is column based (wide) and databases are row based (tall). Data is broken up into entities (tables) with attributes (fields) - one table per entity. In Excel it's common to repeat columns (alarm1 | alarm2 | alarm3 | etc.) and this must NOT be done in database tables. If you want to explore Access I can post a bunch of introductory links that will help you get off to a better start. Be prepared to spend a significant upfront learning time to do it right, or be prepared to spend a lot more time asking how to fix problems resulting from the many pitfalls you can get into.

Alarms for maintenance are not new to me, BTW. I spent some time mining data from an Intelligent Condition Monitoring System (ICMS) in my career.

[Sergdor]

Thank you for the response. Yes any information you can provide will be extremely helpful. I haven't invested much time trying to get it to work because my knowledge base isnt there with access. . At the moment I have a few tables built with the software versions, alarms, and interfaces and once all of the information is in the database I will begin working through the form.
I was told to get all of my reference data squared away in the system before attempting to build any type of form.

In excel I would make multiple line items for the same report to list each of the reported alarms and this allowed me to get a total and compare that year over year. I just assumed in the long run access might be an easier and faster option to filling in hundreds of excel lines.

[Micron]

Easier for me to re-post than look for one of the many times I've posted these
db normalization, said to be the foundation. I tend to think of it as the footing in constructing a good db, but I think you'll get the point. You might want to take a crack at it in a new db (keep what you've got), create the relationships and post a pic of it here if you want feedback. Could also post that db (usually must be zipped). Do follow all of the links (except maybe diagramming and "how do I...") before you build anything. Hopefully they're all current, but some might have become outdated.

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com...on-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.com...ng-part-i.html

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.com...cation-in.html

Important for success:
Naming conventions - http://access.mvps.org/access/general/gen0012.htm
https://www.access-programmers.co.uk...d.php?t=225837

What not to use in names
- http://allenbrowne.com/AppIssueBadWord.html

About Auto Numbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html
About Multi Value Fields - http://www.mendipdatasystems.co.uk/m...lds/4594468763

[Western_Neil]

Totally agree with MICRON. Excel is designed for human interface, good databases not so much.

For example an alarm has only a couple of states, which go into a table. Models of alarms (XYZ) go into another table. These two tables could be referenced by a third table using the Primary Keys (PK) which are commonly numbers. A human looking at the third table will have a hard time understanding it, but the program handles this with no problems.

The human interface in a database is the reports and forms which convert PKs into understandable things. You can make a database in excel, but the program is not optimized for that and would be slow and clunky.

[Micron]

Not to mention that you can't have concurrent users in a workbook (at least couldn't last time I checked) and while Access security is no where near bullet proof, Excel's is worse.

[Sergdor]

Once I get through all of the information Ill work on putting a new DB together from what I am learning. I can already see some errors in my original thought process that I was able to easily correct.

Thankfully I dont have much data at the moment so entering information once the new database is setup wont take very long.

[Micron]

Good luck!

[Sergdor]

Totally agree with MICRON. Excel is designed for human interface, good databases not so much.

For example an alarm has only a couple of states, which go into a table. Models of alarms (XYZ) go into another table. These two tables could be referenced by a third table using the Primary Keys (PK) which are commonly numbers. A human looking at the third table will have a hard time understanding it, but the program handles this with no problems.

The human interface in a database is the reports and forms which convert PKs into understandable things. You can make a database in excel, but the program is not optimized for that and would be slow and clunky.

Though the system I work on calls them alarms maybe I should of used a different term. There are about 150 different alarm message that can be reported by the system and I am looking to log these when they are reported by the user on a quarterly basis. This is used for us to perform distance support with the users to help correct possible issues with their system.

When they provide the quarterly report for their system I need to list each of the reported alarms and log these so overtime we can do some trend analysis on common alarms. We also have them provide a list of interfaces that are degraded or inoperable. These will also get listed and tracked for additional trend analysis of the system. I am hoping to make a form to submit all this data so in 3 years I can look back and see system X had 23 alarms and 34 interfaces down/inop and use this to improve our training and equipment.

[Sergdor]

Good luck!

Thanks. Read all of the information you sent and feel I learned a good bit. I dont think im any closer to my end goal. gonna need to rest on this one a bit and hope it comes to me in the next few days.

Ill need to find some more examples online that are similar to what I am performing. I cant seem to correlate the information provided to the what I am looking to perform.

[Micron]

I cant seem to correlate the information provided to the what I am looking to perform

Not sure what you're trying to do is clear yet. Is this all about reporting on equipment condition monitoring history? It doesn't read like it's about real time status.

You mention reports, so as long as you build the schema correctly and the data is sound, I don't think you should have any unsolvable issues. At times it will just be about how to do something as opposed to fixing why you can't. A good understanding of the process that the db needs to support would be required in order to be able to provide focused advice, even if you do post a pic of your relationships, assuming you go that far before anyone vets your proposed design.

[Sergdor]

All of the systems we support are remote and the report I get is a snapshot in time. we use that to provide direct support for any listed alarms and degraded interfaces listed. i need to log all of the issues reported to build a database which can be used to evaluate the data overtime.

at the end of each quarter i need to provide a detail report of common failures, system/site with the most issues and total issues for that quarter.

The information will need to be saved correctly and with as few steps as possible which has led me to access and away from excel. in the past we have used excel for this tracking but want to begin using access and forms to simplify the process.

[Micron]

Sounds very doable.