trying to eliminate duplicates in query results 1

[melaniews]

I have an unbound form with a button that builds a "where" clause in an Access form filter.

Here is the Button "OnClick" code

Dim strWhere As String
strWhere = " 1=1 " & BuildIn(Me.lboTInterest_ID)
DoCmd.OpenForm "frmEmailResults", acFormDS, , strWhere
DoCmd.Maximize


Here is a sample of what the code puts in the filter

1=1 AND [Interest_ID] In ('5', '1')

This works fine except that if a person has an interest in both 5 and 1, I get their name listed twice.


The form is bound to a query. Here it is....

SELECT Trim([TITLE_PREFIX]) & " " & Trim([FIRST_NAME]) & " " & Trim([MIDDLE_NAME]) & " " & Trim([LAST_NAME]) & " " & Trim([TITLE_SUFFIX]) AS Name, dbo_INDIVIDUAL.EMAIL, dbo_INDIVIDUAL.INDIVIDUAL_ID, *
FROM dbo_INDIVIDUAL INNER JOIN dbo_INTEREST ON dbo_INDIVIDUAL.INDIVIDUAL_ID = dbo_INTEREST.INDIVIDUAL_ID
WHERE (((dbo_INDIVIDUAL.EMAIL) Is Not Null))
ORDER BY dbo_INDIVIDUAL.EMAIL;


I've tried SELECT DISTINCT and SELECT DISTINCTROW in the query but it doesn't seem to help as the filter happens in the form.

I've also tried SELECT TOP 1 in the query and possibly I just didn't get the sort order correct -- though I tried it many different ways -- or perhaps it is again the problem is that the Form is where the filter actually happens.

TIA,

Melanie

 

[johndweidauer]

So there are more than one records in the Interest table for the Individual? If so, select distinct and DistinctRow will not have any affect if you use the "1=1 AND [Interest_ID] In ('5', '1')" filter, because you are allowing multiple values (Interest_ID) in a field that my be unique to the record and not unique to the Individulal_ID field.

example:
table (sample1):
UserID Name IntCode
1 John 5
2 John 3
3 Sara 1

"Select distinct * from sample1" will return all 3 records because there is one field where the values differ. however, using "select distinct userid, name from sample1" will only return two records because there are not any variances in the values of the records.

 

[melaniews]

I've tried distrinct as you suggested and am still coming up with the same thing.

Here's my example data

example:
table (sample1):
UserID Name IntCode
1 John 5
1 John 3
3 Sara 1
3 Sara 3

What I would like to see is who has IntCode 1 or 3

Right now my query would retrieve John once and Sara twice

I want to see John once and Sara once.

TIA

Melanie

 

[johndweidauer]

as long as you are telling the query to pull two values from a field where any one person can have either of the two values, then you will run into this. if you just need the names of the people and not the actual IntCode value, then exclude the IntCode from the select and keep it in your Where clause.

 

[RickSpr]

Actually, I believe SELECT DISTINCT will work, if you omit any distinguishing fields from the form's recordsource query. You have "*" in the SELECT list, which causes Interest_ID to be included, and that distinguishes two rows for the same individual if he has both Interest_ID values. It's likely there are other distinguishing fields included by "*", as well.

If you replace the "*" with just the fields you are actually displaying on the form, it should work--unless, in fact, you have distinguishing fields on the form. If the latter is true, then what you're asking for is illogical, because you want the individual to appear only once in the filtered recordset, but you have multiple values to display in the control.

If you have distinguishing fields on the form, and you don't care which value appears (an idea that's supported by your attempt to use TOP(1)), let me suggest a little different approach. Instead of using the Filter property, let your filtering logic build a complete SQL statement and assign it to the RecordSource property. The SQL statement must include the DISTINCT keyword and omit Interest_ID and any other distinguishing fields. I'd suggest that, while this pseudo-filter is active, you hide any controls that show distinguishing fields. When the "filter" is removed, simply reveal the hidden controls and reset the RecordSource property to the original query. (Downside: The toolbar buttons won't reflect that a filter is applied; you can correct that by setting the Filter property to True.)

BTW, using "1=1" in your filter criteria as a way to ensure you don't get an empty WHERE clause is clever. I've run into that problem and solved it by using conditional logic. Your solution is simpler. I'm giving you a star for that.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[johndweidauer]

Here is her query:
SELECT Trim([TITLE_PREFIX]) & " " & Trim([FIRST_NAME]) & " " & Trim([MIDDLE_NAME]) & " " & Trim([LAST_NAME]) & " " & Trim([TITLE_SUFFIX]) AS Name, dbo_INDIVIDUAL.EMAIL, dbo_INDIVIDUAL.INDIVIDUAL_ID, *
FROM dbo_INDIVIDUAL INNER JOIN dbo_INTEREST ON dbo_INDIVIDUAL.INDIVIDUAL_ID = dbo_INTEREST.INDIVIDUAL_ID
WHERE (((dbo_INDIVIDUAL.EMAIL) Is Not Null))
ORDER BY dbo_INDIVIDUAL.EMAIL;

