Page 1 of 2 12 LastLast
Results 1 to 15 of 23

Thread: Query problem

  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query problem

    How do I word this?? Well here goes.
    I have a master form that keeps track of court ordered restitution, amount owed, amount paid, balance due and such, within the master form is a subform that keeps track of method, date, and amount of each payment a defendant would make.
    The amount paid from the master form gets its value from the Sum of the payment field in the subform.
    Here is what I am trying to do. I want to keep track of who hasn't paid the amount they were ordered to pay within a specified date. I created the query below that should return all records that meet this criteria.

    [Amount Paid]<[Amount Ordered] OR Is Null AND [Date Due]<[Date()]

    The date part works fine. It's the Is Null part that's killing me. It doesn't return the records where [Amount Paid] Is Null, because they haven't made a payment so therefore there is no record in the details table.
    Does anyone understand what I'm saying?? I'm having trouble explaining it here, sorry. I really hope someone here can help. Thanks for trying to understand me. Here's the SQL.

    SELECT DISTINCT tblMaster.[Docket #], tblMaster.[First Name], tblMaster.[Last Name], tblMaster.[Amount Ordered], Sum(tblDetails.Payment) AS [Amount Paid], [Amount Ordered]-[Amount Paid] AS [Balance Due], tblMaster.[Date Due], tblMaster.[Date Ordered]
    FROM tblMaster RIGHT JOIN tblDetails ON tblMaster.[Docket #] = tblDetails.[Docket #]
    GROUP BY tblMaster.[Docket #], tblMaster.[First Name], tblMaster.[Last Name], tblMaster.[Amount Ordered], [Amount Ordered]-[Amount Paid], tblMaster.[Date Due], tblMaster.[Date Ordered]
    HAVING (((Sum(tblDetails.Payment))<[Amount Ordered]) AND ((tblMaster.[Date Due])<Date())) OR (((Sum(tblDetails.Payment)) Is Null));

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    The Netherlands
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query problem

    Hi,

    Try this:

    ([Amount Paid]<[Amount Ordered] OR [Amount Paid] Is Null) AND [Date Due]<[Date()]

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    The Netherlands
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query problem

    Hi again,

    I took another look at the query.
    This part doesn't work: Sum(tblDetails.Payment) Is Null
    The Sum function never returns a null value!
    Null means nothing, that is less then 0!
    Change it to Sum(tblDetails.Payment) = 0.

  4. #4
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query problem

    In your tblMaster, where is the Balance Due field getting its value? If this is an up to date and accurate figure, then your query should simply test for Balance Due <> 0

  5. #5
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Massachusetts
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query problem

    Jols,
    Someone may jump in with a better solution, but I have some ideas for you.

    First, an observation. In tblMaster, you have Amount Paid, and Balance Due, this seems confusing, since they appear to be calculated fields based on tblDetails, is that correct? If that is the case, it would seem those fields are better calculated by doing a calculation on tblDetails, and not storing the value in tblMaster, otherwise, it appears you would need to update two fields, whenever a payment is made. For the calculated values on the form you have setup, you can use a dlookup, or the query below, when done.

    As for the query issue, I would recommend a subquery be used. It seems the main issue is that the way you have it setup, no records are returned when there is no payment. You need to retrieve those records also. I would do this as follows: (Please let me know if you need more detail)

    Create a query, similar to the one you posted, setting the link to retrieve all records from tblMaster, and those that are equal from tblDetails(joined on Docket #). Only add the fields Docket # from tblMaster, and Payment from tblDetails (set total to Sum). This will return all records, including records for which no payment is recorded.

    The SQL for the subquery (leaving out extraneous fields, that you may want to add):
    Name: qryPayments

    SELECT tblMaster.[Docket #], Sum(tblDetails.Payment) AS SumOfPayment
    FROM tblMaster LEFT JOIN tblDetails ON tblMaster.[Docket #] = tblDetails.[Docket #]
    GROUP BY tblMaster.[Docket #];

    The main query (note, if you change the subquery name, change it in here)
    Name: qryRestitution

    SELECT tblMaster.[Docket #], tblMaster.[Date Due], nz([SumOfPayment],0) AS Payment
    FROM tblMaster INNER JOIN qryPayments ON tblMaster.[Docket #] = qryPayments.[Docket #]
    WHERE (((tblMaster.[Date Due])<Date()) AND ((nz([SumOfPayment],0))<[Amount Ordered]));


    Note: the NZ function is only available in Access 2000, if you are not using 2000, I can send you code to write it.

    Let me know if this is too confusing.

  6. #6
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Massachusetts
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query problem

    ok, I dated myself a bit [img]/w3timages/icons/blush.gif[/img]

    NZ is available in 97 AND 2000, sorry for the confusion.

  7. #7
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query problem

    Hey James, you have really helped me get things rolling over here. I created a quick query like you said and then it hit me, the joins were wrong!! All this time and it's something like that.
    Yes my Amount paid and balance due field of my master form are calculated fields.
    Amount Paid get it's value from a calculated control in the subform that contains the Sum of all the payments.
    Here is the control source for it: "=[frmDetails_subform].[Form]![txtPaymentTotal]"
    Balance Due gets it's value from Amount Ordered in the master table and the result of the Amount Paid calculation.
    Control Source for Balance Due is: "=IIf([txtAmountPaid] Is Null,[txtAmountOrdered],[txtAmountOrdered]-[txtAmountPaid])"

    Do you think I should be doing this a different way, dlookup, or a query or something? The way it is now seems to work fine, but then again I am green here.[img]/w3timages/icons/sick.gif[/img]
    Thanks so much for your help and suggestions.[img]/w3timages/icons/smile.gif[/img]
    Jols

  8. #8
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Massachusetts
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query problem

    From your description, it sounds like you are getting the values fine from the subform. My concern/observation is your decision to store those values in the tblMaster. By doing that, you run into a situation where problems could arise, if they are ever updated improperly. The rule of thumb with tables is to usually store the raw values for calculations in the tables, but to perform the calculations on the fly, when needed. This ensures that you always have the most current data. It also makes your tables much more portable, and may eliminate headaches down the road. For example, what would happen if you decided to make a quick entry screen, where just the subform showed, so you could update a group of payments quickly. The way you have it set up, the tblMaster would not be updated with the calculations, unless you created something to do it, and the potential for inaccurate data is high.
    So, if it was me, I would leave the fields Amount Paid and Balance Due on the main form, but do not have them update the tblMaster (in other words, delete them from tblMaster, and leave the Main Form formulas there, but do not set them to update the table). Then, in any place you need the values calculated, create a formula or query (depending on the scenario).

    Let me know if I made this more confusing than it needs to be.

  9. #9
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query problem

    Ohhh no, I'm not storing the values of any of my calculations in any tables. They are just calculated in the forms and queries otherwise they cease to exist.
    The fields Amount Paid and Balance Due are blank in the table Master.
    Thanks a bunch brother!
    Jols

  10. #10
    Lounger
    Join Date
    May 2002
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query problem

    I know this is wayyyy off topic... [img]/w3timages/icons/laugh.gif[/img] ...but I just have to know.... How did you create those screen shots that you attached to your post???? ...I'm sure that will come in handy for me at some point... [img]/w3timages/icons/baby.gif[/img] (Anyone who knows could pass the secret along to me if they'd like?! [img]/w3timages/icons/smile.gif[/img])

    Thanks!! Have a great afternoon!!

  11. #11
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query problem

    I used PaintShop Pro 6.0. It includes a screen capturing tool. I'm not sure what other programs out there offer this feature? Maybe someone here knows of others. Once the screen is captured it's just like attaching any other file.
    Check out PaintShop Pro 7.0, it's powerful and cheap....around $80.

  12. #12
    Lounger
    Join Date
    May 2002
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query problem

    Thanks djoly! I have used Paint Shop Pro before... I should have known it was something like that... I thought maybe it was an option in Access... [img]/w3timages/icons/laugh.gif[/img] ...Silly me!
    Thanks again... Have a great day!

  13. #13
    Star Lounger
    Join Date
    Jan 2001
    Location
    Bethel, CT, USA
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query problem

    You mean like this one?
    Just ALT+PRINT SCREEN, then go where you want it and PASTE.
    No need to buy fancy software, you already own it.
    I use this function and then paste into PAINT for editing.
    Then I can include pictures in Word.doc's of just the portion I want to show.
    Attached Images Attached Images

  14. #14
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Massachusetts
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query problem

    Jols,
    Are you using the NZ function in the the query that is the source for the form? My best guess, is you a getting Null values instead of 0 values, if nulls are converted to 0 (with NZ), they should show as $0.00

  15. #15
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query problem

    Ooops I forgot about that feature, I use Paint Shop so much. Thanks for pointing that out Bill.

    Sorry Alexya

Page 1 of 2 12 LastLast

Posting Permissions

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