Ok, I've been having problems with pulling data from two tables for quite some time. I can't seem to get it into my thick skull how to do it. I will give you as much detailed information as I possible can. I sincerely appreciate any help anyone can provide to me!
I have three tables:
I work at a small law firm and have developed a database for them to store their file information.
tblCaseList has a primary key field of "FileNumber" (since no two file numbers can be the same).
tblContacts has an autonumber primary key. This table holds contact information for various clients and company contacts.
tblFileContacts is a joining table so that I don't have to have "Joe Smith" listed in tblContacts 10 times if he's associated with 10 different files. This table has an autonumber primary key as well, along with a "FileNumber" field (linked to the primary key on tblCaseList), and a "Contact" field that links to the primary key of the tblContacts table.
If I am searching for a contact name, I have a query pulling their name and what file they're associated with. My problem is, if the contact is not associated with a file at all, nothing pulls. I need it to pull the contact, but have a blank in the "FileNumber" field of the query if there is no entry for that in tblFileContacts.
I know there is something to do with joins here, but to be honest, I don't know how to make this happen.
Again, I sincerely appreciate any help anyone can give me. I need to make sure that all data is correct before we launch this to the rest of the firm.
The only problem is, I don't want to run this every once in a while through that query. I have a form with a text box and a command button where the user enters a last name and hits the command button to run the query. The query's SQL is listed below (I desgned it in the design view, so if the SQL is the problem, maybe we can start there):
SELECT tblContacts.ContactID, tblFileContacts.[File Number], tblContacts.FirstName, tblContacts.LastName, tblFileContacts.Notes, tblContacts.WorkPhone, tblContacts.WorkExtension, tblContacts.FaxNumber, tblContacts.EmailName
FROM tblContacts INNER JOIN tblFileContacts ON tblContacts.ContactID = tblFileContacts.Contact
WHERE (((tblContacts.LastName) Like "*" & [Forms]![frmContactBy]![txtLastName] & "*"));