Filtering Multiple Entries

[cpc34]

Is there a command that will allow me to bring up a query that shows only one occurrence of a name? The form it is looking at has the same names occurring repeatedly. Also, is there a way of getting the same query to count the number of occurrences, so that it sas something like: Person A, 5; Person B, 2; Person C, 1 etc?
This is an attempted fix to get around a many-to-many problem I am having with a subform.

 

[BuilderSpec]

Hi

Not quite sure what you;re trying to end up with but here goes...


On your form you could look at adding a 2 column combo box with an sql clause like

select distinct(Name),Count(*) from Table order by Name

this should give you each unique Name and number of times it is found in the table.

Then in the AfterUpdate event on the combo box add the lines

Me.Form.FilterOn = False
Me.Form.Filter = "[Name]='" & Combo1 & "'"
Me.Form.FilterOn = True
Me.Form.Requery

this should change the form so that after you choose a unique name you can now scroll through each entry with that unique name.

Sure you could take this principle and apply it to the sub form..

Regards

BuilderSpec

 

[PHV]

Use an aggregate query:
SELECT Person, Count(*) As CountOfDup FROM yourTable GROUP BY Person;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886