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.
Full Outer Join for Dummies
- Thread starter jdcfsu
- Start date
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
* 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.
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.)
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.
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?
R
Rich
Guest
That's not strictly true, surely?
boblarson said:
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.
R
Rich
Guest
boblarson
Smeghead
- Local time
- Today, 04:31
- Joined
- Jan 12, 2001
- Messages
- 32,059
Good Point!Yes but what do Microsoft know
melanie
A Walking Contradiction
- Local time
- Today, 14:31
- Joined
- Jun 6, 2003
- Messages
- 101
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
Hook the subreport to the main report using the common ID field.
Regards
Melanie
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
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
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
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?
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 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.
Similar threads
