help designing search results form

[alfonsomozo]

I have two tables. One is a list of contacts named "Contacts" and the other is a record of the seminars attended by each contact "Seminars" which is linked to by the ContactID of the Contacts table. So there is one record for each contact but each contact may attend a number of different seminars. The seminars table has a SeminarID, ContactID, Seminar, Seminar Date, Notes.
I have search functionality which shows the results of the search in a continuous form showing each field of the contacts table and the seminars table. This is all working as i would like unless a Contact has attended more than one seminar.
If a contact has attended more than one seminar then there will be a record returned for each seminar. One With all the contact details and then the Seminar details of one seminar and then another with the same contact details except with the details of a different seminar.
Is there anyway in which i can have the search results only show the contact details once but then have all the seminar details for that one contactID. Kind of like as shown below.

ContactID FirstName LastName Email Seminar1 Seminar1date
Seminar2 Seminar2date
SeminarN SeminarNdate

The data is also going to be exported as an rtf for use in a mail merge so it is important not to duplicate contact details.

Here is the SQL for my search query

SELECT Contacts.ContactID, Contacts.SalutationID, Contacts.FirstName, Contacts.LastName, Contacts.Address1, Contacts.Address2, Contacts.City, Contacts.StateID, Contacts.PostalCode, Contacts.CompanyName, Contacts.Position, Contacts.WorkPhone, Contacts.WorkExtension, Contacts.MobilePhone, Contacts.FaxNumber, Contacts.EmailName, Contacts.ContactTypeID, Contacts.Notes, Contacts.[Authorised By], Contacts.[Authorised Date], Seminars.Seminar, Seminars.[Seminar Date], Seminars.Notes
FROM Contacts LEFT JOIN Seminars ON Contacts.ContactID = Seminars.ContactID
WHERE (Contacts.SalutationID Like '*' & [Forms]![Search]![Salutation] & '*' OR [Forms]![Search]![Salutation] Is Null)
  AND (Contacts.FirstName Like '*' & [Forms]![Search]![FirstName] & '*' OR [Forms]![Search]![FirstName] Is Null)
  AND (Contacts.LastName Like '*' & [Forms]![Search]![LastName] & '*' OR [Forms]![Search]![LastName] Is Null)
  AND (Contacts.Address1 Like '*' & [Forms]![Search]![Address] & '*' OR [Forms]![Search]![Address] Is Null)
  AND (Contacts.Address2 Like '*' & [Forms]![Search]![Address2] & '*' OR [Forms]![Search]![Address2] Is Null)
  AND (Contacts.City Like '*' & [Forms]![Search]![City] & '*' OR [Forms]![Search]![City] Is Null)
  AND (Contacts.StateID Like '*' & [Forms]![Search]![State] & '*' OR [Forms]![Search]![State] Is Null)
  AND (Contacts.PostalCode Like '*' & [Forms]![Search]![PostalCode] & '*' OR [Forms]![Search]![PostalCode] Is Null)
  AND (Contacts.CompanyName Like '*' & [Forms]![Search]![CompanyName] & '*' OR [Forms]![Search]![CompanyName] Is Null)
  AND (Contacts.Position Like '*' & [Forms]![Search]![Title] & '*' OR [Forms]![Search]![Title] Is Null)
  AND (Contacts.WorkPhone Like '*' & [Forms]![Search]![WorkPhone] & '*' OR [Forms]![Search]![WorkPhone] Is Null)
  AND (Contacts.WorkExtension Like '*' & [Forms]![Search]![WorkExtension] & '*' OR [Forms]![Search]![WorkExtension] Is Null)
  AND (Contacts.MobilePhone Like '*' & [Forms]![Search]![MobilePhone] & '*' OR [Forms]![Search]![MobilePhone] Is Null)
  AND (Contacts.FaxNumber Like '*' & [Forms]![Search]![FaxNumber] & '*' OR [Forms]![Search]![FaxNumber] Is Null)
  AND (Contacts.EmailName Like '*' & [Forms]![Search]![EmailName] & '*' OR [Forms]![Search]![EmailName] Is Null)
  AND (Contacts.ContactTypeID Like '*' & [Forms]![Search]![ContactTypeID] & '*' OR [Forms]![Search]![ContactTypeID] Is Null)
  AND (Contacts.Notes Like '*' & [Forms]![Search]![Notes] & '*' OR [Forms]![Search]![Notes] Is Null)
  AND (Contacts.[Authorised By] Like '*' & [Forms]![Search]![Authoriser] & '*' OR [Forms]![Search]![Authoriser] Is Null)
  AND (Contacts.[Authorised Date] Like '*' & [Forms]![Search]![Authorised Date] & '*' OR [Forms]![Search]![Authorised Date] Is Null)
  AND (Seminars.Seminar Like '*' & [Forms]![Search]![seminar] & '*' OR [Forms]![Search]![seminar] Is Null)
  AND (Seminars.[Seminar Date] Like '*' & [Forms]![Search]![semdate] & '*' OR [Forms]![Search]![semdate] Is Null)
  AND (Seminars.Notes Like '*' & [Forms]![Search]![semnotes] & '*' OR [Forms]![Search]![semnotes] Is Null);