I did not notice the * in the query, should work, yes

 

[melaniews]

I see your point Rick that I am being illogical in what I am asking. I believe I am leaving out a step. I need to capture the subset (which will have multiple lines because I am using Interest_Id as criteria). Then I need to run another query that will select unique name and email from that subset.

I'm not sure how I would accomplish this but do you think I am on the right track?

TIA

Melanie

 

[johndweidauer]

change this:

SELECT Trim([TITLE_PREFIX]) & " " & Trim([FIRST_NAME]) & " " & Trim([MIDDLE_NAME]) & " " & Trim([LAST_NAME]) & " " & Trim([TITLE_SUFFIX]) AS Name, dbo_INDIVIDUAL.EMAIL, dbo_INDIVIDUAL.INDIVIDUAL_ID, *
FROM dbo_INDIVIDUAL INNER JOIN dbo_INTEREST ON dbo_INDIVIDUAL.INDIVIDUAL_ID = dbo_INTEREST.INDIVIDUAL_ID
WHERE (((dbo_INDIVIDUAL.EMAIL) Is Not Null))
ORDER BY dbo_INDIVIDUAL.EMAIL;

To this:

SELECT Disitinct Trim([TITLE_PREFIX]) & " " & Trim([FIRST_NAME]) & " " & Trim([MIDDLE_NAME]) & " " & Trim([LAST_NAME]) & " " & Trim([TITLE_SUFFIX]) AS Name, *
FROM INDIVIDUAL WHERE INDIVIDUAL.EMAIL Is Not Null and Individual_ID in (select distinct Individual_ID from Interest)
ORDER BY dbo_INDIVIDUAL.EMAIL;

I think this will work, being without the data, I believe the syntax is correct.

 

[RickSpr]

Melanie,

I said that what you're asking for is illogical only if you are trying to combine the records, but have a distinguishing field on the form. By a "distinguishing field" I mean one which could have different values in the rows you want to combine. It isn't necessarily true that you have this, and you haven't said one way or the other.

It may be that all you have to do is add the DISTINCT keyword to the query (or, equivalently, set the query's Unique Records property to Yes), and remove the ", *" from the SELECT column list. Go ahead and try that first. I'm sure that will limit the number of times the individual appears to just once.

If some of your controls them come up showing "#Name?" or are blank when they shouldn't be, you have three choices:
1. If the control's bound field (the field whose name is in the ControlSource property) is in the dbo_Individual table, add the field name to the end of the SELECT list. Otherwise, either:
2. Delete these controls from the form, or
3. Add code to rebuild the form's entire RecordSource property, rather than just a WHERE clause, when the button is clicked. This code should also hide the controls bound to fields in dbo_Interest. When you want to remove the filter (another button, perhaps), restore the original RecordSource property and reveal the hidden controls.

John,

Good try, but I don't think that technique will work. The outer query is only selecting from dbo_Individual, so Melanie won't be able to add a filter restricting on dbo_Interest.InterestID.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[johndweidauer]

Can you explain why it won't work, I mean, I think that is why this thread was started because something didn't work. You basically touched over the issues discussed in earlier posts. I think its obvious, first the query, then the form/report.

I tell it to select distinct values based on values on the "Select In", which will not visually return the records, but will provide the parent query with the data in which to select distinctly from, thus returning distinct values. I may be wrong here, but i think you sounded a little bit rude with your take over tactics.

The issues with the form and all the other junk is all secondary to the query return distinct values...all of which i have been covered in earlier posts. In addition, Not knowing exactly what is being done with the data, the structure, etc., it does constitute a little confusion when trying to establish a solution.

 

[RickSpr]

John,

I'm sorry if I sounded rude. I certainly didn't intend to. On rereading, I see that my first post reiterated what you were saying; perhaps you felt that was rude. I confess that I misunderstood you. I thought, based on the forceful first statement in your preceding post, that you were saying DISTINCT could not be made to work.

"Takeover tactics?" Do you feel you have some sort of proprietary right in a thread based on being the first to reply? That's not how Tek-Tips works. Alternative solutions are very much encouraged.

As to why I don't think your latest query will work:
Ultimately, Melanie wants to restrict on the basis of dbo_Interest.InterestID. In order to do that, dbo_Interest must be present in the FROM clause of the main query. Its presence in the subquery isn't sufficient; the only column returned from the subquery to the main query is Individual_ID, and in fact it's impossible to return an additional column when using an IN predicate. So I believe a join is necessary in the main query. The problem then becomes getting DISTINCT to eliminate the duplicates, which requires omitting distinguishing fields from the SELECT column list (a fact which you obviously recognize).

My additional comments about the form are meant to suggest a more complete solution, in the event that Melanie has included some of the omitted fields on the form.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein