Combining Multiple SQL Results - Is this Possible

[mbc]

hi,

I have a problem with trying to find a way to allow many different sql statements to combine into a final requery.. I have played with this for about 4 days and cannot find a way to achieve the outcome. I am not sure if it is even possible.

Here is the scenario:

At present I have one form that contains 4 combo boxes. 2 of the combo boxes are dynamic in nature (the list provided does change), 2 of the combo boxes are static in nature but each selection made by the client actually uses a different query. But all queries are coming from the same database.

So as of now if the client selects to see all the transactions where they earned income by cash they select the appropriate combo box, select the method (cash) and the transactions appear on the screen... Nothing special so far, this is a standard type of use for a combo box....

I have another two combo boxes that have up to 15 static selections that all return different transactions depending on what is selected.. All of the four combo boxes work by themselves on there own without any input from each other.

But now I want to also be able to refine the searches even further where each combo box can be combined with each other.

For example; Lets say they select all income by cash from one of the combo boxes, but they can also make another selection from another combo box selecting NewYork. This means that they now how a query of all Cash Transactions made in NewYork.

This may seem simplistic enough but I have a total of 4 combo boxes combining some 15 to 20 different scenarios and combination's which can easily be requested at the click of a mouse. Creating an IF Statement for two combo boxes is fine as you can arrange that easy enough, but to get to 4 combo boxes and all the different selections could see a huge tangles mess of if statements, which is not practical and almost impossible to attempt to figure out.

This is a typical query when selected from one of my combo boxes (although I have shortened the where statement here to make it easier for you to see)

strSQL = "SELECT * FROM INCOMENEW " & _
"WHERE INCOMENEW.date >=[Forms]![income form]![btdate1] And INCOMENEW.[income method]= [Forms]![income form]![combo103];"
Forms![income form]![IncomeSubForm].Form.RecordSource = strSQL
Forms![income form]![IncomeSubForm].Form.Requery


There are up to 20 different queries over the 4 combo boxes..

What I am hoping for is a way to combine the selection into one end product.. Lets say I used 4 combo boxes each returning statements such as:


oneSQL = "SELECT * FROM INCOMENEW " & _
"WHERE INCOMENEW.date >=Forms![mainmenu]![incomedate] and INCOMENEW.[Category]= [Forms]![incomeform]![combo163];"


twoSQL = "SELECT * FROM INCOMENEW " & _
"WHERE INCOMENEW.date >=[Forms]![incomeform]![btdate1] And INCOMENEW.incomemethod= [Forms]![incomeform]![combo103];"

threeSQL = "SELECT * FROM INCOMENEW " & _
"WHERE INCOMENEW.[invoices]= [Forms]![incomeform]![combo123];"


fourSQL = "SELECT * FROM INCOMENEW " & _
"WHERE INCOMENEW.[month]= [Forms]![incomeform]![combo103] and INCOMENEW.employee= [Forms]![incomeform]![number] ;"


is there a way to combine all four query results... It is easy enough for me to pass and of the selections from any of the 4 combo boxes as I am already doing that with the selection using the existing IF Statements as they are already being used with the indivdual queries.. so I already know what each selection is. Can I combine them somehow:

example:

alljoinedSQL = oneSQL and twoSQL and threeSQL and fourSQL

combinedSQL = "SELECT * FROM INCOMENEW " & _
"WHERE alljoinedSQL;"
Forms![income form]![IncomeSubForm].Form.RecordSource = combinedSQL
Forms![income form]![IncomeSubForm].Form.Requery


I hope I have explained the above enough.. as i said I have spend days on this, reading and testing and I could do a massive arrangement of IF Statements but there has to be an easier way.. what do you think, is this possible


thanks

 

[Galaxiom]

Although you have refered to your combo based queries as "dynamic" I don't think they are what is usually meant by that term. They are still static queries that use what is essentially a selectable parameter.

You really do need true dynamic queries for something as complex as you have described. These are queries whose SQL string is assembled in VBA by piecing together the subclauses of the WHERE section of query.

The VBA loops through the controls and adds the pieces in response to the user selections. Tose selections can choose the fieldname, the values and even the ORs and ANDs.

The code checks to see if there is a value in the box and concatenates that to the string. Like this basically

strSQL = "SELECT whatever"
 
IF Not IsNull(myvalue) Then
strSQL = strSQL & " WHERE myfield =" & Me.myvalue
End If
 
If Not IsNull(anothervalue) Then
strSQL = strSQL & " AND anotherfield =" & Me.anothervalue
 
End If
 
DoCmd.RunSQL strSQL

It can be done for the selected fields too. The only limit is your creativity in working out how to string it together. There are many varieties and refinements that allow the extra operators to be trimmed and efficient ways to run the loop.

This should get you the idea of what you are looking for if you search the forum for dyanmic query.

 

[DCrake]

