Capturing data from two tables...

[Guitarzann]

Greetings,

Here's the hierarchy:
Table 1 - Master data table containing fields to join the other tables
together.
Tables 2 & 3 - are tables that are joined to Table 1 by a field name, "CUID."

Dilemma:
Table 2 has data that match several "CUIDs" as does Table 3. But, both
tables do not have a common relationship between themselves to produce data
results in my query. If I remove Table 2, I get data from Table 3 and vice
versa.

Question: How can I get both tables data to appear in the query without
having to remove either Table 2 or 3? A little background, I tried the "NZ"
formula and it did not work. The problem is not a null value. Instead, it
appears that Table 2 does not have the "CUID" in common with Table 3 to carry
the results to the query.

Any suggestions? I am not a SQL guru. However, if the answer can be achieve
using SQL, please provide something I may be able to copy/paste and replace
the field names to apply to my database.

Thanx!
Rick

 

[S.Clark]

You need Outer Joins.

Add T1 and T2. Link the two via CUID. Double-Click on the link's line to
get the join properties. Change to "Show all data from T1..."

Add T3. Link T1 & T3 by CUID. Double click link line and again choose
"Show all data from T1..."

Add fields to the grid for T1, T2, & T3.

 

[KARL DEWEY]

Further, if you do not wish to show T1 if there are not records in neither T2
and T3 then add this as criteria for T1.CUID --
T2.CUID Is Not Null OR T3.CUID Is Not Null

 

[Guitarzann]

Thank you both for your replies. Unfortunately, these did not fix the problem.

The easiest way I can explain it would be to imagine Table 3 had no data at
all in it. The formula in my query calls both tables to merge data from like
fields. However, when the query runs it neglects to provide the data that is
in Table 2 because there is no data in Table 3. If there is data in Table 3,
then the query runs successfully.

In MS Excel, it is easy to just build a simple if/then statement and it will
give the answer regardless whether there is data found in both locations. I
have tried both if/then and NZ statements and I just cannot find a way to get
Access to do the same.

 

[KARL DEWEY]

Just append all CUID from Table1 and Table2 first.

 

[Guitarzann]

Thanks Karl, I am taking your advice about reworking the tables and building
a little... testing a little.