Query problem

Topic

New Reply

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));

Reply | Quote

Replies

Hi,

Try this:

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

Reply | Quote

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.

Reply | Quote

I have attached a screenshot of the design grid of the query. Maybe this will help you understand what I’m doing wrong?
Bart, I have tried using both =0 and Is Null. The =0 in the “OR” of the tblDetails. Payment only returns the correct records when I go into the subform and lets say I put the word “check” in the method field of the subform, but I leave the Payment field blank. Does this explain my problem any better?
Maybe I have the criteria in the grid on the wrong lines??
I want the result to show the records where…
“tblDetails.[Amount Paid]=0 OR <tblMaster.[Amount Ordered] And tblMaster.[Date Due]< (Date())."
I'm so close here. I think??
Thanks for all help.
Jols

Edited by Charlotte to remove attachment

Reply | Quote

These screen shots might help also. See how my first forms subform is empty because they haven’t made a payment. (Payment field default value=0)
The query doesn’t work until I make an entry in the subform like in the second screen shot. Now with the “Paid by check” entered in the subform, the query recognizes that the payment field =0.
Anyone know how I can get this query to work regardless if an entry is made in the subform???

Edited by Charlotte to remove attachment

Reply | Quote

Second screen shot.

Edited by Charlotte to remove attachment

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

ok, I dated myself a bit

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

Reply | Quote

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.
Thanks so much for your help and suggestions.
Jols

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

I know this is wayyyy off topic… …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… (Anyone who knows could pass the secret along to me if they’d like?! )

Thanks!! Have a great afternoon!!

Reply | Quote

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.

Reply | Quote

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… …Silly me!
Thanks again… Have a great day!

Reply | Quote

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.

Reply | Quote

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

Sorry Alexya

Reply | Quote

I was being kind of a smart Alec. Because of the 100K file limit on this site I cheated and used a image converter to make the attachment a GIF so that it was small enough to show here. But that’s the first time ever I needed a conversion. Honest!
Oh, and about you query results. I always use a default value of zero in any number fields just to avoid problems with null.

Reply | Quote

I think you need to use caution with a default value of zero for any number field. Although at times appropriate, 0 and null both have their uses, and a null is sometimes helpful. For instance, averaging 1,2,0,0,2 yields 1, but averaging 1,2,null,null,2 yields 1.66.

Reply | Quote

James,
Yeah I see what your saying about null vs. 0. I think my using 0 should work out just fine. Everything here is working great.
I really appreciate all your help!
Jols

Reply | Quote

Guys, you’re getting way off topic here. If you want to continue this discussion of graphics capture, the appropriate forum is Lounge Matters, where loungers who never dip into this forum can follow it.

Reply | Quote

Hey James,
Yes it’s me again. I just have one more quick question since you seem to have the idea what I’m doing here. The form that displays those who are in default, uses the qryInDefault as it’s RecordSource. I have attached what this looks like. My problem is that the Amount paid field for the records where no payment was made are left blank. I need to fill this with $0.00. Any ideas?
Thanks once again.
Jols

Edited by Charlotte to remove attachment

Reply | Quote

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

Reply | Quote