Find Record Form - Include Related Records?

[misscrf]

I have the following structure:

Contact (ID, txtContact, txtNotes)

Match (ID, FKContact, txtMatch, txtNotes)

Contact Match Record (ID, FKMatch, txtsomestuff, txtNotes)

I user starts a new record by assigning it to a contact and a match. Now we have had to add 2 extra tables to the structure, so a user can be on a record and link it to other contacts and/or other matches.

Those tables are as follows:

SELECT [ID]
      ,[FKRecord]
      ,[FKContact]
  FROM [dbo].[tblMContacts_RelatedContacts]

SELECT [ID]
      ,[FKRecord]
      ,[FKMatch]
  FROM [dbo].[tblMContacts_RelatedMatches]

Now I am struggling to update the rowsource for a listbox, on a form for finding a record. When a user looks for a record, they go to a form that looks like this:

Find Record Form

This is the row source of that list box, which functions off the user choosing an option from the contact combo box, the match combo box, or both.

SELECT tblMContacts.ID, tblContact.ContactID, 
tblMatch.MatchID, tblContact.ContactID AS [Contact#], 
tblMatch.MatchID AS [Match#], tblContactType.txtContactType AS Type, 
tblMContactStatus.txtContactStatus AS Status, tblDocType.txtDocType AS [Doc Type], 
tblDMDocsMContact.dtStart AS Start, tblDMDocsMContact.dtEnd AS [End]
FROM (((((tblContact 
LEFT JOIN tblMatch ON tblContact.ID = tblMatch.FKContact) 
LEFT JOIN tblMContacts ON tblMatch.ID = tblMContacts.FKMatch) 
LEFT JOIN tblContactType ON tblMContacts.FKContactType = tblContactType.ID) 
LEFT JOIN tblDMDocsMContact ON tblMContacts.ID = tblDMDocsMContact.FKMC) 
LEFT JOIN tblMContactStatus ON tblMContacts.FKMContactStatus = tblMContactStatus.ID) 
LEFT JOIN tblDocType ON tblDMDocsMContact.FKDocType = tblDocType.ID
WHERE (((tblMContacts.ID) Is Not Null) AND ((tblContact.ContactID)=[Forms]![frmMContacts_FindContacts]![cboFilterContact])) 
OR (((tblMContacts.ID) Is Not Null) AND ((tblMatch.MatchID)=[Forms]![frmMContacts_FindContacts]![cboFilterMatch])) 
OR (((tblMContacts.ID) Is Not Null) AND ((tblContact.ContactID)=[Forms]![frmMContacts_FindContacts]![cboFilterContact]) AND ((tblMatch.MatchID)=[Forms]![frmMContacts_FindContacts]![cboFilterMatch]))
ORDER BY tblContact.ContactID, tblMatch.MatchID;

I am figuring i will need to union any records that are not directly related to a contact or match, chosen. I will need to also list records that are in either of the related tables, which match that contact or match id.

Does this make sense? I tried to give all the details, but please let me know if I'm missing something.

I would love any help with how to get records to show up for any records that are in the related tables, as well as directly linked records.

Thank you.


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[MajP]

Would be helpful if we had the real table and field names. And a description of what it holds and how it relates. Only need the primary and foreign keys. Something like this. I cannot decipher what you are saying. You say there is another 2 tables and provide a sql string. You show real names and notional names. Maybe an example of what you would enter in the combos and the different records from the different tables you would expect to be returned.

tblContact
  contactID (long primary key)
  (other fields holding information about a buisiness contact)

tblMatch
  ID (primary key autonumber)
  FKContact (A long FK, relates to contactid in tblContact)
  table describes....