I'm the dummy in question. I'm new to Access and have a fairly simple database that I'm using to find potential customers. I am targeting them by two criteria using two different queries (both queries are looking at different fields of the same table). I want to create a combined query that pulls ALL of the results from both of my target queries. Searching around this forum I see that I need to create an Outer Join which Access doesn't exactly do. I also see that there are ways to simulate this, but I can't seem to figure out those ways. If someone could please explain this to me in a very simple way, I would be very grateful. Thank you.

I think all you need is to create a new query based on the table, then add the other 2 queries that will link to the table on the related fields. don't join between the queries.

* set the join property to show all records from the table

Taking your idea, I created a new query with my Main Information table and my two Target queries. I created a join between the ID fields of three pieces of information. If I tell the join type to show all from the table and only matches from the queries, I get all of my records when I should only have roughly 30 (the total of both queries). If I make the join type to show all the ones from the query and just the matches from the table, I only get matches from one query, not both. If I pick the "only show matches" join I only get one record... Perhaps I'm not completely understanding your suggestion, but as of yet it doesn't quite work.

"I am targeting them by two criteria using two different queries (both queries are looking at different fields of the same table).

Is there some reason this has to be two queries? If so, are you trying to return the same data from both queries? I guess I'm not getting why you've made two queries based on the same table to pull various criteria. There are a lot of ways to do what you're after, but I would need to see an example to push it in the right direction. The easiest way would be a UNION query, but I don't know what your criteria looks like. The UNION would be:

SELECT CustName, CustDetail
FROM Table1
WHERE FirstCriteria Is True

UNION SELECT CustName, CustDetail
FROM Table1
WHERE SecondCriteria Is True
;

UNION queries require the same fields to be pulled in the SELECT portion of the SQL, though, and I can't tell if that's what you're after. (The WHERE part of the SQL can be different and involve different fields.)

Is there some reason this has to be two queries?

I have three tables, a Main Information table which is physical address/phone number/company name type stuff. I have another table which is People, all my various contacts at the different locations, and a third table which is Data which contains statistical information on the different locations over the last few years.

I have two means of targeting potential customers. the first is based on the People table and so I set up a query to look for people with a particular job title. This resulted in roughly 15 records. My other target query is based on the data, using a bit of math looking for companies in a certain range. What I want to do is combine both queries, using the ID, (a number unique to each physical location in the main table that I use as my relationship for all my other tables) along with the Main Information table so that I can produce a report that shows who all of my potential customers are and outputs phone numbers, addresses, etc.

I think what I want is the Union because in my search of this forum that seemed to be the only way to say "Take ALL records from Query 1 and ALL records from Query 2" and then ideally I'd match them up with their corresponding record in the Main Table. The problem is, I believe you gave me SQL commands but I don't have a clue how to use them or where to put them in Access.

A UNION query will ONLY work if you have the same fields in each query.

A UNION query will ONLY work if you have the same fields in each query.

Well that won't exactly do what I want it to then... Is there then some other option to get both queries so they output on the same Report?

Are there any common fields?

A UNION query will ONLY work if you have the same fields in each query.

That's not strictly true, surely?

Okay, maybe I misstated that a bit. Here's the info I THOUGHT I was referring to:

Note Each SELECT statement must return the same number of fields, in the same order. The corresponding fields are required to have compatible data types with one exception: You can use a Number and a Text field as corresponding fields.

Are there any common fields?

Yeah, the common fields is the ID field. The Main Table has the ID field (here is where it's unique), the People Table (used for Query 1) uses the ID field to connect the contacts with the company, and the Data Table (used for Query 2) uses the ID field to connect the statistics to the company.

Query 1, the People query, is just ID, Contact Name, and Job Title searched by Job Title.
Query 2, the Data query, is just ID, and about 6 statistical fields.

Okay, maybe I misstated that a bit. Here's the info I THOUGHT I was referring to:

Yes but what do Microsoft know:D

Yes but what do Microsoft know:D
Good Point! ;)

If it's a report you're trying to generate, what about using a subreport based on one query and your main report based on the other query instead of trying to join the queries?

Hook the subreport to the main report using the common ID field.

Regards
Melanie

[QUOTE=jdcfsu;576157] (both queries are looking at different fields of the same table).


I took that to mean they are all related, they don't all have to have the "SAME" fields, but you need the related ones to link to. The original table should have all the records, your queries are restricted. If you set the join type properly then all the records should be returned by the original table

