Perform Calculation on text in a range of fields using SQL

[Alhassani]

Good morning,

I am trying to perform a calculation on an individual record that returns the amount of passes against amount of fails as a percentage. This will be going into a database with an expected entry of 50,000 records a year. For reporting purposes, all of the records are assigned scores that are then filtered by various factors such as employee, shift, training group, etc. and then assigned group calculations based on the filtering. A sample is set below with 6 columns. The actual dataset has 55 columns that it needs to pull from.

I am thinking I would first need to define the columns as a group, and then perform percentage calculations using the count function on the text entires in that group. This is because I first need to get the total percentage of all 55 fields, and then need to pull 30 specific fields and get the percentage of only those 30 fields. I have not been able to find any SQL examples online that demonstrate this specifically. All of the example show counting many records at once and averaging a specific column. I am needing the opposite, many columns at once on a specific record and tied to that record's unique ID.

Example.

Name	Field 1	Field 2	Field 3	Field 4	Field 5	Unique ID
John Doe	Pass	Fail	Pass	Pass	Pass	45654

So, for the first calculation I would need to Count the total number of fields (5), then count the number of fields that say "Pass" (4), divide the two and return an 80.00%.

The second calculation would need to look specifically at fields 2, 4, and 5 (the actual 30 fields are spread through out the table like this for orgnization purposes). The count of fields would be 3, the count of passes would be 2 so it should return 66.66%.

I can do this in Excel all day, SQL and access... not so much. Also, I've been told to try everything from hidden subtables to groupby and redefining input parameters as numbers to make this work. It has been a little bit of information overload, so if this is a completely asinine way of completing this task, please let me know if there is a better way.

[ranman256]

You could keep the table as NAME, FIELD1, PASS/FAIL
Then a crosstab query to get your example above : NAME, FIELD1, FIELD2, ...
Then get your stats from the crosstab.

[Alhassani]

Ranman,

Thank you for the input. I am not familiar with the crosstab query. After some review, it appears that it combines the records into a single record. Is this correct? If it does then I'm not sure this will work for me as this recreates the first problem I am trying to overcome. Which, is calculating all columns on a single record to generate a single score.

[John_G]

Hi -

If you can do so, I strongly suggest you restructure your database to properly normalize it, because the problems you are having are one of the effects of non-normalized data.

Instead if having all your scores in the same record, you should have another table for the scores (Call it Test_Scores here) , with each record containing only one score for one person, e.g. :

Unique_ID ' Unique ID of the employee
Field_ID ' Some field identifier
Result ' Result of the test T/F

If you make "Field_ID" numeric, and use it as a FK to another table describing the fields it will help.

Now you can easily use DCount and DSum domain functions to get what you need from the Test_Scores table, using Unique_ID as the criteria.

