SELECT DISTINCT with Memo field 1

[jflachman]

Assume Table A and Table B have the following fields:
Table A
[tt]AIndex
AName
AAddress
AMemo
etc.
[/tt]

Table B
[tt]BIndex
AIndex
BCode[/tt]

With obviously multiple entries in Table B for each entry in Table A


I have a form that displays all of the records of Table A. On the form is a subform that displays any [tt]CodeB[/tt] that appears in Table B for that record.

I want to be able to filter the main form for all records for which there exists a matching record in Table B where [tt]BCode[/tt] is equal to any of several numbers, say 100 or 101. But I only want each record to show up only once since the multiple Table B values will show up in the subform.

Normally, you would do this with a SELECT DISTINCT statement similar to the following
[tt]SELECT DISTINCT [Table A].*
FROM [Table A] LEFT JOIN [Table B]
ON [Table A].AIndex = [Table B].AIndex
WHERE (([Table B].[BCode] = 100) OR
([Table B].[BCode] = 101));[/tt]

But you can't do that if any record in Table A is of type Memo. Any other ideas on how to do that? Jonathan
________________________________________
It is not fair to ask of others what you are unwilling to do yourself.
-Eleanor Roosevelt

 

[JerryDennison]

You cannot use memo fields for aggregate functions. You can convert this field to text(note: it will truncate the field to the first 255 characters) then use this field for your aggregate. This will mean creating two queries. One to format and aggregate, then using this return only the matching records you want in another query to get the rest of the memo field.

 

[RickSpr]

You have a slight misconception. It's not the presence of a memo field in Table A that prevents DISTINCT from being used. It's the presence of a memo field in the SELECT column list. Because you're selecting [Table A].*, you're selecting the memo field along with all the others, and that's what makes DISTINCT illegal.

If you don't actually need the memo field, you can just be more specific with the column list, and your DISTINCT query will work, of course.

Otherwise, you can modify this query to select just [Table A].AIndex, and then use it as the subquery in this SQL statement:
SELECT [Table A].*
FROM [Table A]
WHERE [Table A].AIndex IN (<subquery>)
Rick Sprague

 

[jflachman]

I hadn't used the IN keyword before in SQL. That helps a lot and works perfectly, thank!
Jonathan
________________________________________
It is not fair to ask of others what you are unwilling to do yourself.
-Eleanor Roosevelt