Solved

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

Posted on 2014-01-22
13
317 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
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…
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…

679 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