one to many records

[RuralGuy]

Try changing the INNER JOIN to a LEFT JOIN and see what happens.

[Pells]

Stll the same results!

[RuralGuy]

Is it possible that there could be two PERSONNEL_NO's the same and only the COMPANY will make them unique?

[RuralGuy]

Obviously the selection Criteria is a little more complicated that we have been discussing here. Any chance you can post the db?

[Pells]

There is only 1 personnel_no per person in the EMPDET table, but yes, there are more than 1 personnel_no in the EMPTRANS table, but unique to an individual. There is only one COMPANY per person in each of the tables.

[Pells]

Obviously the selection Criteria is a little more complicated that we have been discussing here. Any chance you can post the db?

No problem.

Can this be emailed to you?

[RuralGuy]

You are testing for both PERSONNEL_NO and COMPANY so I guess it is possible there cound be a duplicate PERSONNEL_NO in another COMPANY.

[RuralGuy]

No problem.

Can this be emailed to you?

I'll PM you with my addy.

[Pells]

No, there wouldnt be a duplicate.

[RuralGuy]

Then you have defined it in the Relationship window.
FROM EMPDET INNER JOIN EMPTRANS ON (EMPDET.PERSONNEL_NO = EMPTRANS.PERSONNEL_NO) AND (EMPDET.COMPANY = EMPTRANS.COMPANY)

[Pells]

Yes, that is correct.

I will send you a copy of the DB. I will have to remove some confidential information first, but will be with you shortly.

many thanks for all your help, much appreciated.

[RuralGuy]

Understood.