?
Solved

How can I "include" all records in three tables in Access 2002.

Posted on 2014-01-22
13
Medium Priority
?
336 Views
Last Modified: 2014-02-10
I have an Access 2002 database.
In the database contains 3 tables:  CE, CEM and SUPP

In my query, I want all records from all 3 tables (even if there are no matching record keys) so that then end result would include all records.

See query here:

CE - CEM - SUPP Query
See SQL view here:

SQL-VIEW.txt

See CE-CEM join properties here:

CE-CEM Join Properties
See CEM-SUPP join properties here:

CEM-SUPP Join Properties
The results of my current query only includes records that are equal in the CE Table & CEM Table & SUPP Table.  

I would like the query to produce all records from the CE table, all records from the CEM table and all records from the SUPP table.

Thank you for your help!
0
Comment
Question by:LessonsLearned
  • 8
  • 5
13 Comments
 

Author Comment

by:LessonsLearned
ID: 39801593
I think I need a "UNION ALL" somewhere, but I am not familiar with SQL language.
0
 
LVL 3

Expert Comment

by:0Doc
ID: 39801642
Is there a field that is common to all three tables?
0
 

Author Comment

by:LessonsLearned
ID: 39801654
The CENO field is common in all three tables.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:LessonsLearned
ID: 39801668
The CE table contains the primary key CENO (indexed with no duplicates)
The CEM table contains a CENO field (indexed, duplicates ok)
The SUPP table contains a CENO field (indexed, duplicates ok)

Note:  I do not have a "join" between the CEM table CENO field, and the SUPP table CENO field.
0
 
LVL 3

Expert Comment

by:0Doc
ID: 39801687
It looks like the CE table is the master, with a record for each CE.  Is that the case?
0
 
LVL 3

Expert Comment

by:0Doc
ID: 39801698
The UNION ALL  command will basically dovetail the contents of the three tables into the query results without any relationships.  If you have 5 CE records, 4 CEMs and 3 SUPPs, you will end up with 12 records in the end.  It's not useful for most functions.  But, I can help you get started on one if that is what you need.
0
 

Author Comment

by:LessonsLearned
ID: 39801701
Yes, that is correct.  

All records in the CE table should point to a record in the CEM table using the CENO field.  

All records in the SUPP table should point to a record in the CEM table using the CENO field.

There is no referential integrity.
0
 

Author Comment

by:LessonsLearned
ID: 39801707
I am headed home from work, but will check back later.
0
 
LVL 3

Expert Comment

by:0Doc
ID: 39801713
What you can do then is join the CEM and SUPP tables to the CE table using the CENO field and Include everything in the CE table.  That should give you everything.  Give that a try.  However, it may give you some duplicate results. If that is a problem, I can help with that.
0
 

Author Comment

by:LessonsLearned
ID: 39803796
The CEM is already joined to the CE table using the CENO field.

I added a join between the Supp table and the CE table using the CENO field.

The results did not change.
0
 

Author Comment

by:LessonsLearned
ID: 39803882
Is it possible to do 2 separate queries and merge the results?  
For example:  
Query 1:  All records in the CEM table and CE table where the CENO are equal.
Query 2:  All records in the SUPP table and CE table where the CENO are equal.

Then merge the results of query 1 and query 2 (sorted by CENO)
0
 
LVL 3

Accepted Solution

by:
0Doc earned 2000 total points
ID: 39804062
Make sure you remove the join between CE and CEM

You could use a union query to merge the two queries.
0
 

Author Closing Comment

by:LessonsLearned
ID: 39848889
Thank you!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question