Programmatically create Inner Joins 1

[sucoyant]

Good day!

I'm trying to figure out a way to dynamically create INNER JOIN queries based on the existing queries in my access database.

SELECT EER_LA_Acct_paid_off.Employee, EER_LA_Acct_paid_off.Acct_paid_off, EER_LA_All_required_docs.All_required_docs, EER_LA_Title_signed_correctly.Title_signed_correctly, EER_LA_Correct_docs_prepared.Correct_docs_prepared, EER_LA_Title_sent_correct_party.Title_sent_correct_party, EER_LA_Tax_check_or_filing_fee.Tax_check_or_filing_fee, EER_LA_Charged_correct_GL.Charged_correct_GL, EER_LA_Required_notes_memo_scrn.Required_notes_memo_scrn
FROM ((((((EER_LA_Acct_paid_off INNER JOIN EER_LA_All_required_docs ON EER_LA_Acct_paid_off.Employee=EER_LA_All_required_docs.Employee) INNER JOIN EER_LA_Charged_correct_GL ON EER_LA_All_required_docs.Employee=EER_LA_Charged_correct_GL.Employee) INNER JOIN EER_LA_Correct_docs_prepared ON EER_LA_Charged_correct_GL.Employee=EER_LA_Correct_docs_prepared.Employee) INNER JOIN (EER_LA_Required_notes_memo_scrn INNER JOIN EER_LA_Tax_check_or_filing_fee ON EER_LA_Required_notes_memo_scrn.Employee=EER_LA_Tax_check_or_filing_fee.Employee) ON EER_LA_Correct_docs_prepared.Employee=EER_LA_Required_notes_memo_scrn.Employee) INNER JOIN EER_LA_Title_sent_correct_party ON EER_LA_Tax_check_or_filing_fee.Employee=EER_LA_Title_sent_correct_party.Employee) INNER JOIN EER_LA_Title_signed_correctly ON EER_LA_Title_sent_correct_party.Employee=EER_LA_Title_signed_correctly.Employee) INNER JOIN EER_LA_Charged_correct_GL AS EER_LA_Charged_correct_GL_1 ON EER_LA_Title_signed_correctly.Employee=EER_LA_Charged_correct_GL_1.Employee;

Another:
Also included, is a screenshot of the actual query design:

SELECT [EER_PD_File_match_letter - Errors].Employee, [EER_PD_File_match_letter - Errors].File_match_letter, [EER_PD_Paid_contain_letter - Errors].Paid_contain_letter, [EER_PD_Pending_cabinet - Errors].Pending_cabinet, [EER_PD_Real_estate_sorted - Errors].Real_estate_sorted, [EER_PD_Record_coversheet - Errors].Record_coversheet
FROM ((([EER_PD_File_match_letter - Errors] INNER JOIN [EER_PD_Paid_contain_letter - Errors] ON [EER_PD_File_match_letter - Errors].Employee = [EER_PD_Paid_contain_letter - Errors].Employee) INNER JOIN [EER_PD_Pending_cabinet - Errors] ON [EER_PD_Paid_contain_letter - Errors].Employee = [EER_PD_Pending_cabinet - Errors].Employee) INNER JOIN [EER_PD_Real_estate_sorted - Errors] ON [EER_PD_Pending_cabinet - Errors].Employee = [EER_PD_Real_estate_sorted - Errors].Employee) INNER JOIN [EER_PD_Record_coversheet - Errors] ON [EER_PD_Real_estate_sorted - Errors].Employee = [EER_PD_Record_coversheet - Errors].Employee;

And another:

SELECT [EER_REP_Account_paid_off - Errors].Employee, [EER_REP_Account_paid_off - Errors].Account_paid_off, [EER_REP_All_collateral_released - Errors].All_collateral_released, [EER_REP_ALSaccountMatchPaperwork - Errors].ALS_match_paperwork, [EER_REP_AMZM_comments_correct - Errors].AMZM_comments_correct, [EER_REP_Appropriate_research - Errors].Appropriate_research, [EER_REP_Bank_successions - Errors].Bank_successions, [EER_REP_Cnty_recording_info_correct - Errors].Cnty_recording_info_correct, [EER_REP_Cost_center_correct - Errors].Cost_center_correct, [EER_REP_Cust_info_correct - Errors].Cust_info_correct, [EER_REP_Legal_correct_legible - Errors].Legal_correct_legible, [EER_REP_Release_form_correct - Errors].Release_form_correct, [EER_REP_Release_signed_notarized - Errors].Release_signed_notarized, [EER_REP_Special_paid_instructions_followed - Errors].Special_paid_instructions_followed
FROM ((((((((((([EER_REP_Account_paid_off - Errors] INNER JOIN [EER_REP_All_collateral_released - Errors] ON [EER_REP_Account_paid_off - Errors].Employee = [EER_REP_All_collateral_released - Errors].Employee) INNER JOIN [EER_REP_ALSaccountMatchPaperwork - Errors] ON [EER_REP_All_collateral_released - Errors].Employee = [EER_REP_ALSaccountMatchPaperwork - Errors].Employee) INNER JOIN [EER_REP_AMZM_comments_correct - Errors] ON [EER_REP_ALSaccountMatchPaperwork - Errors].Employee = [EER_REP_AMZM_comments_correct - Errors].Employee) INNER JOIN [EER_REP_Appropriate_research - Errors] ON [EER_REP_AMZM_comments_correct - Errors].Employee = [EER_REP_Appropriate_research - Errors].Employee) INNER JOIN [EER_REP_Bank_successions - Errors] ON [EER_REP_Appropriate_research - Errors].Employee = [EER_REP_Bank_successions - Errors].Employee) INNER JOIN [EER_REP_Cnty_recording_info_correct - Errors] ON [EER_REP_Bank_successions - Errors].Employee = [EER_REP_Cnty_recording_info_correct - Errors].Employee) INNER JOIN [EER_REP_Cost_center_correct - Errors] ON [EER_REP_Cnty_recording_info_correct - Errors].Employee = [EER_REP_Cost_center_correct - Errors].Employee) INNER JOIN [EER_REP_Cust_info_correct - Errors] ON [EER_REP_Cost_center_correct - Errors].Employee = [EER_REP_Cust_info_correct - Errors].Employee) INNER JOIN [EER_REP_Legal_correct_legible - Errors] ON [EER_REP_Cust_info_correct - Errors].Employee = [EER_REP_Legal_correct_legible - Errors].Employee) INNER JOIN [EER_REP_Release_form_correct - Errors] ON [EER_REP_Legal_correct_legible - Errors].Employee = [EER_REP_Release_form_correct - Errors].Employee) INNER JOIN [EER_REP_Release_signed_notarized - Errors] ON [EER_REP_Release_form_correct - Errors].Employee = [EER_REP_Release_signed_notarized - Errors].Employee) INNER JOIN [EER_REP_Special_paid_instructions_followed - Errors] ON [EER_REP_Release_signed_notarized - Errors].Employee = [EER_REP_Special_paid_instructions_followed - Errors].Employee;

Does anyone have any suggestions?
I can see a pattern in the INNER JOIN's, but I'm having trouble putting this into a VBA program.

Any help would be GREATLY appreciated. I'm willing to compensate your time!

________________________________________

Buddha. Dharma. Sangha.

 

[TonyJollans]

Hi sucoyant,

I don't really understand what you are asking. Is it just how to write SQL or is there something more specific?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[sucoyant]

Sorry. I'm not the best at explaining...

I have a series of tables, and within each table a few items are the same. Please see below:

Paids Distribution
(This table pertains to the 2nd piece of code and the only image in my first post)

Real Estate Filing
(This table pertains to the 3rd piece of SQL code in my first post)

Please note the first 5 items, and the last 3 are the same for each table.

Now, what I would like to do, is try to figure out a way to create a short VBA program that will automatically create the Inner Join queries that you see above.

The reason for this, is that if a new table is created, I want to be able to just hit a button and have the program create the query automatically.

I'm guessing the first portion of code would be a series of "If/Else" statements to grab only the fields that matter... aka, NOT the first 5 and NOT the last 3.

Something like this:

For Each fld In tdf.Fields
        If Not LCase(fld.Name) Like "*employee*" Then
            If Not LCase(fld.Name) Like "*account*" Then
                If Not fld.Name Like "Date" Then
                    If Not LCase(fld.Name) Like "*reviewer*" Then
                        If Not LCase(fld.Name) Like "*comments*" Then
                            If Not LCase(fld.Name) Like "*goodbad*" Then
                                If Not LCase(fld.Name) Like "*cust_lname*" Then
                                    If Not LCase(fld.Name) Like "*cust_fname*" Then
       
                                        'Here is the first substantial item we need...

                                        
                                    End If
                                End If
                            End If
                        End If
                    End If
                End If
            End If
        End If