Then when you need a specific 30 of the scores, you can add the Field_ID to the criteria (I suggest you use consecutive ID's for those 30 - makes the criteria much easier to code).

HTH

John

[Alhassani]

Hi John,

I'm nto sure I am understanding you correctly. You are saying to create a new table that records a score for each record? Then, when running queries to reference the new table? Or, are you saying something else?

I'm still not wrapping my head around how to generate the scores to pull into the new database for the record with this method. Also, can you clarify a little bit on "non-normalized" data? This is for an audit sheet that records 55 fields per audit, is there a better way to record this kind of data?

[John_G]

Hi -

Yes, a new table - once employee-score in each record. Do, instead of having 55 fields in each record for scores, as you do now, you will have 55 corresponding records in the new table, one for each score for each employee.

If this was for a questionnaire, the new table would have 3 fields in each record: Employee_ID, question Number, and Response .

Explaining what database normalization is all about would take too much space - but there are many articles written about it. Just search for Database Normalization and you'll get all kinds of information. In your case, though, when you have a table where each record has single fields or field groups that are "repeated" (such as Score1, score2, .....), the data is not properly normalized.

Your method of generating scores would not have to change - it's how you store the results after you calculate them that changes. Are you ever actually printing or displaying those audit sheets (55 fields across is pretty wide), or do you just want the summaries you mentioned? The audit sheets are not recording the data, they are only displaying it. The percentage calculations you referred to, while not impossible, are "very" cumbersome to do in your present arrangement.

If you post a bit more detail on what you want to accomplish, we can give you a bit better guidance on how to procceed.

John

[Alhassani]

John,

Ok, I understand what you are saying. I was working with a fellow employee earlier on this and the direction I came up with is very similar to what you are saying to do. As far as what I am building, here is the link to the original questions I had about this database. In this forum, I attached copies of the database. I've made some changes since then, but for what we're looking at right now it is the same. https://www.accessforums.net/access/...gue-44029.html

This is a quality database. The "AuditDb" table is a representation of paper audit forms that are used in the auditing process. I can understand why you would refer to database normalization, but each column is representative of an individual area that can have an error in our process. In this regard, I believe it is normalized as much as can be. Once the data is transferred from the paper form to the "AuditDb" table, a calculation is then performed on that record (tracked by the field "DCN"). From those calculations, queries are generated to filter and pull data in a number of different ways. i.e. to track their supervisors performance we would pull all records with "Supervisor X" in the section lead field, and then average all of the numbers that are returned in the calculation.

The problem lies in simply getting the calculation. My proposal prior to and in line with your suggestion (and also switching to VBA, not SQL) has been to add a "Save Record?" dialog box to the record's "On Unload" event. I would then add VBA code to the "On Save" event to populate the second table (called "AuditScores") with the correct score for this audit. This way, the calculation is only performed when a record is updated so I'm not constantly running code on 150,000 records. The number is then stored permanently in the second table. The second table is then used for the SQL queries and report generation.

I have this all programmed in Excel already. I can post some of that code if it will help explain what I need, but the first post is an easier to digest answer. I've tried converting the existing formulas in Excel to VBA, but have been unsuccessful so far.

Long story short, I've figured out how to get all the data into Access. Now I need to assign a score to every record that goes in so I can get useful data back out of Access.

[Alhassani]

So, I've been continuing to scour the internet for a solution here. I really did not think it would be so hard to account for assigning a score to every record. At this point I'm considering a few options. The most realistic I can come up with is detailed below.

To achieve the single score on every record:
Originally proposed idea is to
A.) Use VBA or Query to count "Pass" fields against total fields to generate audit specific score. Then,
B.) put the scores on a new table and run further queries off of the new table.

Recent alternative:
A.) Query filter by name and date.
B.) Export to table that produces average scores for every field based on the time frame specified.
C.) Crosstab query those results into a table capable of averaging all scores together.
D.) Generate report.

The second seems like so much work for something that ideally should be really easy. This is a basic count function in Excel, how is this so difficult in Access? The end state result is knowing every employee's score for the week. That score is then rolled into project reports, individual reports, supervisor reports, etc. I just need this number !!

[rpeare]

it's not hard, just cumbersome, for instance you can't just count items because what if there is no value. you have to account for tests not taken yet.

So you'd end up with a formula something like:

(iif([Field_1] = "Pass", 1, 0) + iif([Field_2] = "Pass", 1, 0) + iif([Field_3] = "Pass", 1, 0) + iif([Field_4] = "Pass", 1, 0) + iif([Field_5] = "Pass", 1, 0))/(iif(not isnull(field_1), 1, 0) + iif(not isnull(field_2), 1, 0) + iif(not isnull(field_3), 1, 0) + iif(not isnull(field_4), 1, 0) +iif(not isnull(field_5), 1, 0))

Where you account for the value not being there at all, then you may want to account for the denominator being zero itself to avoid the #ERROR description popping up if there are no recorded values in your columns at all.

As John_G pointed out, having a non-normalized structure makes these calculations extremely tedious to build and test.

Another option is to use a serious of union queries to fake a normalized structure but I honestly do not know if MS Access can handle a union query with 55 different components you would have to have something like

SELECT UNIQUE_ID, FIELD_1 FROM TestTable
UNION ALL
SELECT UNIQUE_ID, FIELD_2 FROM TestTable
UNION ALL
SELECT UNIQUE_ID, FIELD_3 FROM TestTable
UNION ALL
etc...

