Full Outer Join for Dummies

jdcfsu

New member
Local time
Today, 07:47
Joined
Mar 21, 2007
Messages
9
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?
 
Okay, maybe I misstated that a bit. Here's the info I THOUGHT I was referring to:
Microsoft Acess Help File said:
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.
 
boblarson said:
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.
 
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
 
(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
 

Attachments

  • ScreenShot _03-22-07 09;43;27_001.jpg
    ScreenShot _03-22-07 09;43;27_001.jpg
    58.6 KB · Views: 156
  • ScreenShot _3-22-07 09;43;39_002.jpg
    ScreenShot _3-22-07 09;43;39_002.jpg
    37.4 KB · Views: 152
  • ScreenShot _03-22-07 09;48;31_003.jpg
    ScreenShot _03-22-07 09;48;31_003.jpg
    26 KB · Views: 158
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?
 

Attachments

  • Picture 2.png
    Picture 2.png
    11.4 KB · Views: 149
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.
 

Users who are viewing this thread

Back
Top Bottom