Searching mutiple tables

[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.

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

[pbaldy]

I suspect you're setting up the db wrong. Why are there 4 tables for calls? I would expect 1 table, with perhaps a field to designate call type (or whatever the 4 tables represent).

[mbolster]

There are 4 tables for calls because the calls are in order. The first call call1, second call2, etc. They are kind of distinct I guess. I believe this is what was asked, but I'm certainly no expert, and if a single table with all of the calls would be better for this, including a number for which call it is then I'm willing to chance it if it helps with this search problem.

[mbolster]

This SQL statement is sort of what I want

SELECT * FROM contacts WHERE Form_contactsForm.Call1.Form.....Value = ?

What should I put at ? if I want to get the results from a different table? The tables are linked by ContactID, so I figure there must be a way to do it...frustrating.

[Access_Blaster]

I figure there must be a way to do it...frustrating.

The reason for the fustration is.

pbaldy wrote:

I suspect you're setting up the db wrong. Why are there 4 tables for calls? I would expect 1 table, with perhaps a field to designate call type (or whatever the 4 tables represent).

[pbaldy]

I definitely think you'd be better off with 1 table, with an additional field to signify which call it is (that assumes the 4 tables have the same fields). That will let you easily get a particular call number, all calls for a particular client, etc, just by adjusting query criteria.

[mbolster]

Ok, so assuming I have just one table for the calls. I need to find all of the calls done on a specific date, and then display a form that has all of the clients with calls that meet that criteria. I also need to display all of the other calls for that user. Is there a way to do this?

[pbaldy]

It should all be doable. You can base a form on a query with specific parameters, you can use the master/child links of a form/subform, etc.

[mbolster]

I finally found what I was looking for. Maybe I wasn't explaining it well enough. If any one is interested here is a link to the solution. Thanks for the help though

http://allenbrowne.com/ser-28.html