Filtering Tables: Check boxes

[anshaik]

Hello everyone, I am working on Access 2003 to create a process in which I will be able to filter the data from one table (in my case, named DSO) in to another temporary table (tTable2) for viewing by the user.

In my application, a user will log in and be directed to a main page where as of now they have three options:
1.) View/Edit the DSO Table (which has 399 records, at this moment)
2.) Filter the DSO table (in to a temporary table, tTable2)
3.) View the filtered table (tTable2)

Now on to my code/problem:

I have a form in which 10 checkboxes are shown, the user is to select which relevent data they would need and the click the button to generate this data. Basically my code is supposed to check which boxes are checked and then using that data it is to append data from the original table, DSO, in to a temporary table, tTable2.

This process works fine and well, how I wanted to, however, now I run into my problem. The issue is duplicates. The data I need appended to the new temporary table works fine, however, there are some columns that have multiple instances of data in them. Therefore, those datasets have recorded multiple times. Or, that is how I have evaluated the problem that is occuring, and instead of speculating I will post my code:

Public Sub searchCmd_Click()
Dim SQL, strPlano, strPrc, strExton, strBcp, strRslteng, strRsltspn, strService, strSales, strId, strFf As String
'Removes previous holder tTable2
SQL = "DROP TABLE tTable2"
CurrentDb.Execute (SQL)
'Create a temp table to filter data to (columns)
SQL = "SELECT DSO.* INTO tTable2 FROM DSO"
CurrentDb.Execute (SQL)
'Clears the information (rows)
CurrentDb.Execute "Delete * from tTable2"
'The following set of if statements find the value of the checkbox (true/false) and assigns a string associated
'with that checkbox either a delimiter, %, or the string value of what the column associated is named
'Checks if exton checkbox is true/fale
If extonCheck.Value = 0 Then
    strExton = "%"
Else
    strExton = "Exton"
End If
'Checks if plano checkbox is true/fale
If planoCheck.Value = 0 Then
    strPlano = "%"
Else
    strPlano = "Plano"
End If
'Checks if PRC checkbox is true/fale
If prcCheck.Value = 0 Then
    strPrc = "%"
Else
    strPrc = "PRC"
End If
'Checks if BCP checkbox is true/fale
If bcpCheck.Value = 0 Then
    strBcp = "%"
Else
    strBcp = "BCP"
End If
'Checks if rslteng checkbox is true/fale
If rsltEngCheck.Value = 0 Then
    strRslteng = "%"
Else
    strRslteng = "RSLT English"
End If
'Checks if rsltspn checkbox is true/fale
If rsltSpnCheck.Value = 0 Then
    strRsltspn = "%"
Else
    strRsltspn = "RSLT SPN"
End If
'Checks if service checkbox is true/fale
If serviceCheck.Value = 0 Then
    strService = "%"
Else
    strService = "Service"
End If
'Checks if sales checkbox is true/fale
If salesCheck.Value = 0 Then
    strSales = "%"
Else
    strSales = "Sales"
End If
'Checks if id checkbox is true/fale
If idCheck.Value = 0 Then
    strId = "%"
Else
    strId = "ID"
End If
'Checks if ff checkbox is true/fale
If ffCheck.Value = 0 Then
    strFf = "%"
Else
    strFf = "FF"
End If
'The next set of if statements append data to the tTable2
'The SQL used for the insertions uses the logic that if a box is checked then
'all slots where that specific column is not " " (blank) then it will be inserted into the
'temporary table; tTable2. This iterates through each checkbox, using if statements, until
'all have been iterated through. Result is append from DSO into tTable2 wherever necessary
'***problem: duplicates are created
 
If strExton <> "%" Then 'if checked, append info to tTable2
    SQL = "INSERT INTO tTable2 SELECT * FROM DSO WHERE " & strExton & "<>""  "" "
    CurrentDb.Execute (SQL)
End If
If strPlano <> "%" Then 'if checked, append info to tTable2
    SQL = "INSERT INTO tTable2 SELECT * FROM DSO WHERE " & strPlano & "<>""  "" "
    CurrentDb.Execute (SQL)
End If
If strPrc <> "%" Then 'if checked, append info to tTable2
    SQL = "INSERT INTO tTable2 SELECT * FROM DSO WHERE " & strPrc & "<>""  "" "
    CurrentDb.Execute (SQL)
End If
If strBcp <> "%" Then 'if checked, append info to tTable2
    SQL = "INSERT INTO tTable2 SELECT * FROM DSO WHERE " & strBcp & "<>""  "" "
    CurrentDb.Execute (SQL)
