Hey OBP,
Kindly have a look at the flash demo which i used to design a form for the query builder which i'm trying to build... I've so far been able to build only simple SQL queries with WHERE clause in my code as you know... I wanna add more functionality thru code as in this demo (to build join queries)...
Please please
I'm wondering whether filters can be applied to tables thru coding? How can it be done?
Ranga, due to being so busy with my prior commitments to give your thread the attention it deserves I have aske Jimmy the Hand to have a look at it.
He has developed something similar to you and he is really great at SQL, so he should be able to sort out your union queries, so I would respond to his post if I was you.
He can learn some new things from your code I did and hopefully help you with the SQL side.
Jimmy, while that is indeed fewer lines of code. I think you will find it substantially slower than using a normal file dialog, as it is actually starting excel behind the scenes to use it's dialog.
Cordially,
Aaron
Keep Our Board Clean!
- Please Mark your thread "Solved" if you get an acceptable response (under thread tools).
- Enclose your code in VBA tags then it will be formatted as per the VBIDE to improve readability.
Hey Jimmy,
OBP says that u've developed something similar to my query builder.. Can u post it here?
I've also done a new form design using that flash demo i've posted here.. I wanna add filtering option & implement aggregate functions as well...
Ranga
__________________________________________________________
Is anybody out there to help me with the coding for the new design i've made?
Last edited by Oorang; 07-08-2008 at 04:18 PM. Reason: Merged concurrent post by same user.
Hi Ranga
The design OBP mentioned I did was not exactly like this. It was building queries, all right, but on a very limited scale. More like changing field selections and "WHERE" clauses, based on a table or existing query. Never a "JOIN" in them.
I can upload it if you insist, but it won't be much of a help here.
Now, I don't think I'm any better at Access or coding than you are, so don't hold your breath. But maybe we can work out something together.
I understand the current step of your project is building the "FROM" clause of the SQL. The first question to ask is whether you want to do nested joins. (I guess you do, but I prefer to ask, anyway.)
The 2nd question is whether you want to limit the number of possible joins.
Jimmy
Last edited by JimmyTheHand; 06-20-2008 at 10:36 AM.
-------------------------------------------------
The more details you give, the easier it is to understand your question. Don't save the effort, tell us twice rather than not at all. The amount of info you give strongly influences the quality of answer, and also how fast you get it.
An algotrithm should be found to check circular references in the saved joins.
As I see it now, the join structure of a database must be strictly linear, which means that, from table1 to table2 there can only be maximum one route along join lines. (I'm not familiar with technical terms, but I hope it's clear what I want to say.) In other words, starting from table1, and going along join lines, you must not be able to get back to table1 (except by turning back, of course).
Saving the joins ('Save Join' button on the form) should be done in a way that supports this algorithm. I have been thinking about the algorithm, but no results yet. I suspect it's related to graph theory, but I'm no matemathician. Do you know about graph theory?
UPDATE
Found a thesis in garph theory about acyclic graphs. My understanding was that the joins don't have circular reference if
[number of joins] + 1 = [number of tables]
(Naturally, if two tables are joined by more than one pair of fields, that still counts as one join.)
I hope I didn't misinterpreted it. I would be glad if someone could confirm this... Because this condition seems quite easy to check...
Last edited by JimmyTheHand; 06-20-2008 at 10:51 AM.
-------------------------------------------------
The more details you give, the easier it is to understand your question. Don't save the effort, tell us twice rather than not at all. The amount of info you give strongly influences the quality of answer, and also how fast you get it.
Jimmy,
I want to do nested joins, but i suppose it would not be more than 3 tables at a time (at the max. it wud be 2).
I have these questions for u... I want u to answer them one by one...
1. What do u mean by limiting the no. of joins? Can u explain it in detail?
2. If the join structure of a database is linear, how can any saved join be used? I meant to ask, how can a join be built using a join that is temporarily saved?
3. I get ur idea in using graph theory for that algo, but if joins can be saved (temporarily) to the database, how can they be used to build the required query?
Do u get any ideas from that flash demo that i attached earlier?
Ranga
First I tell what I mean by JOIN. For me a JOIN is a connection between any two tables. If I connect 3 tables, that is 2 joins: One between tables A and B, and one between A and C. (Or B and C).Originally Posted by rangudu_2008
By limiting the number of joins I mean restricting these connections (essentially restricting the number connected tables) to a certain value. E.g. not allowing the user to join more than 4 tables.
I'm not sure I understand the question. I'm trying my best to answer.2. If the join structure of a database is linear, how can any saved join be used? I meant to ask, how can a join be built using a join that is temporarily saved?
By "linear" I mean that between the tables there is no loop. (Or cycle or circular reference or whatever it is called. I'm sorry, I don't know the right words, even in my own language.)
By this definition, the letter 'X' is linear. The letter 'A' is not. You start walking at the very top of 'A' , and can get back to the same spot without ever turning back. It means there is a loop in the letter 'A'. And that loop is the upper triangle. In letter 'X' there's no loop. A query builder must check whether or not the joins defined by the user make a loop.
Now. In Access query builder the user has a nice graphical interface where they can display all the tables and joins they want. But with your form, the user can select and display only one join, and two tables at a time. I tried to imagine how multiple joins can be created this way, and came up with the idea that these join definitions should be temporarily saved, one by one, in a properly designed array variable, or collection, or something. This way the user defines all the joins they want, without any actual change made to the database. When they have finished, they click on a [Build Query] button. And then the code iterates through the saved join definitions, and builds the "FROM" clause of the SQL.
How did you plan to do it?
I think this was covered in Answer #2. But to make it more clear, I didn't propose to save the joins to the batabase, even temporarily. I proposed to save their definitions to an array or collection. (By the way, have you seen the update to my previous post? )3. I get ur idea in using graph theory for that algo, but if joins can be saved (temporarily) to the database, how can they be used to build the required query?
I feel this idea getting crystallized. How is it with you?
I think the next step (or substep) would be discovering the best way to (temporarily) store these join definitions.
Yes, I did. One was the criteria builder that even OBP marked as excellent. Another example was using Acwzmain to obtain a unique query name. There were some others, but right now I can't recall them. Why do you ask?Do u get any ideas from that flash demo that i attached earlier?
Jimmy
-------------------------------------------------
The more details you give, the easier it is to understand your question. Don't save the effort, tell us twice rather than not at all. The amount of info you give strongly influences the quality of answer, and also how fast you get it.
Hello, guys, I have been doing a bit of work on "Joins" in VBA and I have come up with some things that might help you.
Assuming that you want to use the established Relationships for your Joins you can use the System Table "MSysRelationships" to find the "names" of the Relationships and which Tables and Fields they are joined by. This data can be included in your SQL String along with the Join type.
The syntax for the Join part of the string appears to be quite straightforward as shown by this excerpt from Access 2000 VBA for beginners.
If chkIngredientID Then
sFROM = sFROM & " INNER JOIN tblIceCreamIngredient i " & _
"ON s.fkIceCreamID = i.fkIceCreamID "
sWHERE = " AND i.fkIngredientID = " & cboIngredientID
End If
They have a Query Building Form in it that uses the above Join when the "chkIngredientID" check box is ticked, which means the inclusion of another table
If you want me to explore this any further let me know.
Tony,
I don't know whether the OP wants to use predefined relationships or ad-hoc created links in the query. That's his job to decide. My personal feeling is that he wants the latter, because otherwise he wouldn't want the user to specify a 'join type' in the query builder form, since that info is already part of relationships.
In case of 2 tables, SQL syntax for JOIN is very straightforward, yes.
What troubles me is the case of 3-4 or more tables, which looks a little less easy. Just choose any query in one of your databases that has several joint tables, and take a look at its SQL string. You will see lots of round brackets. If you remove them, Access will give a syntax error message, so it seems that brackets are obligatory.
I still couldn't quite grasp the logic behind the rules of using brackets, for sometimes there are more of them than what seems reasonable. Also, I'm confused about the order of tables, as they succeed one another in the SQL.
Unfortunately, the code you provided doesn't use brackets at all, and doesn't tell anything about table order. I personally am very curious how the source you referred to does these things. You can explore, if you want, or you could share a link for that source.
Jimmy
-------------------------------------------------
The more details you give, the easier it is to understand your question. Don't save the effort, tell us twice rather than not at all. The amount of info you give strongly influences the quality of answer, and also how fast you get it.
Jimmy, I will email you a copy and an explanation.
My concern with AD Hoc joins is that you could match up completely unconnected fields, whereas using the Related tables you at least know that the data types and content must match.
You do not necessarily have to use the same "Join" properties as the Relationship, just pick up the Table and Field names. I have written a routine, based on the Access VBA help topic that does that. See Form1 in the database that I have sent you.
Last edited by OBP; 06-23-2008 at 07:12 AM.
Dear Jimmy and Tony,
I'm happy about all the support i'm getting on coding for this query builder...I've the feeling that i'm at home right here @ VBAX...
Right now i'm trying to get some reference books for this that wud help and lead us in the right direction for building that algorithm but i'm sorry that i'm not successful so far... You guys are more well aware of the requirement on the coding to be done than me... I just trying to think of a better solution for designing a workflow for that algorithm which Jimmy proposed...
However, i came across some other forum while browsing; Maybe u guys can find something useful here...
http://www.utteraccess.com
P.S.: Can any of u guys copy me on that mail?
Ranga
Ranga, can you clarify what kind of Joins you want to use, ie Related fields already set up by relationships or allowing any fields to try and be joined?
If you private mail me you email address I will send you the database that I sent Jimmy.
Tony,
The join queries need to be built based on the primary key fields of any two tables at a time.
The joins need to be saved at an intermediary stage that may be used as subqueries in another query...
Plz have a look at that flash demo attached earlier.
I've explained this more mathematically using set theory concept.
Plz refer to the attachment with this post.
Ranga
__________________________________________________________
Tony n Jimmy,
Probably any of u guys can get ideas from Oorang on this algorithm...
(I don't his real name)
Ranga
rofl omg Please don't make me read the whole thread what was I helping on? (It's Aaron btw)
Cordially,
Aaron
Keep Our Board Clean!
- Please Mark your thread "Solved" if you get an acceptable response (under thread tools).
- Enclose your code in VBA tags then it will be formatted as per the VBIDE to improve readability.
Aaron,
I had developed a code for a query builder some time back which can build simple SQL queries... I would like to use it up for some MIS work (Management Information System) so i would like to improve the code.
What needs to be implemented in this new version is to build up ad-hoc join queries through VBA code. A join query can be built with more than 2 tables that can be used as part of subqueries as well.
To build join queries more that can combine more than 2 tables, we are thinking of a solution and we wanna develop an algorithm.
U can see the basic code that i developed, the new form design i?ve made and a flash demo that shows what needs to be implemented through VBA in the previous pages.
ranga, I am afraid the maths and algorithm concept leaves me cold.
I prefer to just "do it", it might not be the best way in the end, but I prefer "doing" to planning.
I would just combine the 2 methods in the database that I sent you. One identifies the available Indexed Key fields for each table and the other provides the method of joining them. I haven't looked at joining more than 2 tables though. But it can't be that difficult to produce some Queries that provide the SQL to do that.
Hi Rangudu,
I'll be honest I haven't really been following the thread closely, but why can't you just use a "Natural Language" approach? Where the interface has five drop downs "Table1 Field1 Join Table2 Field2". Then just let the center drop down be the join type. So just reparse that info into a SQL statement. "From Table1 Join Table2 On Field1 = Field2" if you want to get really fancy you can add a drop down box for the relational comparison operator, even Access's query builder doesn't do that. Access only has 3 joins anyways Inner, Left (Outer), & Right (Outer).
Cordially,
Aaron
Keep Our Board Clean!
- Please Mark your thread "Solved" if you get an acceptable response (under thread tools).
- Enclose your code in VBA tags then it will be formatted as per the VBIDE to improve readability.
Aaron,
I want a clear explanation about what u are suggesting me to do...
Ranga
Ranga, try this one.
It's how I envisioned this Join-builder thing. I had to change the user interface a little bit to suit my plans, and also added a few tables for testing purposes.
The table called "TableJoins" is necessary for the form to work, it contains the saved joins the SQL is being built from. The other tables are just decor.
On the form, the new listbox contains the saved joins (the same as table "TableJoins"). I programmed buttons Save Join, Remove Join and Build SQL. This latter creates and displays the FROM clause of the SQL string.
Also, upon loading the form, the comboboxes corresponding to left and right tables are prepared.
HTH
Jimmy
Last edited by JimmyTheHand; 07-01-2008 at 09:59 PM.
-------------------------------------------------
The more details you give, the easier it is to understand your question. Don't save the effort, tell us twice rather than not at all. The amount of info you give strongly influences the quality of answer, and also how fast you get it.
Posting Permissions
Reply With Quote
