two columns sql join 2

[ck1999]

I am trying to join two tables based off the fields salesorderyear and salesordernum

tblA
salesorderyear
salesordrenum

tblb
soyear
sonum

It is a 1:1 relationship. There are a bunch of fields so the tables are divided based on sections of a report.

I am trying to join the data for the report but am not sure since it is two fields instead of 1.

Any help would be appreciated!

ck1999

 

[BigRed1212]

Don't know what your desired output is but you can start with something like:

select tblA.*, tblB.*
from tblA left join tblB on tblA.salesorderyear = tblB.soyear and tblA.salesordernum = tblB.sonum
order by salesorderyear, salesordernum

That should give you all fields for all records in tblA and all fields for all records in tblB with a sonum and a soyear that match that combination of salesorderyear and saleordernum in tblA.

 

[ck1999]

thanks for the help!

How would I accomplish this for a subform?

ck1999

 

[MajP]

A subform allows you you use a composite key for linking. Even if you use the wizard. If you do not use the wizard then seperate the fields with a semicolon

link Master Fields: salesorderyear;saleordernum
link child fields: soyear;sonum

However, there is really no such thing as a 1:1 relationship. All fields that uniquely describe an entity should be in the same table. Designing your database to support a report structure does not make any sense:
"divided based on sections of a report"

There are sometimes legit reasons to split tables; security, limit access to priveledged information, import export restrictions, etc.

 

[ck1999]

Thanks majp that did the trick

ck1999