Perform Calculation on text in a range of fields using SQL

[rpeare]

TO go along with john's suggestions, I would recommend you change them from text values to number values where 1 would be pass 0 would be fail, something like that, it will make your aggregate calculations a lot easier.



There are many things you can do to alleviate the burden of data entry for an example like this, if every person will have a value for every test (in other words everyone is getting the same 55 tests and there will be a value for every one of those tests) you can build your data entry around that and just bury some of the repetition in your form whether you are using a bound or unbound form., theoretically if it's just entering a pass/fail on 55 different tests and you are entering those tests in order the only thing you should have to enter is the test result (a single keystroke for pass or fail).

[Alhassani]

Ok, so big thanks to both rpeare and John_G for helping me out on this. I've restructed the database and normalized the database. I had a resident programming expert look at the new set-up based on the input from both of you and he said it was perfect. I've created an append query to update the second table used to store pass and fail information based on what is entered into the first table. The main "AuditDb" table will still hold all information as is due to it already being set up and acting as the foundation for all queries to then branch from. The problem I'm running into is:

1.) The append query will only append the results from one column at a time and therefore needs to be built individually for all 55 columns (fine with this, but hoping there's a better way).
2.) The append query updates all records every time by just adding to the bottom of the "Audit_Scores" table without checking to see if the data is already there.

I'm working with some VBA to alleviate this, but I'm running into problems with it.

Quick question for forum etiquette, should I mark this post solved and create a new post based on my current VBA issue? Or, continue posting on this existing thread? I'll wait for a response before I delve any further into the VBA problems.

[rpeare]

Are we talking about doing your data conversion here? in other words the process to get your existing records into your new table structure? Or are you talking about your normal day to day editing/adding data?

[Alhassani]

Day to day editing/adding. I've created a composite primary key in the new table so the multiple record adding is no longer an issue, and makes the VBA no longer needed.

The problem I'm running into now is the box that comes up letting you know what records were appended and what records were not. I don't want my users to have that option. Ideally, they would put in the data and either when the data changes or when they hit save, all the queries will run in the background and update all records as needed.

If I can't stop the message box through VBA or some other option, then I'll take that function completely away from the users and make it a daily administrative function.

Any recommendations?

[Alhassani]

Turns out this is a basic option in the Access Options menu. Problem solved.

[rpeare]

I wouldn't suppress ALL messages because it basically means you won't get a notification anytime anything goes wrong.

You can temporarily suspend the warning messages with

docmd.setwarnings false
'do your append query stuff here
docmd.setwarnings true

that way if you get an error somewhere else in your code you'll still get the warnings to help you track them down.

[Alhassani]

So, I've put 2 different ways of executing this query using vba. Unfortunately, neither way is actually executing the command. When I check the output file at the end, nothing is updated, but when I manually run the query it works fine. The two examples are below.

Method 1: After Update event once the DCN is entered since this field is absoutely required every time.

Private Sub DCN_AfterUpdate()
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "Grades_Qry", acViewNormal, acEdit
    DoCmd.SetWarnings True
    
End Sub

Method 2: Each combo box is updated with an on change event. The default value is set to "Pass", so I don't know if this triggers a change event from a null value upon creation of the form. Regardless, every when changing the combo box it does not run the query as is currently set-up in VBA format.

Private Sub PCH__Sponsor_SSN_Change()
Dim strSQL As String
strSQL = _
    "Insert Into " & Audit_Scores & "(DCN, Audit_Field, Field_Result) " & _
    "Select Audit_Db.DCN, " & "PCH: Sponsor SSN " & "As Audit_Field " & _
    "Audit_Db.[PCH: Sponsor SSN] " & "As Field_Result " & _
    " From Audit_Db "
    
End Sub

I'm also following all of this with an on save event (intended to, can't find an "On Save" event option on the form), that would then update the existing records with any subsequent changes. Again, the actual query works perfect, but I haven't built this into any type of VBA set-up yet.