I took that to mean they are all related, they don't all have to have the "SAME" fields, but you need the related ones to link to. The original table should have all the records, your queries are restricted. If you set the join type properly then all the records should be returned by the original table

I can't seem to get the join properties to work correctly then. It seems that I can only get the results from one of my queries and the table and not both of the queries and the table. Again, I'm pretty new to Access and just can't seem to figure this out.

See attached, this is an exact example. tbActivity is the original table, I created 2 other ones based on it with Min and max grouping (different results).

look at the joins, make sure they are set like I have them. I'm using Access 2000 so your join window may look slightly different, but it will do the same thing.

See how my results come up, basically if you have done all that and it still does not work then you need to compare your data to see where the problem lies. It could be you are making too many joins

I think I'm still stuck on the "what type fields can I use" problem. In your example you show that both queries are searching the same fields. Mine are not. I've included a screenshot that matches my current query settings as closely as I can to yours. This setting, however, returns all of the options in my table without any consideration to the two queries. Is it doing this because while you have two joins for each query I only have one?

it looks correct, so for the query that matches are NOT coming up for, run it by itself and then run the main table and search the main for those records. You want to confirm that they aare actually there.

The other thing to check, is the record count, if "MAIN" has 100 records, then the query should also have 100 records. Unless your other 2 queries have joins to other tables that cause the records to increase/duplicate.

"Main" is the original table and the other 2 queries are built off of it right?

"Main" is the original table and the other 2 queries are built off of it right?

Main is an original table but the two queries are built of other tables containing different information. They all have the GCFA# in common which ties everything back to the Main table which contains phone numbers, etc.

so this is what I mean, first verify that the data is in the main and also in the Queries. you can do this manually like I said, or just work with the the Main table and Only one of the queries at a time. In which case you can reverse the Join to return all of the records from the query. Then you check the Record count of the query alone against the record count of the query when it is joined.

since the queries are coming from other soures you next possibility is that there could be spaces in the fields. you can try using the Trim function in the query.

Ok, I'm still not quite getting this. I've revised my queries so that they both contain the same information as the Main Table. The process works like this: Query 1 searches a secondary table --> Query 2 combines information from Query 1 to match that of the Main Table. The result is my two "Target Full" queries which contain separate sets of records, but have all the same fields as that of the Main Table.

You can see in my new attachment that I have created the join the same as was originally proposed to me. When I set my joins like this I return everything that is in the main table with no consideration to what my queries have filtered out. When I switch the joins the other way, "Include ALL records from 'QUERY' and only those that match in 'TABLE', which is what it logically should be -- I think --, I receive this error message: "The SQL statement could not be executed because it contains ambiguous outer joins."

I don't really understand why there isn't a join option that says "Include ALL records from 'QUERY 1' and ALL records from 'QUERY 2'" because that is really all I want to do...

I'm not sure why it is not working, your screen print is the correct way to join. But I also suggest you audit the results the way I suggested. you need to work with each query and the main table to make sure your data is returning properly.

To answer your last question, you are doing exactly that with the joins as they are set. but the Trick is the Main table MUST have ALL of the GCFA# that will be found in both queries...it has to be a master list or it won't work.

Your other enemy is if there are spaces in your fields if the data comes form different sources.

You need to look closely at the queries and understand what they are returning

I might step back and take a different approach.

In your main table that holds the contact information, add a yes/no field.

Build a query to reset the yes/no field to NO.

Take your two candidate-selector queries. Instead of producing a list of candidates, UPDATE the yes/no field to YES (in the main table.)

Now just run a report on contact information where the yes/no field is YES.

You can put all of that into a macro if you like.

Alternative two:

Build another table that holds the prime key of the main table as an FK. Add the decision-making fields from BOTH of the other queries as fields in this table. Make it a one//many with the main table.

Write an ERASE query for the decision table.

Now write the queries to append records to this table. For query 1, store blanks in the fields associated with query 2. For query 2, store blanks in the fields associated with query 1. Make the report based off of a JOIN between the main table and this decision table. Given the ERASE, and two appends, you will have zero, one, or two records in the decision table. Your report can make the fields in the decision table the detail fields, then make the selected record in the main table a group-header.

Yes, this duplicates some data, but only for the purpose of building a report and only for a brief time. No long-term duplication exists.

That's at least two ways of skinning this cat. (MMMMMEEEEEOOOOOWWWWW :eek: )