dis-allow duplicates in a subform (1 Viewer)

[maestro83]

Hi,

I have a table with thousands of patron details. In this table I have a field for 'Events'. Regular patrons obviously come to multiple events. So, this is a rough example of my table;

Name - Event
Joe - Concert
Jim - Concert
Mary - Dance
Jane - Concert
Tim - Dance
Joe - Dance
Mary - Choir
Joe - Choir

I have created a form with drop down boxes so I can select multi 'Events' and have all entries that have the selected 'Events' displayed in a sub-form. Now if I wanted to see everyone that came to the 'Dance' and 'Concert' it would display like so;

Name - Event
Jane - Concert
Joe - Concert
Joe - Dance
Jim - Concert
Mary - Dance
Tim - Dance

Now as you can see Joe appears twice in the sub-form, but I dont want duplicates. How can I hide duplicates in a given field on the sub-form only?

Cheers
Matt

 

[PNGBill]

Your subform query can group the records on name or better primary key of member.
Output would then be each member who attended shown once only. A 2nd field, if you wish could "count" the events so you have John Smith 4, fred Jones 3 etc.

Your subform uses this query for it's data.

 

[gemma-the-husky]

you have to decide what you want, as you cant have it both ways

ie you can construct the query that generates the list to show "unique values"

now, if you include the ticket type then

Joe, concert and
Joe, dance are both UNIQUE

if you dont include the ticket type then
Joe becomes unique, but now you dont know what the ticket(s) were for.

 

[PNGBill]

It should be possible to create a query that gave you a result of Joe and a text field of joined events, just like [firstname]&","&[lastname]
This may require a temptable to sort it all out or vba to run through the records until no more "Joe"

Otherwise, assuming Joe is not the key then you could select all records and group on the primary key which should give you primarykey, Joe and if req'd a count of Joe which would be the total num of events Joe attended but not the names of each event.

 

[maestro83]

you have to decide what you want, as you cant have it both ways

ie you can construct the query that generates the list to show "unique values"

now, if you include the ticket type then

Joe, concert and
Joe, dance are both UNIQUE

if you dont include the ticket type then
Joe becomes unique, but now you dont know what the ticket(s) were for.

I probably should have specified, but I dont need to see what 'events' they went to. All I want is to see how many unique people have been. I just need to see that Joe came. I just want to have 1 field in the subform display unique values. I dont care about the other fields

 

[maestro83]

Thanks for your help, I realised it was so simple just to Hide the 'Event' field on the query so that it wasn't a determining factor of a unique entry.