Lost in Access

[gingy5537]

Hi Everyone

Not sure if this is the correct category to post this in but let’s try.

I’m trying to create a Corrective Action Procedure database within access. I would like to have a type of a dynamic form depending on the answers selected. I haven’t used Access since 2007 and I’m a bit lost within it.

To explain myself better I will use an example of troubleshooting an PC. So, we would like to open the application and on Form 1 select the model, for this exercise let’s say we select Desktop this then needs to trigger a selection of Yes/No answers related to only a desktop.

Question 1, Check if the power cord is connected. If you click Yes, it needs to go to the question 2. If you select No it should give the corrective of Reconnect power cord, when you then select yes for reconnecting the power cord, then display question 2.

My idea of the Database I would have 3 Tables, model_of_pc (ID,model_number), action(ID,model_number,action,yes/no) and corrective_action(ID,action,corrective_action,yes/no).

I’m not sure how to create the for to dynamically display the question with the yes/no triggers.

Thanks in advance!

 

[Ranman256]

rather than building & programming all these various answers for a user to click thru,
couldnt you just show a list of all the steps to do?
No clicks, no programming, just let the user try steps 1 thru 5 shown.

 

[jdraw]

It may be too early for Access. Perhaps finding and recording the steps for generic troubleshooting from several sites would be a more practical strategy.
You may start with something like this and supplement with your experience.

General Steps to Troubleshoot an Issue

docs.oracle.com

 

[gingy5537]

Hi, thanks for the responses, so my PC example might be a bit too easy, we want to use this for a much more complex systems involving the use of engineering equipment.

I was just trying to get the basis of the scope accross. Currently it is a paperbased system, which we would like to record digitally via a tablet for record keeping. (Hence the Yes/No button) The list normally runs up to about 55 actions that needs to be verified. With multiple corrective actions that needs to be followed.

The whole list of 55 needs to be completed and recorded before use. The list also differs per model of the equipment.

 

[jdraw]

Many troubleshooting processes are documented with flowcharts.
Try Google "general troubleshooting flowchart" adapt as necessary. You can have multiple layers of such charts.
See this one. And this one from Arnold.
Here's another sample of troubleshooting (furnace problem).
Your paper-based system must have a lot of relevant info that can help with any automation.

As with any database issue, you have to do the analysis and design and vetting before getting in to physical database.

 

[oleronesoftwares]

If you have someone in house who is good in flow chart design, work with the person to design or you can use cases to define for what needs to be achieved in the system

 

[The_Doc_Man]

The advice I usually give might be relevant, if also somewhat daunting.

Rule #1: If you can't do it on paper, you can't do it in Access.

Translation: You need to be able to do this kind of work on paper including the decision of what questions to ask for each given choice. There are many ways to do this but if you don't have it laid out in some kind of guiding document, you will all too often sit and stare at the display, trying to figure out what you really needed to do. Then you will get frustrated because your attention is in the wrong place. By working on paper FIRST, you put your entire attention where it belongs - on the problem. By doing a paper mock-up of the data flow as you go through this problem, you give yourself hard documentation of what you really need to do at each point. Think of doing it on paper as devising a roadmap. If you are on a journey but have no map, how will you ever know when you have arrived? (In fact, the answer to that last question - detailing how you know you have finished your journey - might be part of the documentation. There is another aspect of doing the "on paper" first. Old Julius Caesar used the "divide and conquer" method to assure that he was biting off workable chunks. By splitting the analysis from the implementation, you are doing a "divide and conquer" on your problem.

Rule #2: Access won't tell you anything that you didn't tell Access first.

