Solved

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

Posted on 2014-01-22
13
319 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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 500 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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Suggested Solutions

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

710 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