Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-22-2008, 02:45 PM   #1
dillonhh
Registered User
 
Join Date: Nov 2007
Posts: 38
Thanks: 0
Thanked 0 Times in 0 Posts
dillonhh is on a distinguished road
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


Last edited by dillonhh; 01-23-2008 at 07:14 AM.
dillonhh is offline   Reply With Quote
Old 01-23-2008, 06:39 AM   #2
dillonhh
Registered User
 
Join Date: Nov 2007
Posts: 38
Thanks: 0
Thanked 0 Times in 0 Posts
dillonhh is on a distinguished road
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/...licate+records

Last edited by dillonhh; 01-23-2008 at 07:13 AM.
dillonhh is offline   Reply With Quote
Old 01-23-2008, 07:23 AM   #3
KeithG
AWF VIP
 
KeithG's Avatar
 
Join Date: Mar 2006
Location: Illinois
Posts: 2,592
Thanks: 0
Thanked 4 Times in 4 Posts
KeithG will become famous soon enough KeithG will become famous soon enough
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.

__________________
If my post has helped you solve your problems please add to my reputation. Click the scale in the top right side of this post.
KeithG is offline   Reply With Quote
Old 01-23-2008, 07:33 AM   #4
dillonhh
Registered User
 
Join Date: Nov 2007
Posts: 38
Thanks: 0
Thanked 0 Times in 0 Posts
dillonhh is on a distinguished road
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.

Last edited by dillonhh; 01-23-2008 at 07:38 AM.
dillonhh is offline   Reply With Quote
Old 01-28-2008, 12:51 PM   #5
dillonhh
Registered User
 
Join Date: Nov 2007
Posts: 38
Thanks: 0
Thanked 0 Times in 0 Posts
dillonhh is on a distinguished road
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

dillonhh is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
onClick Event - Search Using Dynamic Query dillonhh Modules & VBA 7 12-13-2007 09:31 AM
way too many tables qwertyjjj Theory and practice of database design 3 08-01-2007 01:04 PM
Join Query Unpredictable. Mod Queries 0 08-12-2004 08:44 AM
Crosstable query returns too many records Palsam Queries 2 03-09-2004 11:41 AM
search query null results Matt Brown Queries 2 04-02-2003 12:55 AM




All times are GMT -8. The time now is 04:40 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World