Finding unique records

[NWGal]

I have a new table/form/query set up for tracking initial contact made with people interested in our organization. The table is called tblUnique, and holds the name, date of initial contact etc, there may and probably will be more than one record for an individual based on the nature of our work, sometimes a person is contacted by more than once but we have to count each person only once. I need to query and look for the uniques, but I'm not sure / can't remember how to set this up.
Example, Fred Flinstone may be contacted on two different dates, but I only want to count him once.

[HansV]

Open the query in design view.
If necessary, remove all fields that could have different values for the same contact, such as the contact date field.
Click in an empty part of the upper part of the query design window.
If you don't see the Property sheet, press F4 to display it.
Set the Unique Values property of the query to Yes.
Save the query, and use it as Record Source for the form.
Remarks:
1) If the form contains controls bound to fields that you removed from the query, you'll have to remove those controls.
2) The query, and hence the form, will not be editeable. A query that groups records is always read-only.

(It would be better to have a table in which each contact occurs only once, and another table listing the contact ID plus fields that might occur more than once for the same contact. You could then use the first table as record source for your form.)

[NWGal]

Open the query in design view.
If necessary, remove all fields that could have different values for the same contact, such as the contact date field.
Click in an empty part of the upper part of the query design window.
If you don't see the Property sheet, press F4 to display it.
Set the Unique Values property of the query to Yes.
Save the query, and use it as Record Source for the form.
Remarks:
1) If the form contains controls bound to fields that you removed from the query, you'll have to remove those controls.
2) The query, and hence the form, will not be editeable. A query that groups records is always read-only.

(It would be better to have a table in which each contact occurs only once, and another table listing the contact ID plus fields that might occur more than once for the same contact. You could then use the first table as record source for your form.)

I'm not quite getting this, sorry. I don't want to use this query as the record source for the form. The form is where I'm entering my information. I don't need to keep any personal info on the people, just the date of the contact, who contacted them etc. So although I would absolutely normally do have a table where the unique id was the person, in this case it makes more sense to have the unique record I'm tracking be the contact event itself, not the specific person and if a contact event happens to the same person, I need to see and count just the one event. Does that make more sense?

[HansV]

I don't understand what you want.

[NWGal]

I don't understand what you want.

I'm sorry - finally getting back to this. What I want to do is this. Between the two locations of our organization, we, the staff and volunteers make many 1st contacts with potential "clients". To make our funders happy we need to track these contacts but can only count each person once, i.e as a unique, the problem is that sometimes more than one of us makes a "first contact" thus we end up with duplicates. We don't need or want to add these folks as contacts, we only need their name and the date when contacted and who contacted them, then to be able to sift out and total the "uniques", i.e one person no matter how many times contacted. I have roughed out a form for entering this info into a table, but I am unsure how best to query for the total number of uniques. I am assuming it has something to do with looking at the record set, finding all the duplicates by name, and counting each of those only once. Does that help?

[HansV]

I think the idea is basically what I described in my first reply, except that you won't use it as record source for a form:
- On the Create tab of the ribbon, click Query Design.
- Add tblUnique, then close the Show Table dialog.
- Add only those fields to the query grid that determine the person, such as last name and first name.
- Do *not* include any fields that might have different values for the same person, such as the date of the contact.
- Click in an empty part of the upper pane of the query design window.
- If you don't see the Property Sheet, press F4.
- Set the 'Unique Values' property to Yes. This will suppress duplicates.

S0619.png

- Save this query.

- If you want a count of unique persons, create a new query.
- Activate the Queries tab of the Show Table dialog.
- Add the above query, then close the dialog.
- Add the (first or last) name field from the field list to the query grid.
- Click to highlight the Totals button.
- Set the Total: option for the single column in your query to Count.

[NWGal]

I think the idea is basically what I described in my first reply, except that you won't use it as record source for a form:
- On the Create tab of the ribbon, click Query Design.
- Add tblUnique, then close the Show Table dialog.
- Add only those fields to the query grid that determine the person, such as last name and first name.
- Do *not* include any fields that might have different values for the same person, such as the date of the contact.
- Click in an empty part of the upper pane of the query design window.
- If you don't see the Property Sheet, press F4.
- Set the 'Unique Values' property to Yes. This will suppress duplicates.

S0619.png

- Save this query.

- If you want a count of unique persons, create a new query.
- Activate the Queries tab of the Show Table dialog.
- Add the above query, then close the dialog.
- Add the (first or last) name field from the field list to the query grid.
- Click to highlight the Totals button.
- Set the Total: option for the single column in your query to Count.

Sweet!!! That was the solution. Thank you ever so much. :)

[NWGal]

Opp's one more question - where do I set the total? In the query?

[HansV]

Here's a screenshot of what the first query might look like in design view:

S0620.png

This query returns the list of unique names. Note that neither the ContactID field (the primary key of the table), nor the ContactDate field have been added to the query grid.

And here is the query that will return the count of unique names:

S0621.png

Note that the Totals button is highlighted, and that Count has been specified in the Total: row of the query grid.

Another way to obtain the count would be the expression

=DCount("*", "qryUnique")

[NWGal]

Thank you again! I got this working fine.