End If
If strRslteng <> "%" Then 'if checked, append info to tTable2
    SQL = "INSERT INTO tTable2 SELECT * FROM DSO WHERE [" & strRslteng & "]<>""  "" "
    CurrentDb.Execute (SQL)
End If
If strRsltspn <> "%" Then 'if checked, append info to tTable2
    SQL = "INSERT INTO tTable2 SELECT * FROM DSO WHERE [" & strRsltspn & "]<>""  "" "
    CurrentDb.Execute (SQL)
End If
 
'Refresh the form
Form_filterForm.Requery
'Signify the end of the process to the user
MsgBox "The filtering is complete."
End Sub

As of now my code is focusing on the first 6 checkboxes, the locations: Exton, Plano..etc, instead of the Service/Sales and System. Once I figure that portion out I will expand to the rest.

An idea I had to fix this problem was to select all the duplicate rows and delete one set of the duplicates. I am not sure how to follow through with that idea.

Another idea tried to use DISTINCTROW SQL, but that also ended up not working.

I am new to the Access, VB, and SQL worlds, so some of my code may seem..newbish? Any help would be appreciated, or if another idea to perform the same task in a more efficient/easier way would be great as well.

Thanks you!

 

[twoplustwo]

Hmm I would build the SQL in the VBA based on each parameter, insert that SQL into a query and run/open said query to view. I don't see a need to replicate and store the data for viewing purposes.

Ttry using a group by in the SQL to remove duplicates.

Let us know if you need any help.

 

[twoplustwo]

You can also tidy the code by saying:

strRsltspn = Me!rsltSpnCheck.Value

No need for the IF statements.

 

[anshaik]

I ultimately would like to perform other functions with the data if the user would need to, such as: print, export to excel, or possibly other functions on the data placed in the tTable.

I was unsure if these functions are easily implemented in viewing a query, so I abandoned that idea when beginning this process.

*EDIT:

I am also now looking in to the GROUP BY function of SQL that you mentioned.

 

[twoplustwo]

I found something similar:

Private Sub cmdTest_Click()
 
Dim db As DAO.Database
Dim qDef As DAO.QueryDef
Dim sSql As String
Dim sParameter As String
 
Set db = CurrentDb
Set qDef = db.QueryDefs("qryTest")
 
sSql = "SELECT * FROM tblCommitted WHERE Segmentation = "
 
If Me.chkTest = -1 Then
   sParameter = "'SME'"
Else
   sParameter = "'Multi-site'"
End If
 
sSql = sSql & sParameter
qDef.SQL = sSql
DoCmd.OpenQuery ("qryTest")
 
Set qDef = Nothing
 
End Sub

This code sits behind a button on a form.

It sets up the QueryDef so I can insert my edited SQL into a query stored in the database i.e. no need to append data/excessive bloating etc. I then base sParameter on the value of the checkbox.

Let us know if you have any questions

 

[twoplustwo]

I ultimately would like to perform other functions with the data if the user would need to, such as: print, export to excel, or possibly other functions on the data placed in the tTable.

I was unsure if these functions are easily implemented in viewing a query, so I abandoned that idea when beginning this process.

*EDIT:

I am also now looking in to the GROUP BY function of SQL that you mentioned.

As far as I know you have the same options when outputting data in a table as you do a query.

 

[anshaik]

Ok, I am attempting to work with the code you just posted. Some clearifying questions on how it is to work:

(this is how I understand what is happening)

- A query is already created, say I call it 'test'
- This code is to modify the query and add "SELECT -- " based on my checkboxes


So something like:

Public Sub searchCmd_Click()
Dim db As DAO.Database
Dim qDef As DAO.QueryDef
Dim sParameter, sSql, strPlano, strExton As String
Set db = CurrentDb
Set qDef = db.QueryDefs("test")
 
sSql = "SELECT * FROM DSO WHERE Segmentation = "
 
 
If extonCheck.Value = 0 Then
    'strexton gets something
Else
    'strexton gets something
End If

If planoCheck.Value = 0 Then
    'strplano gets something
Else
    'strplano gets something
End If
 
sSql = sSql & strExton & strPlano
qDef.sSql = sSql
DoCmd.OpenQuery ("test")
 
Set qDef = Nothing
 
 
 
End Sub

I'm still a little confused..can you guide me with the code I have down now? I believe I am not completely understanding how your code is working.

 

[twoplustwo]

Ok, I am attempting to work with the code you just posted. Some clearifying questions on how it is to work:

(this is how I understand what is happening)

- A query is already created, say I call it 'test'
- This code is to modify the query and add "SELECT -- " based on my checkboxes


