Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2004
    Location
    Tampa, FL
    Posts
    520

    Unanswered: Correct query returning 0 records

    In Access 2000 I have a form based on a the following query
    Code:
    SELECT Client_Tbl.RegNum, Client_Tbl.Last, Client_Tbl.First, Goal_tbl.Goal, Goal_tbl.GoalID, Objective_tbl.OBJID, Objective_tbl.Objective, Objective_tbl.Objective_notes, Objective_tbl.Addnote FROM (Client_Tbl INNER JOIN Goal_tbl ON Client_Tbl.RegNum = Goal_tbl.RegNum) INNER JOIN Objective_tbl ON Goal_tbl.GoalID = Objective_tbl.GoalId WHERE (((Client_Tbl.RegNum)=[forms]![selectnameo_frm]![choose2]));
    The problem I am having is that if the client has no Goal_tbl.goal as of yet ( a new client) the query returns nothing. This is no good to me as the form that uses the query is used to Add goals.

    My question is: Did I wirte the query wrong or can it be re-written to return the valies form the Client_tbl if the two related table have no entry for the client. Or could I do something with VB for the OnLoad event.

    Any pointers would be great. thanks !
    Darasen

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Rewrite it using a LEFT JOIN to the Goal_tbl table ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Sep 2004
    Location
    Tampa, FL
    Posts
    520
    Yes you are the Grand Poobah still !
    Being a learn as I goer (if that's a word) i sometimes get stuck on seemingly simple things. Though what's Important if I need help this palce is always very helpfull. And I am then able to look at something and thenm I know why it works. Thanks again !
    Darasen

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •