Solved

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

Posted on 2014-01-22
13
308 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
 

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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now