QUERY TO SORT BY MORE THAN ONE COLUMN

C

chris_quinn26

I have a query on the fly already that works perfect. I can select
what i want to sort and it works great. Here is the problem. The
master table that stores my information is for company contacts. Each
contact has up to 20 tags. I need my tag filter to search in all 20
tags for the items selected in my list box. For example, say i am
searching for a landscaping company for sod repair. There will be
several companies in my master file that will have sod repair, but it
might be that company 1 has this information on tag2 and company 500
has it in tag20. I want both of these to show up.

****************** below is my forms code ********************

Private Sub BUTTON_Filter_Criteria_Click()
On Error GoTo Err_BUTTON_Filter_Criteria

Dim strCritString As String
Dim strBuildString As String
Dim strFullString As String
Dim intPosWhere As Integer
Dim intPosSemi As Integer
Dim qd As QueryDef
Dim rst As DAO.Recordset
Dim frm As Form
Dim booFirstFlag As Boolean
Dim intSelItem As Variant


booFirstFlag = False 'this flag shows whether a where has yet
been added
Set frm = Forms("FORM-User_Select_Query")
Set qd = CurrentDb.QueryDefs("QUERY--User_Select_Query")
strFullString = qd.SQL ' gets the SQL from the existing query

'trim any existing where clause from the SQL
intPosWhere = InStr(1, strFullString, "WHERE")
intPosSemi = InStrRev(strFullString, ";")
If intPosWhere > 0 Then
strFullString = Left(strFullString, intPosWhere - 3)
Else: strFullString = Left(strFullString, intPosSemi - 1)
End If


'filter TYPE
If frm.[CHECKBOX_type] And frm.[LIST_Type].ItemsSelected.Count Then
booFirstFlag = True
strCritString = "WHERE [TABLE--Contact_Type]![ID] In("
strBuildString = ""
For Each intSelItem In frm.[LIST_Type].ItemsSelected
strBuildString = strBuildString & "," &
frm.[LIST_Type].ItemData(intSelItem)
Next intSelItem
If strBuildString <> "" Then
strBuildString = Right(strBuildString, Len(strBuildString)
- 1)
End If 'strips out superfluous leading comma
strCritString = strCritString & strBuildString & ")"
End If

'filter company name
If frm.[CHECKBOX_company] And
frm.[LIST_Company].ItemsSelected.Count Then
If booFirstFlag = True Then
strCritString = strCritString & "AND"
Else
strCritString = "WHERE"
booFirstFlag = True
End If
strCritString = strCritString & "[TABLE--Contacts]![ID] In("
strBuildString = ""
For Each intSelItem In frm.[LIST_Company].ItemsSelected
strBuildString = strBuildString & "," &
frm.[LIST_Company].ItemData(intSelItem)
Next intSelItem
If strBuildString <> "" Then
strBuildString = Right(strBuildString, Len(strBuildString)
- 1)
End If 'strips out superfluous leading comma
strCritString = strCritString & strBuildString & ")"
End If


'filter TAG
If frm.[CHECKBOX_tag] And frm.[LIST_Tag].ItemsSelected.Count Then
If booFirstFlag = True Then
strCritString = strCritString & "AND"
Else
strCritString = "WHERE"
booFirstFlag = True
End If
strCritString = strCritString & "[TABLE--Tag_Names]![ID] In("
strBuildString = ""
For Each intSelItem In frm.[LIST_Tag].ItemsSelected
strBuildString = strBuildString & "," &
frm.[LIST_Tag].ItemData(intSelItem)
Next intSelItem
If strBuildString <> "" Then
strBuildString = Right(strBuildString, Len(strBuildString)
- 1)
End If 'strips out superfluous leading comma
strCritString = strCritString & strBuildString & ")"
End If


