Query greater than zero

[BobNTN]

using

SELECT TblCustInfo.Name, TblCustInfo.Addr, TblCustInfo.City, TblCustInfo.State, TblCustInfo.Zip, TblCustInfo.SrvAmt, TblCustInfo.CID, TblCustInfo.BLCAT, Sum([TblCharges.Chargeamt]) AS SumOfCharges, Sum([TblPayments.Creditamt]) AS SumofCredits, ([SumofCharges]-nz([SumOfCredits])) AS RunBalance, TblMemo.Invmemo
FROM ((TblCustInfo LEFT JOIN TblMemo ON TblCustInfo.State = TblMemo.State) LEFT JOIN TblCharges ON TblCustInfo.CID = TblCharges.CID) LEFT JOIN TblPayments ON TblCustInfo.CID = TblPayments.CID
GROUP BY TblCustInfo.Name, TblCustInfo.Addr, TblCustInfo.City, TblCustInfo.State, TblCustInfo.Zip, TblCustInfo.SrvAmt, TblCustInfo.CID, TblCustInfo.BLCAT, TblMemo.Invmemo, TblCustInfo.Cancel
HAVING (((TblCustInfo.BLCAT)="m1") AND ((TblCustInfo.Cancel)="n"))
ORDER BY TblCustInfo.Zip;

is there a way to return only records resulting in greater than zero from the ([SumofCharges]-nz([SumOfCredits])) AS RunBalance part ???

 

[pbaldy]

What happens if you add

AND ([SumofCharges]-nz([SumOfCredits])) > 0

to the HAVING clause?

 

[BobNTN]

pops up a parameter box for Sumofcredits
tried just RunBalance does the same

 

[pbaldy]

Sorry, didn't see those were calculated. Try

AND (Sum([TblCharges.Chargeamt]) - Nz(Sum([TblPayments.Creditamt]),0)) > 0

 

[BobNTN]

That works Paul, Thanks

Now, one more thing and I believe this is done. I think.

Tell me how one can have a table with 3 or 4 fields and none of them associated with any records ?

Like the debit dates and a memo ? Stuff that is retrievable for just printing or displaying purposes ??

 

[pbaldy]

Just create the table/fields; it doesn't need to be related to any other table. To get a value out for whatever, you can use DLookup, or since it will only have one record, you could add it to any query and add fields from it to the output.

 

[BobNTN]

made TblMisc and added two date fields QDbtDate and MDbtDate and a Invmemo field for my invoices.
added to my Invoice query but when I try to retrieve the memo, it says it can't do it because of an ambiguous join, bla bla, bla.
Thought I had run into this before.

The more I try to do the more ........

Getting so close

 

[pbaldy]

There should not be a join between other tables and that one. Normally that would cause a Cartesian product, but with one record in that table it should not cause a problem.

 

[BobNTN]

well, I can put data in from a form, I can query the table, but I can not get any data from it on a report.
just gives the ambiguous outer join bla bla bla

There HAS to be an easier way to hold data in three fields and either display or print any one or all.


* Command button, next to it shows the date it was last clicked

* single memo text field that prints on a report

Holy Moly, Moses smell the roses!

 

[pbaldy]

Can you post or email me a sample?

 

[pbaldy]

You can delete it. Because the report query is a totals query, it doesn't like the extra table in there. You can either base another query on that one and add the misc table there, or simply use DLookup on the report:

=DLookUp("Invmemo","tblMisc")

 

[BobNTN]

The dLookup works. And it works for the debit dates also.

I think, for the present, I'll just have her input the dates of debit in the Invoice memo form.

Now I'll finish checking everything and I think it will all work. Need to import all her live data.

Maybe I can try to refine some things here and there later but at least it appears it is usable and she will have a debit and credit history.

I really appreciate all your help.

 

[pbaldy]

No problem; see, I told you we could bat it out by Monday!

Post back if you get stuck on anything.

 

[BobNTN]

Well He--, just found a problem.
My QryDbtM1 and Q1 -
Once you debit once, the second time adds 2 more debits, the third time adds 3 more, etc.
Is that because of the "Debit" word I have appending to Chargereason and Date() to Chargedate ??

 

[pbaldy]

No, it's because of the join. There's no reason to have TblCharges in the FROM clause. In design view of the query, just delete that table. The SQL should end up like:

INSERT INTO TblCharges ( CID, Chargeamt, Chargereason, Chargedate )
SELECT TblCustInfo.CID, TblCustInfo.SrvAmt, "Debit" AS Expr1, Date() AS Expr2
FROM TblCustInfo
WHERE (((TblCustInfo.BLCAT)="m1") AND ((TblCustInfo.Cancel)="n"))
ORDER BY TblCustInfo.CID;

And I would drop the ORDER BY. The order of records in a table is irrelevant, and there's probably a slight performance hit having that in there.

 

[BobNTN]

I was on the assumption that the table of any field specified had to be in the window. Guess we know what assume does.

But I don't understand why just having the table open was doubling, tripling, etc each time.
Oh well, as usual, thanks for bailing me out.

 

[pbaldy]

Because the charges table was included, it was pulling from that table too. Thus every time you appended records to that table, the next time it would pull those records. Let's say you have 100 customers. The first time you run that query it's really only pulling from the customer table, so you append 100 records. The second time, the 100 previous charges match the 100 customers so you'll still get 100 records. However the third time, since there are now 200 records in the charges table, it will return (and thus append) 200 records. From there on it will double every time (400, 800...).

 

[BobNTN]

Ok, my balance sheet is only showing positive balances based on this query:

SELECT TblCustInfo.CID, TblCustInfo.Name, TblCustInfo.SrvAddr, TblCustInfo.SrvAmt, TblCustInfo.BLCAT, Sum([TblCharges.Chargeamt]) AS SumOfCharges, Sum([TblPayments.Creditamt]) AS SumofCredits, ([SumOfCharges]-nz([SumofCredits],0)) AS RunBalance
FROM (TblCustInfo LEFT JOIN TblCharges ON TblCustInfo.CID = TblCharges.CID) LEFT JOIN TblPayments ON TblCustInfo.CID = TblPayments.CID
GROUP BY TblCustInfo.CID, TblCustInfo.Name, TblCustInfo.SrvAddr, TblCustInfo.SrvAmt, TblCustInfo.BLCAT, TblCustInfo.Cancel
HAVING (((TblCustInfo.Cancel)="n"))
ORDER BY TblCustInfo.CID;

specifically the

([SumOfCharges]-nz([SumofCredits],0)) AS RunBalance

How can I force it to show result regardless if it is positive, zero or credit ?

 

[pbaldy]

If you're asking what I think you're asking, wrap the charges part in an Nz() function the same way you did the credits part.

 

[BobNTN]

that fixed it
I hope I have run out of stuff to fix!!

Thanks

I did do something sort of smart - for me anyway.
All existing balances I made a query to put them in charges table as Balance forward and any negative balances (credits) into payments as Credit forward.
worked pretty well.