Outer Join creating duplicates in dynamic query search results (1 Viewer)

dillonhh · Jan 22, 2008

Outer Join and duplicates

Now that i have read this again, i think it could be summed up into one question...if i have a form based off a query with an outer join that has various duplicate records, is there a way to use the recordset in an if statement that says something like if this recordID = that recordID then dont show one of them...hence not showing the duplicate field data in the form.

If you want a more specific description of the problem, read on, otherwise don't read on.

Hi All,

So I hope I can explain this ok....here goes....

I have a search using dynamic queries: I have a form where the user can put in various information he wants to search to find a record. In this case it is searching for Hotels. So the user can search a country to see all of the hotels in that country. Also, the user can search an interest like Beach or Nature to see those hotels that apply. Obviously each hotel may have more than one interest so I have a 1-many relationship with a table called Hotels_Interests.

The kicker, and you can likely already see why, is that the user does not have to fill out every search field. He may search Country&Interest, or just one or the other, or leave everything blank to see all hotels in the database. The results are simply ordered by HotelID or something like that in a form that is based off the dynamic query. The dynamic query is of course just based off the query i explained, but with criteria added in.

The problem is with the query that i am basing this search off of. Right now it has the main Hotels table as well as the 1-Many table Hotels_Interests and even another that is 1-Many Hotels_HotelTypes (say All Inclusive, Resort, etc.). So this query has various 1-Many tables as well as the main Hotels. Now, if i fill in all of those fields in the search form, there will obviously not be any duplicates returned, which is super. But if i leave Hotel_HotelTypes search field blank, i will be returned with the same hotel twice or more times, which is my problem, because i want nice search results.

I have heard of people using Union queries to get rid of duplicates but this obviously does not solve my problem as i do not want to just get rid of these entries. What i think i want is some VBA method or whatever of showing in my search results each HotelID that meets the search criteria only one time.

Right now i have it working with If statements that say if the user has left a specific search criteria blank then base the search off a different query. This is obviously crazy and is only a temp fix. Now that i want three or more 1-many tables in my query, i would be talking about if statements for like 6 or more queries, insane.

I apologize for the length of this, but i wanted to be perfectly clear. I feel like it should be not too hard, like using a record set for the form and not showing certain records or something, but i am not sure how to do it.

Thanks so much. Dillon

dillonhh · Jan 23, 2008

So I am seeing after more searching that a lot of people have posted with this same problem and not a single one has really been resolved. So i hope you all may have some ideas...

this comes close but doesnt get me there:

http://www.access-programmers.co.uk/forums/showthread.php?t=41901&highlight=hide+duplicate+records

KeithG · Jan 23, 2008

So you are saying for example if they dont add criteria for hotel type and the Hotle has more than one record in the HotelType table you will get dups correct? When you say dynamic query do you mean you create the SQL when at run time? If they don't have hoteltype criterai just don't add the HotelType table to your SQl. You can still display the thotetype n your result form with a subform. This way you will not get dup rows.

dillonhh · Jan 23, 2008

yes your first question is right..

Code:

Private Sub cmdHotels_Search_Click()
Dim db As Database
Dim QD As QueryDef
Dim where As Variant

Set db = CurrentDb()

' Delete the existing dynamic query; trap the error if the query does
' not exist.
On Error Resume Next
db.QueryDefs.Delete ("dynqryHotels_SearchResults")
DoCmd.Close acForm, "dynfrmHotels_SearchResults", acSaveNo
DoCmd.Minimize
On Error GoTo 0

' Note that there are no type-casting characters surrounding the
' following numeric fields.

where = Null
where = where & " AND [HotelID]= " + Me![HotelIDSearchText]
where = where & " AND [CountryID]= " + Me![CountryIDSearchText]
where = where & " AND [StateProvinceID]= " + Me![StateProvinceIDSearchText]
where = where & " AND [RegionID]= " + Me![RegionIDSearchText]
where = where & " AND [HotelTypeID]= " + Me![HotelTypeIDSearchText]
where = where & " AND [HotelSpotID]= " + Me![HotelSpotIDSearchText]
where = where & " AND [HotelStars]= " + Me![HotelStarsSearchText]


If Left(Me![CompanyNameSearchText], 1) = "*" Or Right(Me![CompanyNameSearchText], 1) = "*" Then
   where = where & " AND [CompanyName] like '" + Me![CompanyNameSearchText] + "'"
Else
   where = where & " AND [CompanyName] = '" + Me![CompanyNameSearchText] + "'"
End If

If Left(Me![ContactNameSearchText], 1) = "*" Or Right(Me![ContactNameSearchText], 1) = "*" Then
   where = where & " AND [ContactName] like '" + Me![ContactNameSearchText] + "'"
Else
   where = where & " AND [ContactName] = '" + Me![ContactNameSearchText] + "'"
End If


Set QD = db.CreateQueryDef("dynqryHotels_SearchResults", _
"Select * from qryHotels_Search " & (" where " + Mid(where, 6) & ";"))
DoCmd.OpenForm "dynfrmHotels_SearchResults", acFormDS
End Sub