if you can actually get all 55 of your fields into a union query the formula becomes a lot simpler to calculate the score.

[Alhassani]

Thank rpeare,

You and John are both hitting on normalized data, and even with the research I don't think I'm grasping what you are getting at. So, approaching this problem from that angle, how would you suggest normalizing the data from the 55 fields? There will be 10s of thousands of records in this database, so I want to make sure the queries and formulas are running as smoothly as possible to prevent excessively sucking up processing power.

[rpeare]

Example.

Name	Field 1	Field 2	Field 3	Field 4	Field 5	Unique ID
John Doe	Pass	Fail	Pass	Pass	Pass	45654

This was your original table example.

What you would need to replace this is 2 tables

MainTable
Unique_ID  Person_ID ----> other 'main' testing related fields
45654      1
45655      2

I am assuming you have an employee/person table in your database that you can refer to using a PK/FK (primary key/foreign key).

The second table would be

TestingTable
Testing_ID  Unique_ID  TestNumber  TestResult
1           45654      1           Pass
2           45654      2           Fail
3           45654      3           Pass
4           45654      4           Pass
5           45654      5           Pass

Where TESTING_ID would be an autonumber field and TESTNUMBER would reflect which column the original data came from.

Once you have the table structure for TestingTable built you can run a series of append queries to it using a select query something like

INSERT INTO TestingTable (Unique_ID, TestNumber, TestResult) VALUES (SELECT Unique_ID, 1 as TestNumber, Field_1 FROM MainTable)

I don't know if I have the syntax right (I didn't test it) but basically you'd run an append query to your TESTINGTABLE for each of your 55 testing columns incrementing the value in your TESTNUMBER field in the SELECT part of the statement by 1 each time you run it.

[Alhassani]

Ok, that definitely makes sense. My only hesitation here is that we do 5 audits a week, at 55 fields, on 60 employees. This method for doing the database not only requires me to completely rebuild the database, but also requires adding (5*55*60) 16,500 entries a week or (16,500*52) 858,000 records a year. How bogged down is access going to get calculating in this manner? If it is excessive, I may need to start looking at other programs my company has available to handle a database of this size.

[John_G]

Hi -

I took a quick look at your database, and I am puzzled as to why all (or almost all) if the fields in the AuditDB table are Text-255?

What I think you need is two new tables - one to describe each of those 55 fields, and another to record the audit results.

The first table (Table1) might be something like this:

Audit_Item_ID
Audit_Item_Description

Examples:

1 PCH: Sponsor SSN
2 PCH: Document Type
3 PCH: Beneficiary Last Name
...

And the second table (Table2) might be:

ID (from the existing AuditDB table)
Audit_Item_ID
Item_Status ("Pass"...etc)

So, there will be 55 records in that second table for each record in AuditDB.

With that setup, counting and calculating becomes easy. Suppose the ID of the current record in AuditDB is 15, just to illustrate.

Then, VBA code to calculate the percentage using all 55 criteria is:

Dim Total_Count as integer, Total_Pass as Single, Percent as Single ' Total_Pass is Single to avoid Integer truncation issues
Total_Count = DCount("*", "Table2", "ID = 15)
Total_Pass = Dcount("*", "Table2", "ID = 15 and Item_Status = 'Pass'")
Percent = Total_Pass / Total_Count * 100.0

Nice, easy code. It would be a bit more complex for the special group of 30.

HTH

John

[John_G]

Access should have no problem handling a database of that size. Indexing the fields used for retrievals will make a big difference.

John

[Alhassani]

Ok, from your other post I had drawn out how I was picturing that in my head, and what you just explained is almost exaclty what I had drawn out. (EDIT: thought I was still responding to rpeare, hi again John!)

As far as the text-255 thing goes, the options are all text so I figured that would make sense. Should it be something else?

Also, if you look at the "Audit Input" form, this is how my auditors will be putting the information into the AuditDb table. How would I change the combo boxes at the bottom to reflect entry into subsequent records rather than the same record?