Next fld
'
'Code to create the SQL statement goes here...
'

Does this help?


________________________________________

Buddha. Dharma. Sangha.

 

[vbajock]

You could set up a table of table names that uses the tabledef collection to discover if any new table has been created since your table of table table names was last updated, and then use the fields collection of the newly arrived table to check it for the field name you are looking for. After that, you have to set up logic to create the SQL string that controls your join definitions. You could also use the ADOX object to accomplish the same thing.

 

[TonyJollans]

Hi sucoyant,

I'm not sure this is a realistic proposition - the permutations are almost endless. It is almost certainly quite a chunk of code. So let's be clear what you want.
[ul]
[li]You have several tables which have many fields in common (based solely on the field name)[/li]
[li]You want to create queries (in VBA code) which join these tables on the common fields[/li]
[/ul]

How will the code know which tables to join?

Do you have existing queries, which want new tables adding to them?

Are the common fields the same for all tables? using your example, might you have another table which only had the first two common fields? And another which had only the last two?


Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[sucoyant]

I'm sorry I'm not making this clear. As I said before, I'm no good at explaining. I forget a few things...

The first 5 and last 3 fields in each table are the same.
For each field excluding the first 5 and the last 3, there are existing queries already created based on the table.

If you take a look that the Query Design (first image posted), you will note that this is from the Paids Distribution group. Also note that the 5 queries within the Query Design window are the 5 substantial fields in the Paids Distribution thable. (This is probably a bad example, sorry. I realize that the number of substantial fields and the number of the fields that aren't needed are the same. Sorry. I hope this isn't confusing!)

The existing queries that the Inner Join queries will use have the format of "EER_STN_Field - Errors" where STN = Short Table Name... ie- Paids Distribution = PD, Real Estate Paids REP.
So, for the Paids Distribution table (PD) the queries that already exist are:

EER_PD_File_match_letter - Errors
EER_PD_Paid_contain_letter - Errors
EER_PD_Pending_cabinet - Errors
EER_PD_Real_estate_sorted - Errors
EER_PD_Record_coversheet - Errors

I already have a short script that automatically generates these queries for a new table. Now I just need to figure out a way to programmatically create the Inner Join queries that you see above, based on the existing queries.

After the table is created, this program will sort out the relevant fields from the table. This is what it looks like:

We can use the listbox (lstTBL) that shows the relevant fields for that table to do our dirty work. Now, we just have to figure out how to get the syntax correct of the Inner Join query.

Any ideas?

________________________________________

Buddha. Dharma. Sangha.

 

[TonyJollans]

Hi sucoyant,

I think I'm beginning to grasp your data structure - but I'm not sure I want to! Why do you need all those Queries? Can you not construct a single Query on the base Table?

Anyway, basic Inner Join syntax should be

( ... ((( Query1
INNER JOIN Query2 ON Query1.Key = Query2.Key )
INNER JOIN Query3 ON Query1.Key = Query3.Key )
INNER JOIN Query4 ON Query1.Key = Query4.Key )
:
:
INNER JOIN Queryn ON Query1.Key = Queryn.Key )

And here is some basic code to generate this ..

[blue][green]' I guess you can generate this from your listbox
' I just hard code it for clarity here[/green]

Dim strQueryName(1 To 5) As String

strQueryName(1) = "EER_PD_File_match_letter - Errors"
strQueryName(2) = "EER_PD_Paid_contain_letter - Errors"
strQueryName(3) = "EER_PD_Pending_cabinet - Errors"
strQueryName(4) = "EER_PD_Real_estate_sorted - Errors"
strQueryName(5) = "EER_PD_Record_coversheet - Errors"

Dim q As Integer
Dim strFromLeft As String
Dim strFromRight As String

strFromLeft = "[" & strQueryName(1) & "]"

For q = 2 To 5
    strFromLeft = "( " & strFromLeft
    strFromRight = strFromRight & "INNER JOIN " & strQueryName(q) & _
                   " ON [" & strQueryName(1) & "].Employee = " & _
                       "[" & strQueryName(q) & "].Employee )"
Next q

MsgBox "FROM " & strFromLeft & " " & strFromRight & ";"[/blue]

I think that will generate more records than you want, but is it anything like the right place to begin? Or have I missed something basic?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[sucoyant]

Wow! I will have to check this out tomorrow, as the work day is nearing an end.

Thank you very much! I will let you know the outcome tomorrow.

Here is a star for your time Tony.

________________________________________

Buddha. Dharma. Sangha.