Advanced Searching

[mbolster]

Hi, I'm having a problem searching certain criteria and displaying it in my form.

My database is set up as having a table of contacts, with a contactID. There are also 4 tables of calls (call1, call2...), each linked to contacts with contactID.
My form displays the contact information, as well as 4 subforms for the calls.

I set it up so that the contactID for the calls would change when the contactID for the main form changed, so that the call information for that contact would show the right contact's calls.

I also have a search that searches the information of the calls, but obviously displays all of the calls and the contact information for the records meeting that criteria.

For example, I would want to find all of the contacts with their first call (Call 1) having an outcome of Left Message. I know how to filter the subform to do this, but how can I get the contacts with a Call1 result of Left Message.



I'm thinking there must be a way to get a list with the ContactID's and then somehow make the recordsource of the main form that list. I don't really know how to do this though.

I'm pretty new to Access, so can any one please help me out and give me an idea of how to do this? Thanks a bunch!

[jzwp11]

Why would you have separate tables for each call? All of the calls should be in 1 table with a field that distinguishes the call number. This would help simplify your searching.

[mbolster]

The reason for this is because I was asked to do it this way. But, even if I had the one table, what would the solution be?

I think that displaying the 4 calls for each contact would be slightly more difficult if I put all the calls in one table.

Thanks

[jzwp11]

If you want to find all contacts which had a call #1 where the result was "left message", you need a query

Now if I was doing this, I would have the 1 call table as I mentioned, so my basic table structure would look like this

tblContacts
-pkContactID primary key, autonumber
-txtFName
-txtLName

tblContactCalls
-pkContactCallID primary key, autonumber
-fkContactID foreign key to tblContacts
-longCallNo (call number)
-dteCall (date of call)
-txtResult

The query would look like this (SQL view):

SELECT tblContacts.txtFName, tblContacts.txtLName..other fields
FROM tblContacts inner join tblContactCalls on tblContacts.pkContactID=tblContactCalls.fkContactI D
WHERE longCallNo=1 and txtresult="Left Message"

If I was actually designing a database for a call log, I would drop the call number in favor of recording the date for each call. I can use a query to find the most recent call based on the date field and see if it's result was "Left Message"

The above table structure will allow you to see the complete call history in one subform or you can still split it into 4 but what happens if you have 10 calls with a client? You would have to constantly add subforms to your main form.