Exploring possibilities

[Pat Hartman]

Are you going to tell me you have never required to make a change in your SQL schema?

Of course I (we) have. But you don't design a schema to force yourself to modify it to add a new "employee" which is essentially what your proposal was to do. You don't use columns to hold instances of data. Questions are employees or vendors or parts or assets, etc. They are equivalent to any other entity. A survey should hold any number of questions because questions are rows of a child table NOT columns in the survey table. Would you add 10 columns to an employee table to hold the names of his dependents? Then if you have to add birthdays for them, you've got to add an additional 10 columns. Then what if some employee has 11 children?

If the OP turns out to need 91 questions, he has to change all the forms and queries and reports that work with this data. What if some analysis needs to be done with the answers. Count the number of true answers for example. That's a pretty long expression to include 90 IIf() statements. Good luck with that.

 

[Pat Hartman]

@TraumaDoc - sorry the picture was from an old version of the app. I did a little clean up. Here's an updated version.

This is a "simple" example. It uses Y/N for the response fields. You could change that to text fields or numbers or a combo. You could even add an additional column to identify what type of response is required and than alter the display to display that type of control so you could use "all of the above" and have different questions use different answer types. I do this in one of my apps but it is too much trouble for a simple example so the example picked one type and used that for all questions. A common combo would be numbers 1-5 defining satisfaction ratings.

 

[561414]

@Pat Hartman
My proposal was this first, then I added the possibility of another design that is simpler and, if the extra field(s) were to be added some time in the future, extending the schema is not that big of a deal. Since the title of the thread is "Exploring possibilities", that is one. I did not read the OP mentioned the necessity of creating different surveys.

This whole post is unnecessary, but if you want to subscribe it to me as something you think I do, well, what else can I say?

Of course I (we) have. But you don't design a schema to force yourself to modify the it to add a new "employee" which is essentially what your proposal was to do. You don't use columns to hold instances of data. Questions are employees or vendors or parts or assets, etc. They are equivalent to any other entity. A survey should hold any number of questions because questions are rows of a child table NOT columns in the survey table. Would you add 10 columns to an employee table to hold the names of his dependents? Then if you have to add birthdays for them, you've got to add an additional 10 columns. Then what if some employee has 11 children?

If the OP turns out to need 91 questions, he has to change all the forms and queries and reports that work with this data. What if some analysis needs to be done with the answers. Count the number of true answers for example. That's a pretty long expression to include 90 IIf() statements. Good luck with that.

 

[ebs17]

On the topic of SQL and database schema:
Most of the tasks that occur here are not just about being able to store any data in any fields and being able to retrieve it quickly and easily from there.

One of the main tasks of SQL is data evaluation, namely high-performance data evaluation, also and especially with large amounts of data. Then there is the SQL language, which has to be based on certain standardized structures so that it can work efficiently, just like a sports car can develop to the maximum on a (closed-off) expressway while having its problems in the woods and swamps.

When it comes to the question of SQL versus NoSQL, the question arises as to whether you will mainly operate data flippers or do massive calculations with the data.

 

[Galaxiom]

The schema you have provided shows a composite primary key, which does align to OP's initial schema, but contains no relationships. Therefore, no referential integrity is applied, data is on the fly and actions do not cascade.

I said it was similar. I didn't say it was exactly what they needed.

If you read the context of the original thread you would realise the sample database was provided only to demonstrate how to add records to a table without having to prepopulate the table with empty records.

Therefore, no referential integrity is applied, data is on the fly and actions do not cascade.

Relying on cascading updates and deletes is not something recommended by most professional developers.

A proof of the latter point is that you can not add new records, you can only edit one at any time, which leads me to believe the designer expects the end user to make additions directly on the tables,

As I said it wan't a complete solution which would include forms to add the records to the other tables. Focus on how the records are added to the table from the form.

Are you going to tell me you have never required to make a change in your SQL schema?

Rarely. It is a fundamental element of good design to support extensibility without altering tables and forms. Well designed databases are extensible by adding records not fields or tables, especially when it is obvious that the need for more elements of the data is so likely as in the the case of this thread.

You have a lot to learn about database design but unfortunately your disposition is will stand in the way of benefiting from the experience of developers who are way above your level.

 

[Pat Hartman]

extending the schema is not that big of a deal

You miss the point of a survey. It isn't just saving the answers to the questions, it is analyzing them or at least counting the responses and you DO NOT want to do that with 90+ separate fields. That is insane. Take your spreadsheet hat off because SQL doesn't have functions that work on columns. They all work on rows. The "sets" are sets of rows, never columns.

You might want to cut your losses and stop trying to defend your position. This is not a discussion you will win and you are showing your lack of understanding of even first normal form.

 

[561414]

