VBA Data Import Routine (multipe XLS into Access) with duplicate fields/values into DISTINCT tables

[orange]

I recommend you replace the module/proc in its entirety. There a re changes in various parts, additional comments and new variables.

[skydivetom]

Copy... will do. Thank you, Jack.

[orange]

Tom,

I noticed I missed the "_FK" on the Target_PK_FK.
To get the proper name, add the _FK on this line in routine Sub CreateLKTables()

30 newInfo2 = " , Target_PK_FK Number, Comments Text(250))" 'part2

It will generate proper field names in each of your LK* tables.
That's the only change involved.

Sample:

[skydivetom]

Jack - thank you. Yes, I had noticed it yesterday and had already made that change. Thank you for the attention to detail and continued support.

[skydivetom]

Jack -- here we go again... I posted a (new) project-related question at:
https://www.accessforums.net/showthr...983#post473983

As mentioned in the new thread, I consider it a BIG KAHUUNA question... not something that's easily solvable. But then, again, may it is...

Since you have expertise in legacy to target system projects, maybe you have some ready-to go solution for it. Thank you for everything you've helped me w/ thus far.

[skydivetom]

Jack:

Earlier in March, you've helped me out tremendously by creating a routine that scans through n source tables and the creates LK (lookup) tables with DISTINCT values for all fields found in those sources tables. I hope you remember.

This process has been working great thus far. However, at this time, I am hoping you would be willing to provide me little bit more assistance with slightly tweaking the process. If you prefer, I will open up a new thread. Just let me know what works best for you.

Process Recap for Creating LKs:
=======================
- I have 5 source tables (containing prefix = "tbl_"). **For demo purposes only**, each table contains only 1 record. (Actual # of records = 70k).
- I open up form "F01_MainMenu" -- this form contains 2 listboxes.
- In the left listbox, I click on the 3 options (top to bottom).
- Upon having clicked 3rd option ("Create & Load..."), a message box indicates that *207* LK tables were created. This count can be validated by clicking on query "FieldNamesQ". So far so good!

Process Recap for Removing LKs that are Not Needed:
========================================
- Again, upon having clicked on "Create LKs...", I ended up with 207 LK tables.
- Reality is, however, that I only need a subset of those LKs (instead of one for every field).
- Thus, with the help of another expert (moke123) in March, I ended up extending the functionality which would delete any "non-LOV" (i.e., LKs that are not needed).
- So, via the right listbox, I would click on the 1st menu item "Delete LK Tables...".
- The delete tables routine is linked to table [delete_tables_nonLOV].
- Specifically, all LK tables where [LOV] = FALSE will be deleted from the database. Thus far, this process also worked fine!

Current Issue:
===========
- Up until yesterday, I actually had 2 of the source tables split up via multiple tables.
- For reasons beyond this post, I decided to merge all fields of the multiple source tables (from the same organization) into a single table. Thus, I have 5 source tables (vs. 11 tables).
- As part of this field merging, however, my DELETE LK tables does NO longer work as it did originally. And I can't figure out why.
- Specially, if you open query "Count_Of_LK_Tables_Generated", the count of LK tables = 96 (that's the count **AFTER** I ran the DELETE function).
- However, when you open query "Count_Of_LK_Tables_To_Keep", the record count = 92.
- So, for some reason 4 LK tables (LK_DAFW, LK_EQUIPMENT, LK_EVENT, LK_FIRST_LINE_SUPERVISOR_NAME) still remain in the DB even though their Boolean flag = FALSE in table [delete_tables_nonLOV].

Here's What I Need Some Help With:
===========================
1. Instead of creating **all** 207 LK tables and then deleting those where [delete_tables_nonLOV].[LOV] = FALSE (via routine DeleteTableNonLOV()), would it be possible to modify the LK creation routine as follows.
2. When executing routine CreateLKTables(), only create LKs where [delete_tables_nonLOV].[LOV] = TRUE?
3. If the latter works, it would streamline the process from currently a) creating all LKs but deleting the unwanted LKs to b) only create the LKs that are needed.

How would the VBA have to be modified to only generate the LKs that are marked TRUE in [delete_tables_nonLOV].[LOV]?

Again, if you prefer me to open up a new post, I will gladly do so.

Thank you for your help in advance.

Cheers,
Tom

[orange]

Hi Tom,