[rpeare]

in the first, assuming grades_qry is an update query remove everything after the query name all you need is

docmd.openquery "Grades_Qry"

in the second you are generating the SQL string but you aren't executing it you would need something more like:

Private Sub PCH__Sponsor_SSN_Change()
Dim strSQL As String
dim db as database

strSQL = _
    "Insert Into " & Audit_Scores & "(DCN, Audit_Field, Field_Result) " & _
    "Select Audit_Db.DCN, " & "PCH: Sponsor SSN " & "As Audit_Field " & _
    "Audit_Db.[PCH: Sponsor SSN] " & "As Field_Result " & _
    " From Audit_Db "
    
set db = currentdb
db.execute (strSQL)
End Sub

[Alhassani]

Ahh ok, that works. haven't tested the second yet but that fixed worked for the first.

So, from a design perspective: after inserting all the records into the 2nd table when the DCN is entered, there may need to be updates. Would it make more sense to use the vba code given in the second example on every field to make any updates, or to have it update all 55 values on save or unload?

I'm thinking of putting it on the combo box on change event, so then the code would only run when needed instead of an extra 55 iterations every time.

[rpeare]

I don't know what you settled on for your data structure or for your form. If you're using unbound controls with a normalized data structure it might be more efficient to do lots of smaller updates because they would only happen when you update a specific data point, if you are still using a non normalized structure a single update with all fields would likely be more efficient. As with all things access, there's more than one possible answer which depend radically on your data structure.

[Alhassani]

I switched to a normalized data structure yesterday. It has helped make problem solving so much easier. I went ahead and did the update query on the change command with each combo box, only its acting wierd. I can't figure out "exactly" what triggers an on change command to test this precisely. Basically, if I change it once, nothing really happens. But if I change it multiple times, it will update to the selection prior to exiting the form. So, if I change it from Pass to FYI, and then FYI to FYIP when I check the table it is supposed to update, it will show FYI. Really, really wierd. Still working with how to make this consistent so I can figure out how to pinpoint the problem.

[rpeare]

likely it's because FYI and FYIP are so similar if your combo box is listing those in order you may be 'selecting' the wrong one.

The ON CHANGE is triggered when the value changes. So if you open your form and it changes from a null to PASS your table should be updated. If you select PASS again from the same combo box nothing is going to happen.

it may help you to put debug.print statements in your code so you can track what/where things are getting triggered

[Alhassani]

Ok, so that was a bad example. I'm chosing drastically different options. One is Pass, one is FYI, and one is DC24A. I have moved the code from OnChange to AfterUpdate because that seems to make more sense. The main database is not updated with the new value only when I hit the save button. I still can't figure out how the secondary database is getting updated. The code is below.

 Private Sub PCH__Sponsor_SSN_AfterUpdate()
Dim strSQL As String
Dim db As Database
strSQL = _
    "UPDATE Audit_Db RIGHT JOIN Audit_Scores ON " & _
    "Audit_Db.DCN = Audit_Scores.DCN SET Audit_Scores.Field_Result = Audit_Db.[PCH: Sponsor SSN]"
Set db = CurrentDb
db.Execute (strSQL)
End Sub

Also, I tried doing a debug.print command, but nothign came up. What is the correct syntax for that?

[rpeare]

if you want to see your sql statment you'd do

debug.print strsql before you execute it, then you could cut and paste the string to an actual query window to see what it's coming up with to verify it's doing what you want it to.

[Alhassani]

Thanks for all the help on this guys. I think we're really starting to veer off topic from what the original question was. Short answer to my question was to restructure the database using a normalized database structure that included adding a second table to store the results in a column instead of a row. We then needed to create queries to pull the information from the table we added in order to generate the desired results.

Whomever is in charge of marking the posts, please mark this solved. I will be posting my next question in a new thread. Thanks again to rpeare and John_G for all the help!