Solved

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

Posted on 2014-01-22
13
310 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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

948 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

20 Experts available now in Live!

Get 1:1 Help Now