Can't sort a query field

[eye707]

I created a query to show records that has a field called 'recommendations.' I have to track the recommendations that haven't been corrected by a specific date. I've had no problem with that, since I can track them using 'is null' in the completion field. However, since more than one person in the table can have the same recommendation, I'd like to have the query only show unique recommendations that have not been completed and not show the same recommendation for each individual that may have it in his record. I can't find any criteria that will show the records that need completion unless that field (recommendations) comes up two, three or four times. I can't use distinct record in the property sheet because there will be duplicates in the other field. I just want the recommendations field to be unique.

Thanks for any help with this!

irene

[zmbd]

eye707:
We need your table name, fields, and types
[tbl_name]![field1_name] - PK, autonumber
[tbl_name]![field2_name] - FK to [tbl_name2]1:M, numeric long
etc...

We also will need to take a look at your SQL:
Open this webpage.
Click on the [CODE/] button in the format toolbar - this will insert a pair of code tags for you.
Open the query in design view. Right click in the table area and in the context menu select SQL View.
Select all of the text shown in this view.
Copy (<ctrl><c>)
Come back to this thread, place the cursor between the code tags you inserted earlier... paste (<ctrl><v>).

Also a small example of your data would be helpfull... say 5 to 10 records that represent your dataset.

[eye707]

SELECT [IRC RECCOMPLIANCE FOLLOW UP].ID
   , [IRC RECCOMPLIANCE FOLLOW UP].[C-CONSUMER TABS#]
   , [IRC RECCOMPLIANCE FOLLOW UP].[C-CONSUMER LAST NAME]
   , [IRC RECCOMPLIANCE FOLLOW UP].[C-CONSUMER FIRST NAME]
   , [IRC RECCOMPLIANCE FOLLOW UP].[C-IRC DATE]
   , [IRC RECCOMPLIANCE FOLLOW UP].[C-RECOMMENDATION]
   , [IRC RECCOMPLIANCE FOLLOW UP].[C-DUE BY]
   , [IRC RECCOMPLIANCE FOLLOW UP].[C-COMPLETE]
   , [IRC RECCOMPLIANCE FOLLOW UP].[C-PERSON RESPONSIBLE]
   , [IRC RECCOMPLIANCE FOLLOW UP].[C-COMPLIANCE CHECK DATE]
   , [IRC RECCOMPLIANCE FOLLOW UP].[C-REMARKS]
FROM [IRC RECCOMPLIANCE FOLLOW UP]
WHERE ((([IRC RECCOMPLIANCE FOLLOW UP].[C-COMPLETE]) Is Null));
 

I copied my table info into Excel because I had to remove private info. Let me know if you need to see it in another way. (I just copied the whole thing back and pasted it into an Excel sheet to see if it would go back into columns and it came out fine.) As you can see, for each consumer we have, we have to log the recommendations made at their incident review committee so that they can be used in other reports that join all the info from different tables together...

ID    C-CONSUMER TABS#    C-CONSUMER LAST NAME    C-CONSUMER FIRST NAME    C-IRC DATE    C-RECOMMENDATION    C-DUE BY    C-COMPLETE    C-PERSON RESPONSIBLE    C-COMPLIANCE CHECK DATE    C-REMARKS
1    29795    xx    Michael    2 /5 /2013    xx employment with the agency should be terminated    12/18/2012    YES    HR Department        
2    29795    xx    Michael    2 /5 /2013    During attendance, include asking the consumers if they have their identification with them.  This should occur daily so that the consuemrs learn to always have ID on their person    2 /11/2013        xx, DH Coord.        
3    29795    xx    Michael    2 /5 /2013    The coordinators will discuss and decide on the type of ID the consumers should carry, i.e., wristlets, badges, etc., home.    2 /7 /2013        DCS (FRF)        
4    15660    xx    Tyree    2 /5 /2013    xx employment with the agency should be terminated    12/18/2012    YES    HR Department        
5    15660    xx    Tyree    2 /5 /2013    During attendance, include asking the consumers if they have their identification with them.  This should occur daily so that the consuemrs learn to always have ID on their person    2 /11/2013        xx , DH Coord.        
6    15660    xx    Tyree    2 /5 /2013    The coordinators will discuss and decide on the type of ID the consumers should carry, i.e., wristlets, badges, etc., home.    2 /7 /2013        DCS (FRF)

[zmbd]

Thank you... Due to projects here in the lab, I won't have a lot of time today to oversee this; however, someone will be along shortly to help.

One thing to note: [C-CONSUMER TABS#] it is highly advisable to not use anything other than alphanumeric, the underscore, and more recently the space (although I still avoid them) in field and other names. The hash/pound charactor is a reserved token for many SQL and other programing languages and even though it is within the braces - using it within a name may result in a lot of issues.
Access 2007 reserved words and symbols:: "Access reserved symbols
The following symbols must not be used as part of a field name or as part of an object name: . / * ; : ! # & - ? " ' $ % "

[eye707]

Thanks for the hash tag tip; I'll change that...

[Rabbit]

So you don't want the results in post #3? What is it that you want to show instead? I have trouble following your first post.