Generate query dynamically

[Heidestrand]

Hello community,

I have a problem I want to solve but I don't know how to properly do it. I already learned here that I can alter a SQL string by typing something into a text box and reading out the content (mySQL="....WHERE (" & strDetec & " is null..)
But what I want to do now is: I have 6 check boxes and I want to dynmically generate a query when I check some of the boxes. Each check box stands for a table. So I want to JOIN the tables how I want by checking the boxes.

My example: I have a text box where I type in a number. Then I have 6 tables with 3 columns each. In every table this number exists in a column. So by checking the boxes I want to "build" my query f.e. with table 1, 3 and 5 where the number is the same.

I'd be awesome if this is possible because it would really help me accomplish my goal with my database.

Is this possible?

Best regards,

heide

 

[Ranman256]

You cant use form boxes in a query if there's nothing in them..so..
Test all controls for a possible filter then build the where clause.

if not isnull(cboState) then   sWhere = sWhere & " and [state]='" & cboState & "'"
if not IsNull(txtName) then    sWhere = sWhere & " and [Name]='" & txtName & "'"
if not IsNull(chkContact) then sWhere = sWhere & " and [Contact]=" & chkContact.value

    'remove 1st And
sWhere= mid(sWhere,5)

     'save the sql as a qry or open the sql

set qdf = currentdb.querYdefs("qsResults")
qdf.sql = ssql
qdf.close
docmd.openquery qdf.name

 

[Heidestrand]

Hey Ranman256, thank you for your answer

Hm, I guess I didn't express myself correctly. So I was thinking of an If clause,
something like with a check box:

If optionKR.Value = True Then
       sqlKR = "SELECT sapmat_materialID, sapmat_aemenge " & _
                   "FROM tblSAPMat " & _
                   "WHERE sapmat_sAPNr = '" & strOne & "'"
                
qdf.sql = sqlKR

But what I want is: I want to build the FROM part of a query by the help of.. yeah.. form controls.
So:

SELECT *
FROM tblA LEFT JOIN tblB ON.... <- I want to build this part dynamically by "clicking" it together
WHERE ...

So you're talking about the WHERE part, but I'm facing the FROM part

The user should be able to enter a number and chose the tables that he wants to join.

 

[plog]

Few thoughts:

1. This sounds like an improperly structured database. Can you post a screen shot of your relationships?

2. You are essentially rebuilding the Query section of Access within Access. User's of access can already design their own queries and select the datasources they want, by creating a new query. Why not just teach your users how to generate queries within Access?

3. The simplest way to do this, isn't by allowing them to dynamically creating a query by selecting the tables, but by creating a generic query with all those possible LEFT JOINed tables already LEFT JOINed. Then you allow them to run that query.

 

[Heidestrand]

Some thoughts to your thoughts:

1. Honestly.. I don't know if my relationships represent what I want to do. I mean I know why you have relationships, the meaning behind it. But I think that I don't really rely on them. Further down I'm going to explain what I want to do in my db.
So right now, my relationships look like this:



2. Yeah my goal is to make it as easy as possible. See further down.

3. This is also an idea. But it's not possible since I have 17 tables that I want to join.


So I guess I need to explain a little bit so that you understand what I'm going to do. And I really hope that you can help me or give me tips because I feel like I put so much work into it (the code, the design) that I miss something important here.

The goal of my database: I wrote a vba script that imports me a table from Excel into Access. This Excel table consists of over 50.000 record sets containing unstructured, unprocessed information about an order like ordered material, components for systems we sell, countries and so forth.

Now I want to make these information useful. Therefore I have tables that I use to match. So I apply them on the imported Excel table and store every match into a special table with the order number and the component, it's like finding the needle in the haystack. And my tables give me the look of the needle
So I have 17 "store tables" for all the different kinds of matches.

And now I want to find answers for questions like: how many component from type X were sold in India? So I have to combine the two matching tables tblComponentX and tblCountries where the order number is the same and count it. Correct?
This should be easy for every user in my company.

Please tell me if my plan works like this or how I can achieve my goal more efficiently. Because.. I'm new into Access and honestly don't know if the way I take is the right one.

 

[namliam]

I can understand PLOG's request for the relationships, whenever people talk of table1,2,3,4,5 it is often times a sure sign of a much deeper problem with database design

The basic idea of custom build sql in access vba goes something like

dim mySelect as string
dim myFrom as string
dim mywhere as string
dim mygroupby as string
dim myhaving as string
dim myorderby as string

mySelect = " Select "
myfrom  = " From tblMasterOrder "

If OptionThisField then
    mySelect = myselect & " ThisField, "
endif
If OptionThatField then
    mySelect = myselect & " ThatField, "
endif

if optionSold then
    myfrom = myfrom & " INNER JOIN tblSAPSysSold on tblMasterOrder.syssold_id_f = tblSAPSysSold.syssold_id "
endif


currentdb.querydefs("SomeEmptyQuery") = Myselect & myFrom & mywhere & mygroupby & myhaving & myorderby

Offcourse this is a basic idea that needs to be expanded upon, but I hope you get the idea.
Also some cleaning need be done, i.e. remove the last , in the Select

Dont know if you also want to allow for left and right joins group by and all the mess.... Can get kindoff complicated

 

[namliam]

Just FWY:
Your relationships will break unless you have a requirement that tblSAPEKLand and tblSAPWELand is Always the same sys_coun_id_f

To remedy you should add your table tbl_syscountry two times one for each of the tables.

 

[Heidestrand]

Hey namliam, thank you a lot for the code. I'm trying to work this out, this is indeed helpful for me.

 

[plog]

1. Your table structure doesn't have any glaring errors.

3. The number of tables you want to include doesn't make this impossible. I still think this is the correct way to go.