Queries that pull from 2 tables

[CFP - Jason]

i have a customer database and there are two types of sales leads. either its a new lead, or an exsisting customer with a new lead for an additional service. i have a main table that hold the basic info for both types. but, when its an exsisting customer, the info is in a different table that is kept with the other active services. Is there a way that i can check the main table for the new leads and then check the other table to see if there are any customers with leads. But i need the query to gather the data associated with the main table for the customers that is in the other table. i hope that isnt too confusing. basically i want to have a query that takes the same customer info for both, but checks the conditons or criteria from two different places and appends them in the same table.
Thanks a bunch for whoever tried to understand that
Jason

 

[jeremie_ingram]

You can acheive this, if I am not too confused you could do it through queries if there is an established relationship between the tbls. Since these are essientially the same data, establishing that link (if it doesnt already esist) should be simple.
You could use a UNION query to combine the two data sources, then use criteria to filter it to new and existing.
A little more description on the tbl structure and thier relationships would help in giving more detailed assistance, and also include any unique identifiers (to differentiate the existing from the new).

The simplest way to create a union query is to start creating a query in design view and add the first tbl only. Add all of the fields you need, then switch to SQL view. Copy the SQL, then close the query without saving it. Create a new query, add the second tbl and all the needed fields. Switch to SQL, add UNION ALL to the end of the statement then paste the first SQL string in behind it. Now you have a UNION query.

 

[CFP - Jason]

awesome. the data that i want to be in the query is all from the same table. the criteria for filtering is in two. does this still work the same way? all the main info is in the same table, but the status is in two different places. like the check box for if its a new lead is in the main one. but the other criteria is a value from another table with the value of "Lead". I know how to do it separatly but, is there a way to query for all the check boxed ones and the "Lead" from the other table and have it in the same query? but the info that is in query is all from the same place. Oh btw the main and the other table are related by customer ID. I hope this is more clear.
Thanks
Jason

 

[jeremie_ingram]

To "is there a way to query for all the check boxed ones and the "Lead" from the other table and have it in the same query? " all you have to do is cerate the 2 tbl Query. Put 1 in the criteria for the check box field and add the LEAD field from the second table.

So, add all neccessary fields to tbl1. Add the LEAD from tbl 2. In the criteria area for the checkbox field place a number 1. Since the tbls have an existing relationship, it should return what you want.