Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Query Help (2007)

    I have a database with the following 3 tables. They are linked with a 1 to many relationship. (See attached photo)
    I would like some assistance in creating a query that would give me a recordset based on the following criteria.

    Unique Clients with at least 1 claim meeting the following criteria -
    Type: V04 81
    Total Charge: > 0
    Paysource: 3
    Amount: is not null or >0

    AND

    Type: V04 81
    Total Charge: > 0
    Paysource: 15
    Complete: No
    Attached Files Attached Files
    Easy Access

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Query Help (2007)

    What exactly do you mean by the AND between the sets of criteria? Should the clients have at least one claim meeting the first set, and also at least one claim (a different one, obviously) meeting the other set?

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Query Help (2007)

    Another question: in the condition Amount: is not null or >0, the part >0 is included in is not null (for a positive value is not null by definition), so it is equivalent to is not null. Is that what you intended?

  4. #4
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Query Help (2007)

    Yes, the client should have 2 different claims that meet each of the criteria sets.
    Easy Access

  5. #5
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Query Help (2007)

    The Amount criteria should filter any records that do not have a payment at all (there are no matching records in the Payment table for that claim) or if the payment amount is 0.
    So to revise my original statement...Amount: Is Not Null or =0
    Easy Access

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Query Help (2007)

    Hmm - Is Not Null or =0 again is equivalent to Is Not Null. I suspect that you mean Is Null or =0.

    How about this series of queries:

    qryFirstSet:

    SELECT Claim.ClientID
    FROM Claim LEFT JOIN Payment ON Claim.ClaimID = Payment.ClaimID
    WHERE Claim.Type="V04 81" AND Claim.TotalCharge>0 AND Claim.PaySource=3 AND (Payment.Amount Is Null Or Payment.Amount=0)

    qrySecondSet:

    SELECT Claim.ClientID
    FROM Claim
    WHERE Claim.Type="V04 81" AND Claim.Complete=False AND Claim.TotalCharge>0 AND Claim.PaySource=15

    qryCombined:

    SELECT DISTINCT Client.*
    FROM (Client INNER JOIN qryFirstSet ON Client.ClientID = qryFirstSet.ClientID) INNER JOIN qrySecondSet ON Client.ClientID = qrySecondSet.ClientID

    The latter is the one that returns the unique clients. It is not updateable. An updateable alternative is

    SELECT *
    FROM Client
    WHERE ClientID In (SELECT ClientID FROM qryFirstSet) And ClientID In (SELECT ClientID FROM qrySecondSet)

    See attached small demo database in Access 2000 format.
    Attached Files Attached Files

Posting Permissions

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