thanks for looking.

 

[MajP]

In a report Sorting and grouping would take care of this.
Or you could make a subReport

In a form view you could do a main form and a subform. If you want to just show for a single contact

ContactID FirstName LastName Email
Seminar1 Seminar1date
Seminar2 Seminar2date
SeminarN SeminarNdate

You could use a datasheet and a subdatasheet if you want to

ContactID1 FirstName LastName Email
Seminar1 Seminar1date
Seminar2 Seminar2date
SeminarN SeminarNdate
ContactID2 FirstName LastName Email
Seminar1 Seminar1date
Seminar2 Seminar2date
SeminarN SeminarNdate
ContactID3 FirstName LastName Email
Seminar1 Seminar1date
Seminar2 Seminar2date
SeminarN SeminarNdate

You could use nested subforms in single sheet view
Or you could use linked subforms to have multiple continous form views.

What choice would you like?

 

[hneal98]

You could just create a main form that has the contacts and then a subform that shows the Seminars. Set up the subform so it is in Datasheet view. Then if the user selects a particular seminar from there, then a detail form can pop up showing the Seminar detail for that contact.

I think this would save you a lot of headaches.

 

[alfonsomozo]

Thanks for your great input. I created a subform based on my contacts query and used it to show the Seminar fields in datasheet view. This was all done using the wizard relating the subform to the main form via the contactID.
I tested it out with some searches and it shows up the seminar details properly.
I have a minor problems with this that i'm hoping can be easily fixed.
The search still returns one result for each seminar. If i search for a particular person that has attended two seminars i will get 2 results. Both look exactly the same and both show both seminars in the subform. The only thing that i noticed was that in the subform the first item is selected both times, i thought that maybe for the second record the second seminar would be selected.

Is there an easy way to stop the search returning these duplicates. As stated earlier the contact details are going to be used for a mailmerge but not the seminar details. If i export the query in its current state there is an entry for each seminar so if letters were to be sent out some people could be getting more than is needed.

Also with a subform it wont let me set my main form in continuous view. Is there anyway around this because it is imperative that the main form is of continuous style.

I'm thinking this linked subforms to have multiple continous form views that you suggested MajP would work really well and am intrigued to see how it works. If anyone has done something similar before it would be great to see some examples that i could adapt to my application.
thanks for everything.

 

[MajP]

Look at thread702-1207087. AceMan describes one way to do continous synched subforms, I describe another.
For the records that are similar, you may be able to do a DISTINCT predicate query. Look in the help file under DISTINCT, DISTINCTROW, and Unique Values.
Unfortunately, I think you will have a problem because I do not think that your database is normalized. You actually need a third table because you have a Many to Many relationship. Each Contact could attend many seminars, and every Seminar has many contacts. Your tables should be like this.

TblContacts
ContactID
other contact information

TblSeminar
SeminarID
other Seminar information

joinTblContacts_Seminar
ContactID
SeminarID

All information about a contact is entered only once. All information about a seminar is entered once and only once. The join table links the two. So if person 2 attended seminar 4,7,and 8 the data looks like this

ContactID SeminarID
2 4
2 7
2 8

Because of your poor design you have artificially duplicate records.

 

[alfonsomozo]

Ok i have made some changes and have made a bit of progress. I created a new form and placed the "contacts query" and "seminars subform" as subforms. They have been linked together and it is working like it was before except now the contacts form is continuous.

I understand what you mean about the current many-to-many relationship. I have created a new table with two fields ContactID and SeminarID and have linked them with their respective tables. I'm a little stuck as to what i'm supposed to do now.