Better way to Join?

  • Thread starter Thread starter Glen
  • Start date Start date
G

Glen

I have a primary table with 2 fields (JobIn and JobOut)
that both contain a job number. A secondary table has
JobNumber and JobDesc. When I link the JobNumber to both
the fields in the primary table and set the join to
include all records from the primary and only matches
from the secondary, the result is a join with AND between
the two links. If I manually change the AND to OR,
Access will no longer show me Design mode for the query.
The end of the SQL is:
ON (vMasterTemplate.JobNumber =
InventoryTransactions.ToJobID) AND
(vMasterTemplate.JobNumber =
InventoryTransactions.FromJobID);

Is there a better way to do this?
Thanks
 
Glen said:
I have a primary table with 2 fields (JobIn and JobOut)
that both contain a job number. A secondary table has
JobNumber and JobDesc. When I link the JobNumber to both
the fields in the primary table and set the join to
include all records from the primary and only matches
from the secondary, the result is a join with AND between
the two links. If I manually change the AND to OR,
Access will no longer show me Design mode for the query.
The end of the SQL is:
ON (vMasterTemplate.JobNumber =
InventoryTransactions.ToJobID) AND
(vMasterTemplate.JobNumber =
InventoryTransactions.FromJobID);
Click to expand...


Not at all sure what's preventing the query design from
coming up, but it sounds like you should add the secondary
table twice. Then connect the JobIn to one copy of the
table and JobOut to the other copy.
 
It looks like you only need one table:
TblJob
JobID
JobNumber
JobDesc
JobIn
JobOut

It's not clear what JobIn and JobOut are but nonetheless you don't have a
one-to-many re;ationship and consequently don't need two tables.
 
Well, that worked to get design mode back. However, when
I tried to use the field from the aliased table in
Crystal Reports, I get an ODBC error. On to Crystal to
see if there is a solution there.
Thanks
 
Back
Top