SELECT * FROM INCOMENEW WHERE ([Date]>=[mainmenu]![incomedate]![btdate1] AND [Category]=[Forms]![incomeform]![combo163] ") 

OR ([Date]>=[Forms]![incomeform]![btdate1] And [incomemethod]= [Forms]![incomeform]![combo103]) 

OR ([invoices]= [Forms]![incomeform]![combo123]) 

OR ([month]= [Forms]![incomeform]![combo103] AND employee= [Forms]![incomeform]![number]);"

Points:
Invalid field names Date, Month & Number - Access Reserved words
Both Income form and main menu for must be both open
Any string values need single quotes around them

 

[mbc]

Thanks DCrake and Galaxiom,

DCrake the forms are open and yes I did sort of shove stuff together for this post so some of the fields names are not actually called that...

I forget how many combination's of stuff I have tried and my brain is now fried that I forget where I have place the single quotes, where I haven't and what to do next, but I will have another look. I sort of shoved this together in enough hope someone might be able to send me in the right direction.

Galaxiom I am looking at your code and I see something which I have not tried.. Now that I can see that I can add a series of SQL queries together I just have to get the right combination.

I will spend some more time on it and see if I can get the result I am after

thanks DCrake and Galaxiom for your help

 

[mbc]

GalaxiomAtHome

I looked at what you were doing and tried to apply that, after going around in circles i realized where you are saying and I dont think I have described what I am trying to do..

maybe I have no idea what I am doing and this is the problem.


If I already have the contents of 4 variables with and SQL Statement can i add them together.


strSQL = strSQL & oneSQL & twoSQL & threeSQL & fourSQL

doing the above only returns an error telling me that "characters found after end of SQL Statement"


I have attempted to write the contents of a variable to a text box and then instruct to add that to the variable, but I get an error telling me that the text box does not exist, or a string issue...

Forget about the rest of the post, because if I can add contents of existing SQL Variables together then everything is resolved, as each of the above variables are results of up to 20 If statements to get the result. The result is not simply the choice made from the combobox, the contents of the combo box is only step one which is why adding that result is not important, where as adding the end product, the variables themselves is more important.

I believe its all me, I cant be the first to ever want to do this, so I know I am not doing it right.

 

[Galaxiom]

Characters found after the end suggests you are creating invalid SQL in you concatenation.

Set break points in the construction of the SQL string and see what the string is actually holding when the code stops.

 

[DCrake]

It sounds like you have not removed the semi colon from the end of each sql statement.

 

[mbc]

Hi,


I would like to thank DCrake and Galaxiom for your help, your tips allowed me to find a way around this problem.

I eventually kept writing the contents of the variables to a text box to see what was happening at every step along the way, this lead me to see why they were falling over as I tried to use them in an sql query.

In case someone else reads this post and is looking to do a similar thing here is a small example of what I did, there may be a better way to achieve this but this works for me, so here it is:

I used combo boxes to derive at an end result which was driven by the user. The contents of the selection made by the user was only one step in the chain, as their selection caused another 10 to 15 steps to occur which was in relation to their selection, all of this had a bearing on the end result, so it wasn’t just a matter of using the contents of the combo box, and all cases the selected item was never taken into much consideration in the sql statement.


Naturally I created the variables:
Dim startSQL, oneSQL, twoSQL, threeSQL, fourSQL, strSQL As String


The end product of each of the four combo boxes ended with something like this (in reality the sql statements I use are much longer, I have just edited a demo here for the ease of the exercise):

oneSQL = "SELECT * FROM INCOMENEW " & _
"WHERE INCOMENEW.date >=[Forms]![income]![btdate1] and INCOMENEW.InvoiceNumber >0;"


twoSQL = "SELECT * FROM INCOMENEW " & _
"WHERE INCOMENEW.Text1 is Null;"


threeSQL = "SELECT * FROM INCOMENEW " & _
"WHERE INCOMENEW.busnumber >=[Forms]![mainmenu]![buscode];"


threeSQL = "SELECT * FROM INCOMENEW " & _
"WHERE INCOMENEW.maxnumber >=[Forms]![income]!

;"
   
  To add the statements together I used  the following statement 
  strSQL = twoSQL & threeSQL & fourSQL & oneSQL
   
  

But that caused a problem as the complete clause found in each variable was added together..  For example the word “where” then appeared 4 times in the statement and the colon at the end also created problems.

Eventually I resolved this by creating a starting clause using a field that was common throughout all of the queries (it could easily have been the id field, but I used another field)..    I then made sure that the startSQL was the first one to be added in the chain of SQL Statements…   I then removed the colon from the end of the statements and started each statement with an “and”.

   
  He is a sample of the finished product using same statements listed above:
   
  Dim startSQL, oneSQL, twoSQL, threeSQL, fourSQL, strSQL As String


  oneSQL = "and INCOMENEW.date >=[Forms]![income]![btdate1] and INCOMENEW.InvoiceNumber >0"


   twoSQL = "and INCOMENEW.Text1 is Null"


  threeSQL = "and INCOMENEW.busnumber >=[Forms]![mainmenu]![buscode]"


  threeSQL = "and INCOMENEW.maxnumber >=[Forms]![income]![code]"
   
  startSQL = "SELECT * FROM INCOMENEW " & _
  "WHERE INCOMENEW.BUSINESSCODE =  Forms![income form]![C1]"
   
  strSQL = startSQL & twoSQL & threeSQL & fourSQL & oneSQL
   
  Forms![income form]![IncomeSubForm].Form.RecordSource = strSQL
  Forms![income form]![IncomeSubForm].Form.Requery
   


  So in the end the finished SQL Statement in the strSQL Variable might have looked something like this:

  strSQL = "SELECT * FROM INCOMENEW " & _
  "WHERE INCOMENEW.BUSINESSCODE =  Forms![income form]![C1] and INCOMENEW.date >=[Forms]![income]![btdate1] and INCOMENEW.InvoiceNumber >0 and INCOMENEW.Text1 is Null  and INCOMENEW.busnumber >=[Forms]![mainmenu]![buscode] and INCOMENEW.maxnumber >=[Forms]![income]![code]"
   


  That’s it, maybe this will help someone else…
   


  Thanks again to DCrake and Galaxiom   for your help

 

[gemma-the-husky]

when its really tricky, I use a function

so in the query have a column

DoINeedThisRow(field1,field2,field3 ... etc) with a criteria of TRUE

Now have a function

DoINeedThisRow(field1 as ..,field2 as ...,field3 as ...... etc)as boolean
in here - you can test any of these fields, against any variable of form reference in your DBS, and return true/false depending.

That's what I do for really complex evaluation.