It appears after a short read that instead of using FieldNamesQ as the input to the CreateLKTables,
the input could be RevisedSourceForCreateLKs_Q.

The SQL of this query is

SELECT FieldNamesQ.field_name
, FieldNamesQ.data_type
FROM FieldNamesQ INNER JOIN delete_tables_nonLOV 
ON FieldNamesQ.field_name = delete_tables_nonLOV.FieldnameStandardized
WHERE (((delete_tables_nonLOV.LOV)=True));

I am not as familiar with your set up as I once was, so have made a copy of your database and adjusted the 1 line of
CreateLKTables as follows

.....
  NewSQL0 = " create table LK_XXX ( XXX "                             'Base create SQL
    NewSQL1 = "  CONSTRAINT XXX PRIMARY KEY );"                         'PK constraint
    Set db = CurrentDb
    'Set rsFields = db.OpenRecordset("FieldnamesQ", dbReadOnly)
    
    Set rsFields = db.OpenRecordset("RevisedSourceForCreateLKs_Q", dbReadOnly) '-------------------jed jun 24 2021 !!!!!
    Do While Not rsFields.EOF
      ....

NOTE: The following is part of the DEBUG.PRINT log from the immediate window. I'm not sure what this represents??

Update: FieldUsage is referencing FieldNamesQ - it should be adjusted to reference RevisedSourceForCreateLKs_Q to remove/reduce the messages to Immediate window.

