Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2011
    Posts
    47

    Unanswered: Help with Query...

    I have a query that seaches 3 tables:

    1) tblclientdetails(FamilyID, First Name, Surname,Status, Typeofwork)
    2) tbllegalaidledger(Code, Minutes, Items, Billed Total,All, FamilyID)
    3) tbllegalaidfiancialledger (Code, Credit, FamilyID)
    FamilyID is a foregin key in the last two tables.

    At the moment for Status and Typeofwork there is a Criteria of "Active" and "LegalAid" respectively. From the tbllegalaidledger it calculates a total for All as well.
    I need the query to search the tbllegalaidfinancialledger in the CODE for "PA" and display the credit for that, if a particular person does not have a "PA" in tbllegalaidfinacialledger i need to query just to show nothing but still show that person.

    How do I go about this?

    Thanks

  2. #2
    Join Date
    Oct 2009
    Posts
    204
    Put in criteria under the [Code] field in that query:

    =Iif([Code]="PA",[Code],"")

  3. #3
    Join Date
    Jul 2011
    Posts
    47
    Quote Originally Posted by chris07tibgs View Post
    Put in criteria under the [Code] field in that query:

    =Iif([Code]="PA",[Code],"")
    Hi Chris, thanks for the reply.

    That IFF statement is filtering everyone out that doesn't have a "PA" code for the tblfiancialledger.
    I need a statement that filters out "PA" and its value for credit, if a person has one and if the person doesn't I need it to show 0 and still show everyone!

    In short I need something Iff the code is in "PA" then show the value, if the person does not have "PA" then show still show the person in the query with the other parameters and return a value of 0 for "PA"

    Thanks

    Sorry for any confusion.

    Thanks
    Last edited by rasghar; 11-15-11 at 19:38.

  4. #4
    Join Date
    Oct 2009
    Posts
    204
    You can put criteria on any field in the query.

    I don't know your field names but if [Credit] is the field you want to return "0" if [Code] is "PA", then you would put this under the criteria for the [Credit] field:

    =Iif([Code]="PA",[Credit],"0")

    Repeat like this for other fields till you get to where you want.

Posting Permissions

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