Exploring possibilities

[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.

 

[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.

 

[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

 

[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.

 

[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.

 

[561414]

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.

Oh my
READ-THE-THREAD, super moderator.