INSERT into LK_OSHAREPORTABLE SELECT DISTINCT OSHAREPORTABLE from tbl_MMAC WHERE OSHAREPORTABLE Is Not Null;
INSERT into LK_OSHASUBMITTED SELECT DISTINCT OSHASUBMITTED from tbl_MMAC WHERE OSHASUBMITTED Is Not Null;
Error -Table OSHASUBMITTED does not exist??
INSERT into LK_OTHER_PPE SELECT DISTINCT OTHER_PPE from tbl_NNSY_INJURY WHERE OTHER_PPE Is Not Null;
Error -Table OTHER_PPE does not exist??
INSERT into LK_OTHER_PPE SELECT DISTINCT OTHER_PPE from tbl_PHNSY_INJURY WHERE OTHER_PPE Is Not Null;
Error -Table OTHER_PPE does not exist??
INSERT into LK_OTHER_PPE SELECT DISTINCT OTHER_PPE from tbl_PNSY_INJURY WHERE OTHER_PPE Is Not Null;
Error -Table OTHER_PPE does not exist??
INSERT into LK_OTHERWISERECORDABLE SELECT DISTINCT OTHERWISERECORDABLE from tbl_POAIRS WHERE OTHERWISERECORDABLE Is Not Null;
Error -Table OTHERWISERECORDABLE does not exist??
INSERT into LK_OVERTIME SELECT DISTINCT OVERTIME from tbl_NNSY_INJURY WHERE OVERTIME Is Not Null;
Error -Table OVERTIME does not exist??
INSERT into LK_PAY_STATUS SELECT DISTINCT PAY_STATUS from tbl_NNSY_INJURY WHERE PAY_STATUS Is Not Null;
INSERT into LK_PAY_STATUS SELECT DISTINCT PAY_STATUS from tbl_PHNSY_INJURY WHERE PAY_STATUS Is Not Null;
INSERT into LK_PAY_STATUS SELECT DISTINCT PAY_STATUS from tbl_PNSY_INJURY WHERE PAY_STATUS Is Not Null;
INSERT into LK_PAY_STATUS SELECT DISTINCT PAY_STATUS from tbl_POAIRS WHERE PAY_STATUS Is Not Null;
INSERT into LK_PAY_STATUS_MMAC SELECT DISTINCT PAY_STATUS_MMAC from tbl_MMAC WHERE PAY_STATUS_MMAC Is Not Null;
INSERT into LK_PAYGRADE SELECT DISTINCT PAYGRADE from tbl_POAIRS WHERE PAYGRADE Is Not Null;
INSERT into LK_PAYRATE SELECT DISTINCT PAYRATE from tbl_POAIRS WHERE PAYRATE Is Not Null;
Error -Table PAYRATE does not exist??
INSERT into LK_PCN SELECT DISTINCT PCN from tbl_MMAC WHERE PCN Is Not Null;
INSERT into LK_PCN SELECT DISTINCT PCN from tbl_NNSY_INJURY WHERE PCN Is Not Null;
INSERT into LK_PCN SELECT DISTINCT PCN from tbl_PHNSY_INJURY WHERE PCN Is Not Null;
INSERT into LK_PCN SELECT DISTINCT PCN from tbl_PNSY_INJURY WHERE PCN Is Not Null;
INSERT into LK_POSTMISHAP_PHASE SELECT DISTINCT POSTMISHAP_PHASE from tbl_PNSY_INJURY WHERE POSTMISHAP_PHASE Is Not Null;
Error -Table POSTMISHAP_PHASE does not exist??
INSERT into LK_PPE_REMARK SELECT DISTINCT PPE_REMARK from tbl_NNSY_INJURY WHERE PPE_REMARK Is Not Null;
Error -Table PPE_REMARK does not exist??
INSERT into LK_PPE_REMARK SELECT DISTINCT PPE_REMARK from tbl_PHNSY_INJURY WHERE PPE_REMARK Is Not Null;
Error -Table PPE_REMARK does not exist??
INSERT into LK_PPE_REMARK SELECT DISTINCT PPE_REMARK from tbl_PNSY_INJURY WHERE PPE_REMARK Is Not Null;
Error -Table PPE_REMARK does not exist??
INSERT into LK_PPE_YES_NO SELECT DISTINCT PPE_YES_NO from tbl_MMAC WHERE PPE_YES_NO Is Not Null;
INSERT into LK_PPE_YES_NO SELECT DISTINCT PPE_YES_NO from tbl_NNSY_INJURY WHERE PPE_YES_NO Is Not Null;
INSERT into LK_PPE_YES_NO SELECT DISTINCT PPE_YES_NO from tbl_PHNSY_INJURY WHERE PPE_YES_NO Is Not Null;
INSERT into LK_PPE_YES_NO SELECT DISTINCT PPE_YES_NO from tbl_PNSY_INJURY WHERE PPE_YES_NO Is Not Null;
INSERT into LK_PRE_MISHAP SELECT DISTINCT PRE_MISHAP from tbl_PNSY_INJURY WHERE PRE_MISHAP Is Not Null;
Error -Table PRE_MISHAP does not exist??
INSERT into LK_PRESCRIPTION_MMAC SELECT DISTINCT PRESCRIPTION_MMAC from tbl_MMAC WHERE PRESCRIPTION_MMAC Is Not Null;
Error -Table PRESCRIPTION_MMAC does not exist??
INSERT into LK_PRIVATECASE SELECT DISTINCT PRIVATECASE from tbl_MMAC WHERE PRIVATECASE Is Not Null;
Error -Table PRIVATECASE does not exist??
INSERT into LK_PROCESS_CODE_NAME SELECT DISTINCT PROCESS_CODE_NAME from tbl_NNSY_INJURY WHERE PROCESS_CODE_NAME Is Not Null;
Error -Table PROCESS_CODE_NAME does not exist??
INSERT into LK_PROCESS_CODE_NAME SELECT DISTINCT PROCESS_CODE_NAME from tbl_PHNSY_INJURY WHERE PROCESS_CODE_NAME Is Not Null;
Error -Table PROCESS_CODE_NAME does not exist??
INSERT into LK_PROCESS_CODE_NAME SELECT DISTINCT PROCESS_CODE_NAME from tbl_PNSY_INJURY WHERE PROCESS_CODE_NAME Is Not Null;
Error -Table PROCESS_CODE_NAME does not exist??
INSERT into LK_PROCESS_CODE_SUBCLASS SELECT DISTINCT PROCESS_CODE_SUBCLASS from tbl_NNSY_INJURY WHERE PROCESS_CODE_SUBCLASS Is Not Null;
Error -Table PROCESS_CODE_SUBCLASS does not exist??
INSERT into LK_PROCESS_CODE_SUBCLASS SELECT DISTINCT PROCESS_CODE_SUBCLASS from tbl_PHNSY_INJURY WHERE PROCESS_CODE_SUBCLASS Is Not Null;
Error -Table PROCESS_CODE_SUBCLASS does not exist??
INSERT into LK_PROCESS_CODE_SUBCLASS SELECT DISTINCT PROCESS_CODE_SUBCLASS from tbl_PNSY_INJURY WHERE PROCESS_CODE_SUBCLASS Is Not Null;
Error -Table PROCESS_CODE_SUBCLASS does not exist??
INSERT into LK_PRODUCTLINE SELECT DISTINCT PRODUCTLINE from tbl_MMAC WHERE PRODUCTLINE Is Not Null;
Error -Table PRODUCTLINE does not exist??
INSERT into LK_PROJECT_ID SELECT DISTINCT PROJECT_ID from tbl_MMAC WHERE PROJECT_ID Is Not Null;
INSERT into LK_PROJECT_ID SELECT DISTINCT PROJECT_ID from tbl_NNSY_INJURY WHERE PROJECT_ID Is Not Null;
INSERT into LK_PROJECT_ID SELECT DISTINCT PROJECT_ID from tbl_PHNSY_INJURY WHERE PROJECT_ID Is Not Null;
INSERT into LK_PROJECT_ID SELECT DISTINCT PROJECT_ID from tbl_PNSY_INJURY WHERE PROJECT_ID Is Not Null;
INSERT into LK_RECORDKEEPER_NOTES SELECT DISTINCT RECORDKEEPER_NOTES from tbl_POAIRS WHERE RECORDKEEPER_NOTES Is Not Null;
Error -Table RECORDKEEPER_NOTES does not exist??
INSERT into LK_RECORDKEEPER_TITLE SELECT DISTINCT RECORDKEEPER_TITLE from tbl_POAIRS WHERE RECORDKEEPER_TITLE Is Not Null;
Error -Table RECORDKEEPER_TITLE does not exist??
INSERT into LK_REFERENCE_NUMBER SELECT DISTINCT REFERENCE_NUMBER from tbl_NNSY_INJURY WHERE REFERENCE_NUMBER Is Not Null;
Error -Table REFERENCE_NUMBER does not exist??
INSERT into LK_REFERENCE_NUMBER SELECT DISTINCT REFERENCE_NUMBER from tbl_PHNSY_INJURY WHERE REFERENCE_NUMBER Is Not Null;
Error -Table REFERENCE_NUMBER does not exist??
INSERT into LK_REFERENCE_NUMBER SELECT DISTINCT REFERENCE_NUMBER from tbl_PNSY_INJURY WHERE REFERENCE_NUMBER Is Not Null;
Error -Table REFERENCE_NUMBER does not exist??
INSERT into LK_RESP_WORN SELECT DISTINCT RESP_WORN from tbl_NNSY_INJURY WHERE RESP_WORN Is Not Null;
Error -Table RESP_WORN does not exist??
INSERT into LK_RESP_WORN SELECT DISTINCT RESP_WORN from tbl_PHNSY_INJURY WHERE RESP_WORN Is Not Null;
Error -Table RESP_WORN does not exist??
INSERT into LK_RESP_WORN SELECT DISTINCT RESP_WORN from tbl_PNSY_INJURY WHERE RESP_WORN Is Not Null;
Error -Table RESP_WORN does not exist??
INSERT into LK_RESTRICTED_DAYS SELECT DISTINCT RESTRICTED_DAYS from tbl_MMAC WHERE RESTRICTED_DAYS Is Not Null;
INSERT into LK_RESTRICTED_DAYS SELECT DISTINCT RESTRICTED_DAYS from tbl_PHNSY_INJURY WHERE RESTRICTED_DAYS Is Not Null;
INSERT into LK_REVIEW SELECT DISTINCT REVIEW from tbl_NNSY_INJURY WHERE REVIEW Is Not Null;
Error -Table REVIEW does not exist??
INSERT into LK_REVIEW SELECT DISTINCT REVIEW from tbl_PHNSY_INJURY WHERE REVIEW Is Not Null;
Error -Table REVIEW does not exist??
INSERT into LK_REVIEW SELECT DISTINCT REVIEW from tbl_PNSY_INJURY WHERE REVIEW Is Not Null;
Error -Table REVIEW does not exist??
INSERT into LK_ROOT_CAUSE SELECT DISTINCT ROOT_CAUSE from tbl_NNSY_INJURY WHERE ROOT_CAUSE Is Not Null;
Error -Table ROOT_CAUSE does not exist??
INSERT into LK_RWDTOTALACTUAL_1 SELECT DISTINCT RWDTOTALACTUAL_1 from tbl_POAIRS WHERE RWDTOTALACTUAL_1 Is Not Null;
INSERT into LK_SECOND_LINE_SUPERVISOR_BADGE_NUM SELECT DISTINCT SECOND_LINE_SUPERVISOR_BADGE_NUM from tbl_NNSY_INJURY WHERE SECOND_LINE_SUPERVISOR_BADGE_NUM Is Not Null;
Error -Table SECOND_LINE_SUPERVISOR_BADGE_NUM does not exist??
INSERT into LK_SECOND_LINE_SUPERVISOR_BADGE_NUM SELECT DISTINCT SECOND_LINE_SUPERVISOR_BADGE_NUM from tbl_PHNSY_INJURY WHERE SECOND_LINE_SUPERVISOR_BADGE_NUM Is Not Null;
Error -Table SECOND_LINE_SUPERVISOR_BADGE_NUM does not exist??
INSERT into LK_SECOND_LINE_SUPERVISOR_BADGE_NUM SELECT DISTINCT SECOND_LINE_SUPERVISOR_BADGE_NUM from tbl_PNSY_INJURY WHERE SECOND_LINE_SUPERVISOR_BADGE_NUM Is Not Null;
Error -Table SECOND_LINE_SUPERVISOR_BADGE_NUM does not exist??
INSERT into LK_SECOND_LINE_SUPERVISOR_FIRST_NAME SELECT DISTINCT SECOND_LINE_SUPERVISOR_FIRST_NAME from tbl_NNSY_INJURY WHERE SECOND_LINE_SUPERVISOR_FIRST_NAME Is Not Null;
Error -Table SECOND_LINE_SUPERVISOR_FIRST_NAME does not exist??
INSERT into LK_SECOND_LINE_SUPERVISOR_FIRST_NAME SELECT DISTINCT SECOND_LINE_SUPERVISOR_FIRST_NAME from tbl_PHNSY_INJURY WHERE SECOND_LINE_SUPERVISOR_FIRST_NAME Is Not Null;
Error -Table SECOND_LINE_SUPERVISOR_FIRST_NAME does not exist??
INSERT into LK_SECOND_LINE_SUPERVISOR_FIRST_NAME SELECT DISTINCT SECOND_LINE_SUPERVISOR_FIRST_NAME from tbl_PNSY_INJURY WHERE SECOND_LINE_SUPERVISOR_FIRST_NAME Is Not Null;
Error -Table SECOND_LINE_SUPERVISOR_FIRST_NAME does not exist??
INSERT into LK_SECOND_LINE_SUPERVISOR_LAST_NAME SELECT DISTINCT SECOND_LINE_SUPERVISOR_LAST_NAME from tbl_NNSY_INJURY WHERE SECOND_LINE_SUPERVISOR_LAST_NAME Is Not Null;
Error -Table SECOND_LINE_SUPERVISOR_LAST_NAME does not exist??
INSERT into LK_SECOND_LINE_SUPERVISOR_LAST_NAME SELECT DISTINCT SECOND_LINE_SUPERVISOR_LAST_NAME from tbl_PHNSY_INJURY WHERE SECOND_LINE_SUPERVISOR_LAST_NAME Is Not Null;
Error -Table SECOND_LINE_SUPERVISOR_LAST_NAME does not exist??
INSERT into LK_SECOND_LINE_SUPERVISOR_LAST_NAME SELECT DISTINCT SECOND_LINE_SUPERVISOR_LAST_NAME from tbl_PNSY_INJURY WHERE SECOND_LINE_SUPERVISOR_LAST_NAME Is Not Null;
Error -Table SECOND_LINE_SUPERVISOR_LAST_NAME does not exist??
INSERT into LK_SENT_DATE_JULIAN SELECT DISTINCT SENT_DATE_JULIAN from tbl_NNSY_INJURY WHERE SENT_DATE_JULIAN Is Not Null;
Error -Table SENT_DATE_JULIAN does not exist??
INSERT into LK_SENT_DATE_JULIAN SELECT DISTINCT SENT_DATE_JULIAN from tbl_PHNSY_INJURY WHERE SENT_DATE_JULIAN Is Not Null;
Error -Table SENT_DATE_JULIAN does not exist??
INSERT into LK_SENT_DATE_JULIAN SELECT DISTINCT SENT_DATE_JULIAN from tbl_PNSY_INJURY WHERE SENT_DATE_JULIAN Is Not Null;
Error -Table SENT_DATE_JULIAN does not exist??
INSERT into LK_SEVERITY_LEVEL SELECT DISTINCT SEVERITY_LEVEL from tbl_MMAC WHERE SEVERITY_LEVEL Is Not Null;
Error -Table SEVERITY_LEVEL does not exist??
INSERT into LK_SHIFT SELECT DISTINCT SHIFT from tbl_MMAC WHERE SHIFT Is Not Null;
INSERT into LK_SHIFT SELECT DISTINCT SHIFT from tbl_NNSY_INJURY WHERE SHIFT Is Not Null;
INSERT into LK_SHIFT SELECT DISTINCT SHIFT from tbl_PHNSY_INJURY WHERE SHIFT Is Not Null;
INSERT into LK_SHIFT SELECT DISTINCT SHIFT from tbl_PNSY_INJURY WHERE SHIFT Is Not Null;
INSERT into LK_SHIFT SELECT DISTINCT SHIFT from tbl_POAIRS WHERE SHIFT Is Not Null;
INSERT into LK_SHOP SELECT DISTINCT SHOP from tbl_MMAC WHERE SHOP Is Not Null;
INSERT into LK_SHOP SELECT DISTINCT SHOP from tbl_NNSY_INJURY WHERE SHOP Is Not Null;
INSERT into LK_SHOP SELECT DISTINCT SHOP from tbl_PHNSY_INJURY WHERE SHOP Is Not Null;
INSERT into LK_SHOP SELECT DISTINCT SHOP from tbl_PNSY_INJURY WHERE SHOP Is Not Null;
INSERT into LK_SHOP SELECT DISTINCT SHOP from tbl_POAIRS WHERE SHOP Is Not Null;
INSERT into LK_SHORT_TERM_CORRECTIVE_ACTIONS SELECT DISTINCT SHORT_TERM_CORRECTIVE_ACTIONS from tbl_NNSY_INJURY WHERE SHORT_TERM_CORRECTIVE_ACTIONS Is Not Null;
INSERT into LK_SHORT_TERM_CORRECTIVE_ACTIONS SELECT DISTINCT SHORT_TERM_CORRECTIVE_ACTIONS from tbl_POAIRS WHERE SHORT_TERM_CORRECTIVE_ACTIONS Is Not Null;
INSERT into LK_SIR SELECT DISTINCT SIR from tbl_NNSY_INJURY WHERE SIR Is Not Null;
Error -Table SIR does not exist??
INSERT into LK_SIR SELECT DISTINCT SIR from tbl_PHNSY_INJURY WHERE SIR Is Not Null;
Error -Table SIR does not exist??
INSERT into LK_SIR SELECT DISTINCT SIR from tbl_PNSY_INJURY WHERE SIR Is Not Null;
Error -Table SIR does not exist??
INSERT into LK_SIR_BECAME_REPORTABLE SELECT DISTINCT SIR_BECAME_REPORTABLE from tbl_NNSY_INJURY WHERE SIR_BECAME_REPORTABLE Is Not Null;
Error -Table SIR_BECAME_REPORTABLE does not exist??
INSERT into LK_SIR_BECAME_REPORTABLE SELECT DISTINCT SIR_BECAME_REPORTABLE from tbl_PHNSY_INJURY WHERE SIR_BECAME_REPORTABLE Is Not Null;
Error -Table SIR_BECAME_REPORTABLE does not exist??
INSERT into LK_SIR_BECAME_REPORTABLE SELECT DISTINCT SIR_BECAME_REPORTABLE from tbl_PNSY_INJURY WHERE SIR_BECAME_REPORTABLE Is Not Null;
Error -Table SIR_BECAME_REPORTABLE does not exist??
INSERT into LK_SIR_SUBMITTED SELECT DISTINCT SIR_SUBMITTED from tbl_NNSY_INJURY WHERE SIR_SUBMITTED Is Not Null;
INSERT into LK_SIR_SUBMITTED SELECT DISTINCT SIR_SUBMITTED from tbl_PHNSY_INJURY WHERE SIR_SUBMITTED Is Not Null;
INSERT into LK_SIR_SUBMITTED SELECT DISTINCT SIR_SUBMITTED from tbl_PNSY_INJURY WHERE SIR_SUBMITTED Is Not Null;
INSERT into LK_SOURCEDOCUMENTID_MMAC SELECT DISTINCT SOURCEDOCUMENTID_MMAC from tbl_MMAC WHERE SOURCEDOCUMENTID_MMAC Is Not Null;
INSERT into LK_SSN SELECT DISTINCT SSN from tbl_MMAC WHERE SSN Is Not Null;
INSERT into LK_SSN SELECT DISTINCT SSN from tbl_PHNSY_INJURY WHERE SSN Is Not Null;
INSERT into LK_SSN SELECT DISTINCT SSN from tbl_PNSY_INJURY WHERE SSN Is Not Null;
INSERT into LK_SUB_LOCATION SELECT DISTINCT SUB_LOCATION from tbl_NNSY_INJURY WHERE SUB_LOCATION Is Not Null;
INSERT into LK_SUB_LOCATION SELECT DISTINCT SUB_LOCATION from tbl_PHNSY_INJURY WHERE SUB_LOCATION Is Not Null;
INSERT into LK_SUB_LOCATION SELECT DISTINCT SUB_LOCATION from tbl_PNSY_INJURY WHERE SUB_LOCATION Is Not Null;
INSERT into LK_SUPERVISOR1_DISCRIP SELECT DISTINCT SUPERVISOR1_DISCRIP from tbl_NNSY_INJURY WHERE SUPERVISOR1_DISCRIP Is Not Null;
INSERT into LK_SUPERVISOR1_DISCRIP SELECT DISTINCT SUPERVISOR1_DISCRIP from tbl_PHNSY_INJURY WHERE SUPERVISOR1_DISCRIP Is Not Null;
INSERT into LK_SUPERVISOR1_DISCRIP SELECT DISTINCT SUPERVISOR1_DISCRIP from tbl_PNSY_INJURY WHERE SUPERVISOR1_DISCRIP Is Not Null;
INSERT into LK_SUPERVISOR2_SHORT_FIX SELECT DISTINCT SUPERVISOR2_SHORT_FIX from tbl_NNSY_INJURY WHERE SUPERVISOR2_SHORT_FIX Is Not Null;
Error -Table SUPERVISOR2_SHORT_FIX does not exist??
INSERT into LK_SUPERVISOR2_SHORT_FIX SELECT DISTINCT SUPERVISOR2_SHORT_FIX from tbl_PHNSY_INJURY WHERE SUPERVISOR2_SHORT_FIX Is Not Null;
Error -Table SUPERVISOR2_SHORT_FIX does not exist??
INSERT into LK_SUPERVISOR2_SHORT_FIX SELECT DISTINCT SUPERVISOR2_SHORT_FIX from tbl_PNSY_INJURY WHERE SUPERVISOR2_SHORT_FIX Is Not Null;
Error -Table SUPERVISOR2_SHORT_FIX does not exist??
INSERT into LK_SUPERVISOR3_CORRECT SELECT DISTINCT SUPERVISOR3_CORRECT from tbl_NNSY_INJURY WHERE SUPERVISOR3_CORRECT Is Not Null;
Error -Table SUPERVISOR3_CORRECT does not exist??
INSERT into LK_SUPERVISOR3_CORRECT SELECT DISTINCT SUPERVISOR3_CORRECT from tbl_PHNSY_INJURY WHERE SUPERVISOR3_CORRECT Is Not Null;
Error -Table SUPERVISOR3_CORRECT does not exist??
INSERT into LK_SUPERVISOR3_CORRECT SELECT DISTINCT SUPERVISOR3_CORRECT from tbl_PNSY_INJURY WHERE SUPERVISOR3_CORRECT Is Not Null;
Error -Table SUPERVISOR3_CORRECT does not exist??
INSERT into LK_TDY SELECT DISTINCT TDY from tbl_POAIRS WHERE TDY Is Not Null;
Error -Table TDY does not exist??
INSERT into LK_TIME_OF_INJURY SELECT DISTINCT TIME_OF_INJURY from tbl_NNSY_INJURY WHERE TIME_OF_INJURY Is Not Null;
INSERT into LK_TIME_OF_INJURY SELECT DISTINCT TIME_OF_INJURY from tbl_PHNSY_INJURY WHERE TIME_OF_INJURY Is Not Null;
INSERT into LK_TIME_OF_INJURY SELECT DISTINCT TIME_OF_INJURY from tbl_PNSY_INJURY WHERE TIME_OF_INJURY Is Not Null;
INSERT into LK_TOTAL_DAYS_AWAYOLD SELECT DISTINCT TOTAL_DAYS_AWAYOLD from tbl_MMAC WHERE TOTAL_DAYS_AWAYOLD Is Not Null;
Error -Table TOTAL_DAYS_AWAYOLD does not exist??
INSERT into LK_TOTAL_HOURS_WORKED SELECT DISTINCT TOTAL_HOURS_WORKED from tbl_PHNSY_INJURY WHERE TOTAL_HOURS_WORKED Is Not Null;
Error -Table TOTAL_HOURS_WORKED does not exist??
INSERT into LK_TOTAL_HOURS_WORKED SELECT DISTINCT TOTAL_HOURS_WORKED from tbl_PNSY_INJURY WHERE TOTAL_HOURS_WORKED Is Not Null;
Error -Table TOTAL_HOURS_WORKED does not exist??
INSERT into LK_TRAINEE SELECT DISTINCT TRAINEE from tbl_MMAC WHERE TRAINEE Is Not Null;
Error -Table TRAINEE does not exist??
INSERT into LK_TRAINEE SELECT DISTINCT TRAINEE from tbl_NNSY_INJURY WHERE TRAINEE Is Not Null;
Error -Table TRAINEE does not exist??
INSERT into LK_TRANSPORTED_HOW SELECT DISTINCT TRANSPORTED_HOW from tbl_POAIRS WHERE TRANSPORTED_HOW Is Not Null;
Error -Table TRANSPORTED_HOW does not exist??
INSERT into LK_TREATMENT SELECT DISTINCT TREATMENT from tbl_POAIRS WHERE TREATMENT Is Not Null;
INSERT into LK_TWD SELECT DISTINCT TWD from tbl_NNSY_INJURY WHERE TWD Is Not Null;
Error -Table TWD does not exist??
INSERT into LK_WC_STATUS SELECT DISTINCT WC_STATUS from tbl_POAIRS WHERE WC_STATUS Is Not Null;
Error -Table WC_STATUS does not exist??
INSERT into LK_WITNESS SELECT DISTINCT WITNESS from tbl_POAIRS WHERE WITNESS Is Not Null;
INSERT into LK_WORK_PHASE SELECT DISTINCT WORK_PHASE from tbl_MMAC WHERE WORK_PHASE Is Not Null;
Error -Table WORK_PHASE does not exist??
INSERT into LK_WORK_PHASE SELECT DISTINCT WORK_PHASE from tbl_NNSY_INJURY WHERE WORK_PHASE Is Not Null;
Error -Table WORK_PHASE does not exist??
INSERT into LK_WORK_PHASE SELECT DISTINCT WORK_PHASE from tbl_PHNSY_INJURY WHERE WORK_PHASE Is Not Null;
Error -Table WORK_PHASE does not exist??
INSERT into LK_WORK_PHASE SELECT DISTINCT WORK_PHASE from tbl_PNSY_INJURY WHERE WORK_PHASE Is Not Null;
Error -Table WORK_PHASE does not exist??
INSERT into LK_WORK_RELATED_EXPLANATION SELECT DISTINCT WORK_RELATED_EXPLANATION from tbl_POAIRS WHERE WORK_RELATED_EXPLANATION Is Not Null;
INSERT into LK_WORK_STARTTIME SELECT DISTINCT WORK_STARTTIME from tbl_MMAC WHERE WORK_STARTTIME Is Not Null;
INSERT into LK_WORK_STARTTIME SELECT DISTINCT WORK_STARTTIME from tbl_POAIRS WHERE WORK_STARTTIME Is Not Null;
INSERT into LK_WORK_TYPE_MMAC SELECT DISTINCT WORK_TYPE_MMAC from tbl_MMAC WHERE WORK_TYPE_MMAC Is Not Null;
Error -Table WORK_TYPE_MMAC does not exist??

I hope it's helpful. As I said I'm not as familiar with the details nor your expectations as I was back in March. Your process(es) have expanded so this is a sample that may help.

Summary: I created the revised query and modified the CreateLKTables by commenting out the original and using the revised query. 1 line commented,1 new line added only.

I have attached my copy(zip) of your latest database with the adjustments I have made.

Good luck.

[skydivetom]

Jack -- thank you for the follow-up... I very much appreciate it.

The 1st option (linking delete_tables_nonLOV to the query) definitely works best. It deletes, e.g., 92 tables and then adds 92 tables. I checked out the attachment but that one still generated a different number.

Anyhow, the modified SQL (as you recommended) did the trick! Thank you very much, again.

Cheers,
Tom

[orange]

You are very welcome.