'filter DBE
If frm.[CHECKBOX_dbe] And frm.[LIST_Dbe].ItemsSelected.Count Then
If booFirstFlag = True Then
strCritString = strCritString & "AND"
Else
strCritString = "WHERE"
booFirstFlag = True
End If
strCritString = strCritString & "[TABLE--DBE]![ID] In("
strBuildString = ""
For Each intSelItem In frm.[LIST_Dbe].ItemsSelected
strBuildString = strBuildString & "," &
frm.[LIST_Dbe].ItemData(intSelItem)
Next intSelItem
If strBuildString <> "" Then
strBuildString = Right(strBuildString, Len(strBuildString)
- 1)
End If 'strips out superfluous leading comma
strCritString = strCritString & strBuildString & ")"
End If

'Write recoded SQL string back to query
strFullString = strFullString & vbCrLf & strCritString
qd.SQL = strFullString

'Check for no hits
Set rst = CurrentDb.OpenRecordset("QUERY--User_Select_Query")
If rst.BOF And rst.EOF Then
MsgBox "NO Records to Process"
Exit Sub
End If

rst.Close ' free up resources

'Open macro
DoCmd.RunMacro ("MACRO--Append_Rfq_To_Temp_Table")

Set rst = Nothing 'free up resources
Set qd = Nothing 'free up resources


Exit_BUTTON_Filter_Criteria:
Exit Sub

Err_BUTTON_Filter_Criteria:
MsgBox Err.Description
Resume Exit_BUTTON_Filter_Criteria


End Sub





****************below is my query coding *****************************

SELECT DISTINCT [TABLE--Contacts].ID, [TABLE--Contacts].TYPE,
[TABLE--Contacts].[COMPANY NAME], [TABLE--Contacts].[CONTACT PERSON],
[TABLE--Contacts].[ADDRESS 1], [TABLE--Contacts].[ADDRESS 2],
[TABLE--Contacts].[CITY/STATE/ZIP], [TABLE--Contacts].[OFFICE NUMBER],
[TABLE--Contacts].[FAX NUMBER], [TABLE--Contacts].[CELL NUMBER],
[TABLE--Contacts].DBE, [TABLE--Contacts].EMAIL,
[TABLE--Contacts].[COMPANY WEBSITE], [TABLE--Contacts].[TAG 01],
[TABLE--Contacts].[TAG 02], [TABLE--Contacts].[TAG 03],
[TABLE--Contacts].[TAG 04], [TABLE--Contacts].[TAG 05],
[TABLE--Contacts].[TAG 06], [TABLE--Contacts].[TAG 07],
[TABLE--Contacts].[TAG 08], [TABLE--Contacts].[TAG 09],
[TABLE--Contacts].[TAG 10], [TABLE--Contacts].[TAG 11],
[TABLE--Contacts].[TAG 12], [TABLE--Contacts].[TAG 13],
[TABLE--Contacts].[TAG 14], [TABLE--Contacts].[TAG 15],
[TABLE--Contacts].[TAG 16], [TABLE--Contacts].[TAG 17],
[TABLE--Contacts].[TAG 18], [TABLE--Contacts].[TAG 19],
[TABLE--Contacts].[TAG 20], [TABLE--Contacts].NOTES

FROM [TABLE--Tag_Names] INNER JOIN ([TABLE--DBE] INNER JOIN
([TABLE--Contact_Type] INNER JOIN [TABLE--Contacts] ON
[TABLE--Contact_Type].TYPE = [TABLE--Contacts].TYPE) ON
[TABLE--DBE].[YES OR NO] = [TABLE--Contacts].DBE) ON
[TABLE--Tag_Names].[TAG NAME] = [TABLE--Contacts].[TAG 01];
 
A

Allen Browne

These tables need to be redesigned.

One company can do many types of operation.
One operation can be done by many companies.
You therefore have a many-to-many relation between the companies, and the
operations they perform.


1. Company (one record for each company, with a CompanyID primary key).

2. Operation (one record for each operation type, with an OperationID pk.)

3. CompanyOperation (one record for each combination). Fields:
CompanyID who does this
OperationID which type of operation they do.
So, if ABC company does 3 types of operations, they will have 3 records in
this table.

The interface will be a main form bound to the Company table, with a subform
bound to the CompanyOperation table. You enter as many rows in the subform
as apply to that company, using a combo to select the Operation on each row.

