Dynamically create Joins

[It_thug]

I hope I am explaining this well.
Before I try to spend a lot of time beating my head against a wall, I was wondering if there was any direction someone wanted to point me.

I have a couple of VBA modules that I have set up to create a Query that will join 5, 10, 0r 15 Variables. These are static in that for those forms, you have to choose 5, 10, or 15 variables to compare each other to. You can't choose 4, or 6, etc...

What I would like to do, is be able to have a form, and module where they can choose whatever number of variables they want to combine, and then have the query created on the fly, based on the number of variables chosen. This seems to me like it should be possible, but I'm not sure where to begin. I'm thinking at this point it wouldn't be difficult to use a For Each Loop to grab the Variables once they are selected in the form.

But I'm not sure how to get that Query generated dynamically. (along the lines that I'm using below).
This is just a snippet of the code

Dim VN1 As String
Dim VN2 As String
Dim VN3 As String
Dim VN4 As String
Dim VN5 As String
Dim CreateCommon As String
Dim Exists As String
Dim FilteredQry1 As String
Dim FilteredQry2 As String
Dim FilteredQry3 As String
Dim FilteredQry4 As String
Dim FilteredQry5 As String

VN1 = TempVars!v_name1
VN2 = TempVars!v_Name2
VN3 = TempVars!v_Name3
VN4 = TempVars!v_Name4
VN5 = TempVars!v_Name5


CreateCommon = "SELECT [" & VN1 & "].[School Number] INTO 5CommonSchools " & _
        "FROM ((([" & VN1 & "]  " & _
        "INNER JOIN [" & VN2 & "] ON [" & VN1 & "].[School Number] = [" & VN2 & "].[School Number])  " & _
        "INNER JOIN [" & VN3 & "] ON [" & VN2 & "].[School Number] = [" & VN3 & "].[School Number])  " & _
        "INNER JOIN [" & VN4 & "] ON [" & VN3 & "].[School Number] = [" & VN4 & "].[School Number])  " & _
        "INNER JOIN [" & VN5 & "] ON [" & VN4 & "].[School Number] = [" & VN5 & "].[School Number];"

FilteredQry1 = "SELECT [" & VN1 & "].* FROM 5CommonSchools LEFT JOIN [" & VN1 & "] ON [5CommonSchools].[School Number] = [" & VN1 & "].[School Number];"

FilteredQry2 = "SELECT [" & VN2 & "].* FROM 5CommonSchools LEFT JOIN [" & VN2 & "] ON [5CommonSchools].[School Number] = [" & VN2 & "].[School Number];"

FilteredQry3 = "SELECT [" & VN3 & "].* FROM 5CommonSchools LEFT JOIN [" & VN3 & "] ON [5CommonSchools].[School Number] = [" & VN3 & "].[School Number];"

FilteredQry4 = "SELECT [" & VN4 & "].* FROM 5CommonSchools LEFT JOIN [" & VN4 & "] ON [5CommonSchools].[School Number] = [" & VN4 & "].[School Number];"

FilteredQry5 = "SELECT [" & VN5 & "].* FROM 5CommonSchools LEFT JOIN [" & VN5 & "] ON [5CommonSchools].[School Number] = [" & VN5 & "].[School Number];"

On Error Resume Next
Exists = IsObject(CurrentDb.TableDefs("5CommonSchools"))

If Exists Then
DoCmd.DeleteObject acTable, "5CommonSchools"
End If

myRecordset.Open CreateCommon

CreateQuery "Filtered_5_" + VN1, FilteredQry1
CreateQuery "Filtered_5_" + VN2, FilteredQry2
CreateQuery "Filtered_5_" + VN3, FilteredQry3
CreateQuery "Filtered_5_" + VN4, FilteredQry4
CreateQuery "Filtered_5_" + VN5, FilteredQry5

There may be a much better way to do this. So I'll give an idea of what I am doing.

I need to select Data for a range of years, for multiple variables. And then, filter it down to only the Organizations that have the data for all the variables chosen, and then open each variable with the list of organizations that I have filtered down, and with the information for that variable (over the course of the years.

Since I'm still new to a lot of this, I may have gone the long way around to get this data. Like I said, I have it set up so that I can do 5,10, or 15 comparisons.. but now need to figure out how to do it for a dynamic number of variables to be chosen.

Anyone willing to point me in the right direction, or at least tell me it's possible, or not possible would be a hero.

Thanks.

 

[highandwild]

How will you be using all of these queries?

 

[pbaldy]

Can you give an example of what the data looks like? The question implies a design issue. What type of data is stored in different tables?

 

[It_thug]

Thanks for the questions. Let me see if I can explain. (not sure if I can do it adequately).

Let's say I have about 500 different variables. The variable Table has 4 fields in it. OrgID, Variable#, Value, Year.

At any given point, I need to be able to pull out all of the organizations that have value in 30 (for a nice round number) of these variables over the course of X number of years. (Filtering out any Organizations that have not answered at any time over the course of X years).

Since it is possible that Each Variable will have a different list of Organizations that have value for each variable, i then have to compare those lists together and come up with only the list of Organizations that have value in all 30 variables for all X years.

Then display the data for export for each individual variable. (this part is easy).


The way it's done right now, is that I have a list, they choose 5, 10, or 15.. which creates temvpvars, and I use those tempvars to plug into a premade query that will give me the list that has only the ORGIDs that I need.

What I need to do, is be able to filter for this for any number of OrgIDs chosen..... So, my thought was to be able to build a query like the one above dynamically based on the number of Variable IDs chosen.

This has been bouncing around my brain too much the last few days... so bear with me if it's a bit jumbled.

I am most def not asking anyone to come up with the actual work. I would prefer just being pointed in the right direction.

I have thought of using Recordsets and a For Each loop to get the list of Variables from the form..

But I'm starting to think there is a better way to get the List of organizations that appear for all X number of years for each Variable chosen.