Only half of this Query works

[TheSearcher]

sql2 = "INSERT INTO tbl_Evaluation_Archive (Client_Id, Client_Last_Name, Client_First_Name, F1, F2, F3, F4, F5, F6, F7A, F7B, F8, F9, F10, F11, F12, F13, F14, F15, F16, F17, F18, "
sql2 = sql2 & "F19, F20, F21, F22, F23, F24, F25, F26, F27, F28A, F28B, F29, F30, F30_PRE, F30_POST, F31, F31_PRE, F31_POST, F32, F32_PRE, F32_POST, F33, F33_PRE, F33_POST, F34, F34_PRE, "
sql2 = sql2 & "F34_POST, F35, F35_PRE, F35_POST, Comments, Last_Modified_Date, Modified_By, Completed, Completion_Date, Completed_By, New_Score_4, New_Score_16, New_Score_27, New_Score_28A, New_Score_28B) "
sql2 = sql2 & "SELECT tbl_Evaluation.Client_Id, tbl_Evaluation.Client_Last_Name, tbl_Evaluation.Client_First_Name, tbl_Evaluation.F1, tbl_Evaluation.F2, tbl_Evaluation.F3, tbl_Evaluation.F4, tbl_Evaluation.F5, "
sql2 = sql2 & "tbl_Evaluation.F6 , tbl_Evaluation.F7A, tbl_Evaluation.F7B, tbl_Evaluation.F8, tbl_Evaluation.F9, tbl_Evaluation.F10, tbl_Evaluation.F11, tbl_Evaluation.F12, tbl_Evaluation.F13, "
sql2 = sql2 & "tbl_Evaluation.F14, tbl_Evaluation.F15, tbl_Evaluation.F16, tbl_Evaluation.F17, tbl_Evaluation.F18, tbl_Evaluation.F19, tbl_Evaluation.F20, tbl_Evaluation.F21, tbl_Evaluation.F22, "
sql2 = sql2 & "tbl_Evaluation.F23, tbl_Evaluation.F24, tbl_Evaluation.F25, tbl_Evaluation.F26, tbl_Evaluation.F27, tbl_Evaluation.F28A, tbl_Evaluation.F28B, tbl_Evaluation.F29, tbl_Evaluation.F30, "
sql2 = sql2 & "tbl_Evaluation.F30_PRE, tbl_Evaluation.F30_POST, tbl_Evaluation.F31, tbl_Evaluation.F31_PRE, tbl_Evaluation.F31_POST, tbl_Evaluation.F32, tbl_Evaluation.F32_PRE, tbl_Evaluation.F32_POST, "
sql2 = sql2 & "tbl_Evaluation.F33, tbl_Evaluation.F33_PRE, tbl_Evaluation.F33_POST, tbl_Evaluation.F34, tbl_Evaluation.F34_PRE , tbl_Evaluation.F34_POST, tbl_Evaluation.F35, tbl_Evaluation.F35_PRE, "
sql2 = sql2 & "tbl_Evaluation.F35_POST, tbl_Evaluation.Comments, tbl_Evaluation.Last_Modified_Date, tbl_Evaluation.Modified_By, tbl_Evaluation.Completed, tbl_Evaluation.Completion_Date, "
sql2 = sql2 & "tbl_Evaluation.Completed_By, tbl_Evaluation.New_Score_4, tbl_Evaluation.New_Score_16, tbl_Evaluation.New_Score_27, tbl_Evaluation.New_Score_28A, tbl_Evaluation.New_Score_28B "
sql2 = sql2 & "FROM tbl_Evaluation "
sql2 = sql2 & "WHERE tbl_Evaluation.Client_Id = '" & Globals.glb_Client_id & "' and tbl_Evaluation.UID = " & UID

Everything gets inserted perfectly as it should except the following fields: New_Score_4, New_Score_16, New_Score_27, New_Score_28A, New_Score_28B. They are the last 5 fields in the query. They always end up blank after the insert into tbl_Evaluation_Archive. I tried to use a message box to display the query but the query has too many characters and it gets cut off.
Any help will be greatly appreciated.

 

[MarkK]

What is an F17 and how do you distinguish it from, say, an F33 or an F7A?

 

[plog]

Any help will be greatly appreciated.

Fix your tables. You are storing values in field names--that is incorrect. Also, but most likely relatedly, when you start numerating your field names, its time for a new table as well.

Instead of a field for every F#, you should have a record for it. Additionally, all those fields suffixed with PRE and POST should go as well. PRE and POST should be stored with the record as a value in a field, not in the field's name.

You really need to read up on normalization, because the issue you have now (and the other issues you will face) is a symptom of your poor table set up.

 

[Mark_]

For myself, I would put a very large text box on the screen. Prior to executing your SQL statement, set the text box equal to your SQL string. This way you can see EXACTLY what you are doing.

As others have noted, your naming convention and file layout should be addressed.

 

[boerbende]

What I would suggest is to use debug.print SQL2 in the immediate window.
Then copy this result to the query builder and debug the query until it works.
Don't make things more complex but simplify it first before debugging.
Try first to minimize your query to that part which does not work.

 

[TheSearcher]

Thank you all for your help!
If a database is created with poor normalization and unconventional field names are used in its tables the queries should still work, right? Assuming, of course, that the syntax is correct.
The issue with my query was that the 5 fields that were blank had not yet been populated in tbl_Evaluation. They became populated only after the query was run. Therefore it makes sense that they were blank!
It was just a logical error.

 

[MarkK]

If a database is created with poor normalization and unconventional field names are used in its tables the queries should still work, right? Assuming, of course, that the syntax is correct.

Yes, and if you build a roof first, then build a house on top of the roof, absolutely, all your doors will swing just fine, as long as you install them plumb and level. Happy move-in day.

 

[TheSearcher]

If you build a roof first and then build a house on top of the roof that would be stupid. But if you build a roof first and place it on top of the house everything should be fine. I inherited this database. It made more sense to work with what I had than to redesign the entire thing. The company that hired me would never have agreed to pay me to completely redesign it.

 

[Mark_]

Ah, so your installing the doors that go into the chimney?

 

[Mark_]

The company that hired me would never have agreed to pay me to completely redesign it.

Something you may wish to do, start a log of problems that you encounter and note what kind(s) of problems they generate.

For the work you are doing now you can annotate that the poor table design cost N amount of time to work around, revealed X other issues, and can cause Y to happen if not addressed either through a fix of the system or a rework of their training procedures.

Then put in "And I can get you in touch with some people who may be willing to touch this as a rebuild". You don't want to get sucked into "I'll rewrite it for you", but if you can demonstrate that there are issues that could be affecting their bottom line you can angle for getting the job of identifying what is going wrong now. That should lead to "And I can help you write the spec for what you will need to replace this with".

Be the consultant that they have to "convince" should fix it for them. But don't leave them thinking that having linoleum roofing is a good idea.