Need immediate help

  • Thread starter Thread starter Shahzad Atta
  • Start date Start date
S

Shahzad Atta

Hi There,

Need help in creating the query for the following scenario

I have a table named User with User.ID as primary key. this key goes as
foreign key in another table called Roaster. But the foreign key occurs
twice in a row as
Roaster.CreatedByID and Roaster.ModifiedByID
the two fields (Roaster.CreatedByID and Roaster.ModifiedByID)can contain
different values which also includes Null value.

Now I want to retrieve data from Roaster and User table on the basis of
Roaster.CreatedbyID and Roaster.ModifiedByID. the problem is when I write an
inner join query in case of Null values no record is returned. In case of
one null value the same happens.

I wrote the following query:

Select R.fld1, R.fld2 , U1.fld1 , U2.fld1
From Roaster as R, Uers as U1, User as U2
where
R.fld = some value
And R.CreatedByID = U1.ID
And R.ModifiedByID= U2.ID

in this case no records are returned but if I slightly modify the query as

Select R.fld1, R.fld2 , U1.fld1 , U2.fld1
From Roaster as R, Uers as U1, User as U2
where
R.fld = some value
And R.CreatedByID = U1.ID
OR R.ModifiedByID= U2.ID

multiple results are returned.
Also Access is not allowing me to use Left Outer Join as it says Error in
expression
 
Dear Shahzad Atta:

Should it not be:

Select R.fld1, R.fld2 , U1.fld1 , U2.fld1
From Roaster as R, Uers as U1, User as U2
where
R.fld = some value
And (R.CreatedByID = U1.ID
OR R.ModifiedByID= U2.ID)

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top