With this structure it is dead easy to select the companies who do sod
repair: Just create a query using the CompanyOperation table, and there is
only one field to search.

The example above is the standard way to handle this data. The junction
table (#3 above) resolves the many-to-many relation into a pair of
one-to-many relations. This is the *only* way to handle this kind of data in
a relational database.

If it still sounds foreign, here's another example to read up on:
Relationships between Tables (School Grades example)
at:
http://allenbrowne.com/casu-06.html
and a whole bunch more links that describe these relational concepts:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a query on the fly already that works perfect. I can select
what i want to sort and it works great. Here is the problem. The
master table that stores my information is for company contacts. Each
contact has up to 20 tags. I need my tag filter to search in all 20
tags for the items selected in my list box. For example, say i am
searching for a landscaping company for sod repair. There will be
several companies in my master file that will have sod repair, but it
might be that company 1 has this information on tag2 and company 500
has it in tag20. I want both of these to show up.

****************** below is my forms code ********************

Private Sub BUTTON_Filter_Criteria_Click()
On Error GoTo Err_BUTTON_Filter_Criteria

Dim strCritString As String
Dim strBuildString As String
Dim strFullString As String
Dim intPosWhere As Integer
Dim intPosSemi As Integer
Dim qd As QueryDef
Dim rst As DAO.Recordset
Dim frm As Form
Dim booFirstFlag As Boolean
Dim intSelItem As Variant


booFirstFlag = False 'this flag shows whether a where has yet
been added
Set frm = Forms("FORM-User_Select_Query")
Set qd = CurrentDb.QueryDefs("QUERY--User_Select_Query")
strFullString = qd.SQL ' gets the SQL from the existing query

'trim any existing where clause from the SQL
intPosWhere = InStr(1, strFullString, "WHERE")
intPosSemi = InStrRev(strFullString, ";")
If intPosWhere > 0 Then
strFullString = Left(strFullString, intPosWhere - 3)
Else: strFullString = Left(strFullString, intPosSemi - 1)
End If


'filter TYPE
If frm.[CHECKBOX_type] And frm.[LIST_Type].ItemsSelected.Count Then
booFirstFlag = True
strCritString = "WHERE [TABLE--Contact_Type]![ID] In("
strBuildString = ""
For Each intSelItem In frm.[LIST_Type].ItemsSelected
strBuildString = strBuildString & "," &
frm.[LIST_Type].ItemData(intSelItem)
Next intSelItem
If strBuildString <> "" Then
strBuildString = Right(strBuildString, Len(strBuildString)
- 1)
End If 'strips out superfluous leading comma
strCritString = strCritString & strBuildString & ")"
End If

'filter company name
If frm.[CHECKBOX_company] And
frm.[LIST_Company].ItemsSelected.Count Then
If booFirstFlag = True Then
strCritString = strCritString & "AND"
Else
strCritString = "WHERE"
booFirstFlag = True
End If
strCritString = strCritString & "[TABLE--Contacts]![ID] In("
strBuildString = ""
For Each intSelItem In frm.[LIST_Company].ItemsSelected
strBuildString = strBuildString & "," &
frm.[LIST_Company].ItemData(intSelItem)
Next intSelItem
If strBuildString <> "" Then
strBuildString = Right(strBuildString, Len(strBuildString)
- 1)
End If 'strips out superfluous leading comma
strCritString = strCritString & strBuildString & ")"
End If


'filter TAG
If frm.[CHECKBOX_tag] And frm.[LIST_Tag].ItemsSelected.Count Then
If booFirstFlag = True Then
strCritString = strCritString & "AND"
Else
strCritString = "WHERE"
booFirstFlag = True
End If
strCritString = strCritString & "[TABLE--Tag_Names]![ID] In("
strBuildString = ""
For Each intSelItem In frm.[LIST_Tag].ItemsSelected
strBuildString = strBuildString & "," &
frm.[LIST_Tag].ItemData(intSelItem)
Next intSelItem
If strBuildString <> "" Then
strBuildString = Right(strBuildString, Len(strBuildString)
- 1)
End If 'strips out superfluous leading comma
strCritString = strCritString & strBuildString & ")"
End If


'filter DBE
If frm.[CHECKBOX_dbe] And frm.[LIST_Dbe].ItemsSelected.Count Then
If booFirstFlag = True Then
strCritString = strCritString & "AND"
Else
strCritString = "WHERE"
booFirstFlag = True
End If
strCritString = strCritString & "[TABLE--DBE]![ID] In("
strBuildString = ""
For Each intSelItem In frm.[LIST_Dbe].ItemsSelected
strBuildString = strBuildString & "," &
frm.[LIST_Dbe].ItemData(intSelItem)
Next intSelItem
If strBuildString <> "" Then
strBuildString = Right(strBuildString, Len(strBuildString)
- 1)
End If 'strips out superfluous leading comma
strCritString = strCritString & strBuildString & ")"
End If

'Write recoded SQL string back to query
strFullString = strFullString & vbCrLf & strCritString
qd.SQL = strFullString

'Check for no hits
Set rst = CurrentDb.OpenRecordset("QUERY--User_Select_Query")
If rst.BOF And rst.EOF Then
MsgBox "NO Records to Process"
Exit Sub
End If

rst.Close ' free up resources

'Open macro
DoCmd.RunMacro ("MACRO--Append_Rfq_To_Temp_Table")

Set rst = Nothing 'free up resources
Set qd = Nothing 'free up resources


Exit_BUTTON_Filter_Criteria:
Exit Sub

Err_BUTTON_Filter_Criteria:
MsgBox Err.Description
Resume Exit_BUTTON_Filter_Criteria
End Sub

****************below is my query coding *****************************

SELECT DISTINCT [TABLE--Contacts].ID, [TABLE--Contacts].TYPE,
[TABLE--Contacts].[COMPANY NAME], [TABLE--Contacts].[CONTACT PERSON],
[TABLE--Contacts].[ADDRESS 1], [TABLE--Contacts].[ADDRESS 2],
[TABLE--Contacts].[CITY/STATE/ZIP], [TABLE--Contacts].[OFFICE NUMBER],
[TABLE--Contacts].[FAX NUMBER], [TABLE--Contacts].[CELL NUMBER],
[TABLE--Contacts].DBE, [TABLE--Contacts].EMAIL,
[TABLE--Contacts].[COMPANY WEBSITE], [TABLE--Contacts].[TAG 01],
[TABLE--Contacts].[TAG 02], [TABLE--Contacts].[TAG 03],
[TABLE--Contacts].[TAG 04], [TABLE--Contacts].[TAG 05],
[TABLE--Contacts].[TAG 06], [TABLE--Contacts].[TAG 07],
[TABLE--Contacts].[TAG 08], [TABLE--Contacts].[TAG 09],
[TABLE--Contacts].[TAG 10], [TABLE--Contacts].[TAG 11],
[TABLE--Contacts].[TAG 12], [TABLE--Contacts].[TAG 13],
[TABLE--Contacts].[TAG 14], [TABLE--Contacts].[TAG 15],
[TABLE--Contacts].[TAG 16], [TABLE--Contacts].[TAG 17],
[TABLE--Contacts].[TAG 18], [TABLE--Contacts].[TAG 19],
[TABLE--Contacts].[TAG 20], [TABLE--Contacts].NOTES

FROM [TABLE--Tag_Names] INNER JOIN ([TABLE--DBE] INNER JOIN
([TABLE--Contact_Type] INNER JOIN [TABLE--Contacts] ON
[TABLE--Contact_Type].TYPE = [TABLE--Contacts].TYPE) ON
[TABLE--DBE].[YES OR NO] = [TABLE--Contacts].DBE) ON
[TABLE--Tag_Names].[TAG NAME] = [TABLE--Contacts].[TAG 01];
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

MSACCESS.EXE Hangs in Memory - NEED HELP! 2
Crosstab query on the fly 1
Running a parameter query off more than one multi-select list box 2

Top