AnsweredAssumed Answered

Trouble with JOIN

Question asked by KingsPawn on Apr 9, 2009
Latest reply on Apr 10, 2009 by philmodjunk

Title

Trouble with JOIN & FIND Does not give me ALL of the records I think it should

Post

Newbie

FM9 Pro Advanced

Microsoft XP

  

I have two tables:

 

Table1:

FIELD-A (RangeName)  

There may be many of these each with a unique value.

FIELD-B (ShootName)  

This field will appear many times for each occurrence of FIELD-A.

FIELD-C (Number)        

This field will appear many times for each occurrence of FIELD-B.

FIELD-X(TeamName)

This field will appear many times for each occurrence of FIELD-C.

 
 

Table2:

FIELD-A (RangeName)  

There may be many of these each with a unique value.

FIELD-B (ShootName)  

This field will appear many times for each occurrence of FIELD-A.

FIELD-D (Gun)  

There will be four occurrences of this field for each occurrence of FIELD-B.

FIELD-E (Yardage)            Containing either “100” or “200”

There will be two occurrences of this field for each occurrence of FIELD-D.

FIELD-F (Number)        

This field will appear many times for each occurrence of FIELD-E.

FIELD-G (Score)

There will be 1 occurrences of this field for each occurrence of FIELD-F.

 
 

There are other fields in each of these tables but they do not effect/affect this exercise.

 

I have JOINED table1 and table2 together by fields A,B,C of table1 with fields A,B,F of table2.

 

I do the following after loading Global variables with data.

 

"Go to Layout[“LayOutName”(Table1)]

Show All Records

Enter FindMode[]

Set Field[Table1::Field-A;$$RangeName]

Set Field[Table1::Field-B;$$ShootName]

Set Field[Table1::Field-C,$$TeamName]

 

Set Field[Table2::Field-A;$$Gun

Perform Find []]"

 

If I now look at the FOUND SET I will see a record for each Table1 record that contains matching $$RangeName, $$ShootName and $$TeamName.

 

I only see records that match the Table2 records containing Table2 FIELD-E with a value of “100”.

I know for a fact that there are Both 100 & 200 FIELD-E records for all FIELD-D records.

 

If I remove:

Set Field[Table2::Field-A;$$Gun

I get the same results.

 

Is my JOIN incorrect or does the problem have to do with my FIND?

 

Lost as usual, I remain

 

KingsPawn:smileyvery-happy:

  

Outcomes