If you want a particular output, you have to assure you have a source for the data of that output. This might mean putting everything on paper (see #1 above) and then working backwards for every desired output to verify that you have a corresponding input. Remember this: Access knows only how to build tables, queries, forms, reports, macros, and modules. YOU are the subject matter expert. You know that, of course, from working with Access 2007, but sometimes we need a reminder.

 

[Mike Krailo]

Hmmm, I could see the usefulness of creating something that showed the symptoms of the problem just by changing the condition of a given component in the system (like a PC, or a Projector, or a rack of PC's, or a key Server, or a Network Switch, or a Video Switch, etc...) Drilling further down to component level would really complicate things but it could be done if you have the time. You would need to account for every possible failure condition and preferably simulate it in the database. The starting point would be the flow chart eluded to by @oleronesoftwares

If each system is related to each other in a specific way to do something more complex like maintain a huge server farm, automobiles of certain makes and models, or even a Flight Simulator. Then I can see creating a database like this. The only problem is, it would take a long time to make it operational. There is going to be a lot of preliminary work involved in this project because only someone with the final rendition of the flow chart of each failure condition would be able to even start working on this in a database.

Then you could run into the situation where "What if parts of the current system is changed or modified?", then there would have to be a way to update workings of logic behind the troubleshooting further down the road.

 

[LarryE]

It sounds like you have:

1. multiple equipment types
2. with multiple makes and models and serial numbers
3. which could be serviced by multiple people
4. on multiple service dates
5. each service date having multiple questions
6. each question with a Yes or No answer

Is that correct? Because as previously mentioned, a successful implementation requires a proper design and that means deciding what tables you need and how those tables relate to one another.

 

[Pat Hartman]

The one thing you do NOT want to do is to have to write code to create this network. That means you need to come up with a way of using tables and relationships to control the flow. This design is not for the faint of heart. You can probably get close to what you want with just self referencing tables but you will need the ability to interject code at some points.

Start with a flow chart for each of several processes. Be as detailed as you can be. This is the "paper" step Doc referred to. Then think about how to tablize the decision making process.

Do some research on truth tables. No one talks about them any more but they are excellent at concisely defining complex decision trees.

 

[NauticalGent]

Do some research on truth tables.

Learn something new every day...

 

[Pat Hartman]

Here's a Truth table for determining the number of days in February. The number of rows in a truth table is normally 2 to the power of x with x being the number of questions. So this sample would normally have 8 rows 2**3 = 8 BUT, some of the conditions are irrelevant so it is reduced to four rows. In this sample, if a year isn't divisible by four, it cannot possibly be divisible by 100 or 400 and if it isn't divisible by 100, it cannot possibly be divisible by 400. So, usually, you create all the details and summarize later. This one is simple enough to summarize immediately.

Most of the on line references will be explained in mathematical terms. So, it isn't clear how you would employ the technique in code. This example is more of a "cascading" AND. Rather than a straight AND/OR/Not/NOR/XOR/NAND.
Truth tables are used whenever you have complex logic so you can ensure that you handle all possible permutations of truth.

To represent programming logic, you can even "embed" complex answers so if row 1 actually needed further logic, you would replace the "answer", which is shown as 28, with the name of a second decision table with more detail logic. Use them to organize/simplify your thought process.

 

[LarryE]

You may be able to use a table design like below:

You need to allow for multiple equipment types, serviced by different technicians on different dates using different questionnaires. As mentioned above, it's not for the faint of heart, but can be done with some careful planning.

 

[Isaac]

one more thought.

i think you've gotten some good advice on this thread, although i think it's perfectly fine to use a database for this if you really wanted.........BUT...as others have mentioned, if you're just beginning in access, the best way to begin is to learn how to model data into tables, then move on to queries, and lastly, forms. even though forms might be the first thing you want, it's actually the last thing to program.
so, taking that into account, if you're new to access OR you feel lost even without being brand new, this might not be the best use case for building a database.

honestly, a viable solution may be (as they have said) a flowchart,,,,,,OR if you do want something a bit niftier and more automated, i think you could do all this with one excel sheet and some creative VBA. it might be a fun chance to learn some vba.

apparently i can't attach spreadsheets to awf any more for some reason ( ) so i'm sending a picture of my spreadsheet to give you the idea, and a snippet of code. this may give you another idea! this does actually work:

of course you might tie this to a Userform object in excel to give it a GUI.

there are a million ways to make this approach stink less, (ha), but this was just done in a minute - to help you see other possibilities, if a DB seems like overkill:

Sub GuideMe()

Dim ws As Worksheet, rngAllConditions As Range, rng As Range
Dim blResolved As Boolean

Set ws = ThisWorkbook.Worksheets("Sheet1")
Set rngAllConditions = ws.Range("a2:a8")

Set rng = ws.Range("A2")
Do Until (blResolved = True) Or (rng.Row = 9)
    
    If MsgBox("Is this true: " & rng.Value & "?", vbYesNo, "  ") = vbYes Then 'then the first possible cause condition was TRUE
        If MsgBox("Do this, then click Yes or No for whether it worked: " & rng.Offset(0, 1).Value, vbYesNo, "  ") = vbYes Then 'it worked, exit loop after marking resolved!
            blResolved = True
        Else 'first try didn't work, move down to next possible cause condition
            Set rng = rng.Offset(1, 0)
        End If
    Else 'else the first possible cause condition wasn't true...move to next possible cause condition
        Set rng = rng.Offset(1, 0)
    End If

Loop

If blResolved = True Then
    MsgBox "Congratulations, you got it done", vbInformation, "  "
Else
    MsgBox "Sorry, call next level help support", vbInformation, "  "
End If

End Sub

 

[LarryE]

I was interested in how to provide a solution using ACCESS because it a rather complex question and I love a challenge and have been working on it. If you are still interested in a solution using ACCESS, please let us know. Thank you.

 

[gingy5537]

Hi Everyone! Thanks for all the replies and valuable info!

We realised after this that it is going to be quiet complex. I will work through all your suggestions and see what fits our need. For now were following Isaacs suggestion and building the base in excel with some VBA scripts.

From there we can always evolve it into a proper database with time.

Thanks again
Wian

 

[Pat Hartman]

We were not trying to dissuade you from using Access, only to emphasize the complexity and the need for detailed planning. Inexperienced people tend to just jump right in to building forms without much consideration thought regarding the schema and that won't work for something like this. I can't see that using Excel resolves any problems.