The above is the code for the on_click for the search button. The qryHotels_Search that this is being based off of is made up of the Hotels table and then the Hotels_HotelTypes, Hotels_HotelSpots tables that make up the 1-many relationship. The code creates a new query basaed off the existing one and then simply adds the criteria that the user enters into the search field. Then I have a search results form that is based off this dynamic query, but it is a datasheet view. in the results, i really dont care about anything other than HotelID showing up, which is why i would like to get rid of the duplicate HotelID fields that appear because of the the 1-many entries.

But ya if there is a way to only select those tables that matter out of the query that would probably solve my problem. It would still be kind of ugly with If statements but maybe it would be do-able.

dillonhh · Jan 28, 2008

Problem Solved

Well, after hours of ugliness, I have figured out the problem, so if anyone is interested in the code I will post it below:

Code:

Private Sub cmdHotels_Search_Click()
Dim db As Database
Dim QD As QueryDef
Dim where As Variant
Dim fields As Variant

Set db = CurrentDb()

' Delete the existing dynamic query; trap the error if the query does
' not exist.
On Error Resume Next
db.QueryDefs.Delete ("dynqryHotels_SearchResults")
DoCmd.Close acForm, "dynfrmHotels_SearchResults", acSaveNo
DoCmd.Minimize
On Error GoTo 0

' Note that there are no type-casting characters surrounding the
' following numeric fields.

where = Null
fields = Null

where = where & " AND [HotelID]= " + Me![HotelIDSearchText]
where = where & " AND [CountryID]= " + Me![CountryIDSearchText]
where = where & " AND [StateProvinceID]= " + Me![StateProvinceIDSearchText]
where = where & " AND [RegionID]= " + Me![RegionIDSearchText]
where = where & " AND [HotelStars]= " + Me![HotelStarsSearchText]

'To search the 2 name fields
If Left(Me![CompanyNameSearchText], 1) = "*" Or Right(Me![CompanyNameSearchText], 1) = "*" Then
   where = where & " AND [CompanyName] like '" + Me![CompanyNameSearchText] + "'"
Else
   where = where & " AND [CompanyName] = '" + Me![CompanyNameSearchText] + "'"
End If

If Left(Me![ContactNameSearchText], 1) = "*" Or Right(Me![ContactNameSearchText], 1) = "*" Then
   where = where & " AND [ContactName] like '" + Me![ContactNameSearchText] + "'"
Else
   where = where & " AND [ContactName] = '" + Me![ContactNameSearchText] + "'"
End If


'Select all fields thus far from query for search
fields = "[HotelID], [CountryID], [StateProvinceID], [RegionID], [HotelStars], [CompanyName], [ContactName]"


'To select only those fields necessary to cut down the thousands of results

If Not IsNull(HotelTypeIDSearchText) Then
    where = where & " AND [HotelTypeID]= " + Me![HotelTypeIDSearchText]
    fields = fields & ", [HotelTypeID]"
End If

If Not IsNull(HotelSpotIDSearchText) Then
    where = where & " AND [HotelSpotID]= " + Me![HotelSpotIDSearchText]
    fields = fields & ", [HotelSpotID]"
End If

If Not IsNull(HotelInterestIDSearchText) Then
    where = where & " AND [HotelInterestID]= " + Me![HotelInterestIDSearchText]
    fields = fields & ", [HotelInterestID]"
End If

If Not IsNull(HotelAmenityIDSearchText) Then
    where = where & " AND [HotelAmenityID]= " + Me![HotelAmenityIDSearchText]
    fields = fields & ", [HotelAmenityID]"
End If

If Not IsNull(HotelActivityIDSearchText) Then
    where = where & " AND [HotelActivityID]= " + Me![HotelActivityIDSearchText]
    fields = fields & ", [HotelActivityID]"
End If
    
'MsgBox "Select " & fields & " from qryHotels_Search " & (" where " + Mid(where, 6)) & " group by " & fields & ";"
Set QD = db.CreateQueryDef("dynqryHotels_SearchResults", _
"Select " & fields & " from qryHotels_Search " & (" where " + Mid(where, 6)) & " group by " & fields & ";")
DoCmd.OpenForm "dynfrmHotels_SearchResults", acFormDS
End Sub

So, if the criteria that is tied to the one-many tables is filled in, a new Variant "fields" is changed to incorporate that field in to the Select and Group By sections of the SQL statement. That way, when someone does not search a field that is tied to a one-to-many table this is left out of the Group By part of the query essentially removing it all together and eliminating any possibility of duplicate field data.

Any other details are pretty straight forward to follow.

Thanks also for the tip KeithG. Hopefully some of the many unsolved threads I saw with this problem will find this code and be helped.

Dillon

Outer Join creating duplicates in dynamic query search results (1 Viewer)

dillonhh

Registered User.

dillonhh

Registered User.

KeithG

AWF VIP

dillonhh

Registered User.

dillonhh

Registered User.

Similar threads

Users who are viewing this thread