When it comes to the question of SQL versus NoSQL, the question arises as to whether you will mainly operate data flippers or do massive calculations with the data.

That's an amazing point, we must always use the right tools for the job. If we depend too much on our golden hammer, we just won't grow. Besides, it is also good to know we can do a lot of processing client-side to avoid overloading our database systems. It all depends on the architecture we choose to use, but if at any point, we notice we did not make the right choice, we should not be religious about it and make the changes necessary. I am guilty of such a thing, I have made mistakes that needed large scale changes and paid with my time, things like that happen because we rely too much on just one thing.

I am such a grand developer, trust me, I make no mistakes

You can do better than this, Galaxiom.

 

[561414]

You might want to cut your losses and stop trying to defend your position. This is not a discussion you will win and you are showing your lack of understanding of even first normal form.

It is important to approach debates with an open mind and a willingness to consider different perspectives, rather than simply focusing on "winning" at all costs, disqualifying the unknown and discrediting that who does not think like you. In fact, a healthy and productive debate should ultimately lead to a deeper understanding of the topic at hand, regardless of who "wins" or "loses."

I have a lack of understanding of soooo many things, Pat. It's why I'm never claiming expertise, not even database design because that would be arrogant of me. As a competent developer, the first thing I proposed to the OP was a many to many relationship with criteria as records, and then proposed another possibility that, in the scenario of rare change, it can work with very little effort, it's valid, it can solve the problem at hand. You refuse to believe that a schema can and will change often times, then you claim to know it all when you also make mistakes, like everyone else, it's no big deal, Pat. I remember a recent thread where I am suggesting you to correct a function because it clearly isn't working as you intended, but I got no response from you, were you cutting your losses instead of coming back and telling everyone, "hey, yes, that was a mistake, thanks for pointing it out"?

It has been the case many times, it's why I'm in this forum. In attempts to learn something from the posters with large post counts, I have seen some bad stuff, you don't like it when it's pointed out, and that's OK, but have some humillity, be human beings, it's no problem. We can engage in constructive threads. They don't have to be like this thread because you want to win at all cost.

 

[Pat Hartman]

I have a lack of understanding of soooo many things, Pat.

And yet, here you are, arguing to justify something that is just plain wrong at the most basic level of database design.

You refuse to believe that a schema can and will change often times,

Wrong. Plus we are talking about a very specific situation which is referred to as a repeating group and you are refusing to understand why a repeating group is NEVER stored flat (except possibly in some cases for a data warehouse). It is always a child table with a row for each value rather than multiple columns in a single row.

I am suggesting you to correct a function because it clearly isn't working as you intended, but I got no response from you, were you cutting your losses instead of coming back and telling everyone, "hey, yes, that was a mistake, thanks for pointing it out"?

I certainly would have fixed it if I had seen your remark. You can say that I post "bad" answers all you want but please point out a "bad" answer that I didn't correct. You may disagree with my suggestions but that is your opinion. "bad" is something i suggested that actually won't work.

I don't argue with people over a difference of opinion. If both a and b work, it doesn't matter which you choose although I might point out that certain methods such as using domain functions in queries or VBA loops work fine when you have a hundred rows of test data in your table but once you get to thousands, there are better alternatives so you might as well start with the better alternative rather than end up being forced to correct a problem under pressure later. What you are suggesting violates first normal form. That is wrong in the world of relational databases. You may choose whatever wrong techniques you want but when you recommend them to people who don't know better, someone has to point out the problem.

Surveys are special, well understood, standard patterns. A business who takes a survey does so because they want to analyze the answers. The answers are not going to just be entered and only used for display thereafter. Please, just to convince yourself that repeating groups are not stored flat - Assume that each item answer has a combo with 5 choices. Make a query that counts how many of each value each of the 90 columns has.

The query using the normalized schema would be:

Select Question, Answer, Count(*) as AnsCount
From YourTable
Group by Question, Answer;

 

[561414]

Select Question, Answer, Count(*) as AnsCount
From YourTable
Group by Question, Answer;

Even if I wanted to do it via SQL, there would be a way. You would know it had you read the entire thread and its context. But you went overboard to try to prove your points without considering even what OP needs.

Here's the possibility I've been talking about that you and your friend have been arguing over. It's so complicated, Pat.

 

[TraumaDoc]

This project started as an Excel spreadsheet. In that form I had 3 columns ...one to show the ID # of the criteria, one to show the text of the criteria, and one for the grade. So I had 90 rows that could be pre-populated with the criteria ID, criteria text and the grade was blank..to be added during the review. I actually had that column with conditional formatting so when "red" was selected from a drop down, the whole cell just turned red. When completed this was exported as a PDF to be included in a facility report. Works for one facility but very clunky for several or many and I think just shows that Excel is the wrong tool for the job. Thus my move to Access.

