Re: Help with Expression within Query

what if you just use a left join, and then test the foreign key of the
right side table for null?

SELECT A.Field1, A.Field2, B.ForeignKey, B.Field3...,
iif(IsNull(B.ForeignKey), "Yes","No") AS FKFieldIsNull
FROM A LEFT JOIN B ON A.PrimaryKey=B.ForeignKey)
....

You can get most of this done by using the "Find Unmatched" query
wizard and then adding a field to your query and testing the foreign
key of the "right side" table (the one that may not have records
corresponding to the other table's PK) that returns Yes or No.

(Yeah, I bet that explanation totally confused you... well, try the SQL
and go from there...)

Hope it helps a little.

.

Relevant Pages

  • Re: Optimiztion question
    ... The sample screen shot in Figure 14.2 shows an execution plan that accesses only one table, although two tables are included in a join in the query. ... Because there is a declared foreign key relationship between the authors table and the titleauthor table, and the au_id column in the titleauthor table is not allowed to be null, the optimizer knows it does not need to access the authors table to resolve the query. ... ALTER TABLE.NOCHECK CONSTRAINT ...
    (microsoft.public.sqlserver.programming)
  • Re: Optimiztion question
    ... as to why a foreign key relationship affects the query in this case. ... between the authors table and the titleauthor table, ...
    (microsoft.public.sqlserver.programming)
  • RE: Query Problem, Please Help!!
    ... >> If you did these things, the query would be super easy, such as the following: ... >> If you used a foreign key to store the months, then the query could join the ... >> for spammers are Ripley@xxxxxxxxxxxxxxx and scott@xxxxxxxxxxxxxxxxxx ... >> When you see correct answers to your question posted in Microsoft's Online ...
    (microsoft.public.access.queries)
  • Re: populating combo box in access form
    ... include the foreign key from the many-side table. ... I've been using the Query ... I now have an employee table (tblEmployee) that also has drop down ... table (tblAssign) which has assignment details. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Calculate among different table to get the final total
    ... LoanID (foreign key of LOAN TABLE) ... for them to display in the query. ... I dont mind how it works as long as I can get the total Quantity to be ...
    (microsoft.public.access.formscoding)