So something like:

Public Sub searchCmd_Click()
Dim db As DAO.Database
Dim qDef As DAO.QueryDef
Dim sParameter, sSql, strPlano, strExton As String
Set db = CurrentDb
Set qDef = db.QueryDefs("test")
 
sSql = "SELECT * FROM DSO WHERE Segmentation = "
 
 
If extonCheck.Value = 0 Then
    'strexton gets something
Else
    'strexton gets something
End If
 
If planoCheck.Value = 0 Then
    'strplano gets something
Else
    'strplano gets something
End If
 
sSql = sSql & strExton & strPlano
qDef.sSql = sSql
DoCmd.OpenQuery ("test")
 
Set qDef = Nothing
 
 
 
End Sub

I'm still a little confused..can you guide me with the code I have down now? I believe I am not completely understanding how your code is working.

Try your code with one variable i.e. strExton

The SQL would need altering for more than one.

 

[anshaik]

Just to clearify (may have misunderstood the workings of this SQL):

Working with one variable would imply that these checkboxes are choices, resulting in all the values with Exton being returned or all the values of Plano being returned..etc etc.

But I may need all the data with Plano & Exton returned, and not the 8 other choices that I have. Another case would be the data from checkboxes Plano, exton, x, y, z, and not the other 6 datasets.

 

[twoplustwo]

Just get it working for one variable first

 

[anshaik]

Ok, this code works for what I need for one variable, looking at the Exton column:

Public Sub searchCmd_Click()
Dim db As DAO.Database
Dim qDef As DAO.QueryDef
Dim sParameter, sSql, strPlano, strExton As String
Set db = CurrentDb
Set qDef = db.QueryDefs("test")
sSql = "SELECT * FROM DSO WHERE "
MsgBox sSql
'Checks if exton checkbox is true/fale
If extonCheck.Value = 0 Then
    strExton = " "
Else
    strExton = "Exton<> "" "" "
End If
'Checks if plano checkbox is true/fale
'If planoCheck.Value = 0 Then
 
'Else
 
'End If
 
 
sSql = sSql & strExton
MsgBox sSql
qDef.SQL = sSql
DoCmd.OpenQuery ("test")
 
Set qDef = Nothing
'Refresh the form
Form_filterForm.Requery
 
End Sub

That works for one option, how do I expand it to the others?

(The msgbox's were just me checking the SQL statements before they're executed)

 

[twoplustwo]

Use an AND clause in the SQL i.e.

WHERE Field1 = 'A'
AND Field2 = 'United Kingdom'
etc.

For each new criteria you'll need an AND clause and new quotations.

 

[anshaik]

Using AND comes back to the problem I was thinking earlier:

AND results in narrowing down my filter to return everything that has Plano as " " and Exton as " "

Using this code I got that result:

'Checks if exton checkbox is true/fale
If extonCheck.Value = 0 Then
    strExton = " "
Else
    strExton = "Exton<> "" "" "
End If
'Checks if plano checkbox is true/fale
If planoCheck.Value = 0 Then
    strPlano = " "
Else
    strPlano = "AND Plano<> "" "" "
End If

sSql = sSql & strExton & strPlano
MsgBox sSql

Narrowed down to these two variables, if they are both checked, what I need is everywhere there is Exton & everywhere there is Plano to be returned, not the union of these two. Am I making a mistake in my SQL?

 

[anshaik]

I think I fixed that problem using OR, instead of AND. Now a syntax of my code problem arises:

If I do not check the Exton box, my SQL string would look like:
"SELECT * FROM DSO WHERE OR Plano..."

A blank where the Exton string is, creating issues. Ideas on how I could overcome that?

 

[twoplustwo]

sSQL = "SELECT * FROM tblName WHERE "
 
If extonCheck.Value = 0 Then
    strExton = " "
Else
    strExton = "Exton<> "" "" "
End If

If planoCheck.Value = 0 Then
    strPlano = " "
Else
    If strExton = " " THEN
        strPlano = "Plano<> "" "" "
    Else
        strPlano = "OR Plano = 'Something'"
End If
 
sSql = sSql & strExton & strPlano

There is probably something more efficient than that method however.

 

[anshaik]

Yeah, that works. Thanks a lot twoplustwo. I will continue to implement this in the other 4 checkboxes and then see if I have any more questions.

 

[dfenton]

You can also tidy the code by saying:

strRsltspn = Me!rsltSpnCheck.Value

No need for the IF statements.

It's much simpler to just use:

strRsltspn = Me!rsltSpnCheck

...as .Value is the default property of a member of the controls collection on a form.