Reflecting on the positive points of the Excel method, it was nice to have 90 rows all preset and ready to grade. It was cool to use the conditional formatting too.

What do I need in Access?
I need to create a report card that shows facility, date of assessment, each criteria with its grade.
I want entry, ie filling out the report card, to be easy. In this sense a form with the facility and date at the top (parent form) and the list of all 90 criteria below with any empty box to specify the grade as a color (subform).
I want to be able to track historic performance. How many red last review compared to this review. What about a trend of performance for one specific criteria? But otherwise I dont project any complex statistics.
There will be multiple people performing the assessments and filling out the report cards.

At times I feel like I'm very close to having all of that, and then I get overwhelmed and it all seems to fall to pieces.

I really appreciate everyone's comments and I will keep at it. I've already got a second project on deck (OR scheduling) and I'm committed to becoming adept at Access.

sf

 

[Pat Hartman]

@561414 Even if I wanted to do it via SQL, there would be a way.

Of course there's a way. There's always a way. How much time do you have? The simplest solution would be to write VBA to normalize the flat table's columns to rows and then you could use the query I suggested. But, pivoting 90 columns isn't trivial and you will almost certainly run into limits in the number of queries you can use in any one union query so you would need to stack unions on top of unions. You were so convinced that your way was a viable solution, I really think that you should take the time to actually write the VBA and SQL needed to produce the result I described. That is the only way you are going to understand the problem.

@TraumaDoc
Using a schema similar to what I suggested for a survey, you would run an append query to copy the "90" rows of the questions for a particular survey to the answers table for an instance of the survey. If you care to look at the sample I posted, that is exactly what the code does. Open the owner/respondent form. Pick a respondent from the combo. Add a new instance of an existing survey to the subform and behind the scenes, an append query runs. Just look at the subform's AfterInsert event code and qAppendSurveyQuestions.

If you want the answers to be color coded, add conditional formatting to the survey response answers subform.

To create your performance trends, you would use queries to calculate averages for different periods and then use queries to combine the different periods to find trends.

You have a choice. If this is the only survey you will ever have to create, then by all means box yourself in and commit to one and only one survey. If you see a future use, then you might want to consider making a very small extra effort to support multiple surveys and taking the same survey multiple times.

I added conditional formatting to the answers

 

[561414]

I really think that you should take the time to actually write the VBA and SQL needed to produce the result I described. That is the only way you are going to understand the problem.

I don't know how many times I have written that my first suggestion was a many to many approach, Pat Are you OK?
I even posted a database in post #30. I already wrote the VBA code there. In fact, how do you want it? you want some SQL string? Let's cook that thing, it's gonna be easy. Hold on.

 

[Galaxiom]

Using a schema similar to what I suggested for a survey, you would run an append query to copy the "90" rows of the questions for a particular survey to the answers table for an instance of the survey.

That "append" is exactly the clumsy process that the technique in my sample database avoids entirely. However Pat's suggestion is a solution frequently used by many developers.

Unfortunately 561414 has totally derailed the thread with their obsession of trying to justify their extremely amateurish concept. TraumaDoc was on the right path in the first place and just needed help with the form structure.

 

[Pat Hartman]

Correct. There wasn't anything wrong with your first suggestion.

 

[Pat Hartman]

That "append" is exactly the clumsy process that the technique in my sample database avoids entirely. However Pat's suggestion is a solution frequently used by many developers.

That's a narrow minded opinion. When you always need a complete set of answers, simply appending them all at once can eliminate problems. If all items were not needed, then I agree, the append query would be a poor choice. For a sparse set, items should be added only as they have values and a left join serves a similar purpose. But, we don't always have to use a single method. Sometimes we can make distinctions based on desired outcomes and user preferences There is even a third option which filters the combo's RowSource to remove already added items. And given some thought, there are probably other options.

 

[561414]

@Galaxiom I'm a guy, and the obsessed one is not me, I've been trying to bring the thread back on track for a while already
@Pat Hartman There, parameter query, done, sql, rad tools and everything. It can be done as per the initial request. Things appear to have changed now that OP mentioned extra stuff required.

 

[561414]

TraumaDoc was on the right path in the first place and just needed help with the form structure

Dude, it was YOU who derailed the thing and it was ME who said he only needed help with the forms
Some serious stuff is wrong over here.

 

[561414]

OP, it's been a fun day. Here, this is a good basis for your project.

 

[Pat Hartman]

You didn't add 90 columns and you didn't count the number of "red" RECORDS for each column of the set. You counted the number of "red" columns within ONE record which was not the problem posed. In a survey, it wouldn't make any sense to count across. You want to know how many people answered red to question 1. How many answered red to question 2, etc.

We can be done.