# Thread: help plz

1. Registered User
Join Date
Aug 2005
Posts
18

## Unanswered: help plz

i have 2 queries totaldues and totalPayments
totaldues has 2 fileds(custID (Group By)
amountDue(sum of all invoices dues)
and totalPayments consist of CustID(Group By) and Amount_Paid(sum of
all payment)

thses queries calculate total dues and total payments customer made.
then i have another query on the base of these 2 queries which is
called custGrandBalance and it consist of customerID,amountDue(which is
sum of all dues about this customer), Amount_Paid(sum of all payment),
TotalDues: ([SumOfamountDue]-[SumOfAmount_Paid])

this query brings me total dues for the customer. Now problem starts
here that if i have amountDue > 0 but amount_Paid is nothing that means
customer never made any payment then this query will show me blank
record. for some reason. but if i put any payment in payment table then
i see the result. can any one help why is that. If there is no record
in query totalPayemnts then it should be equal to 0 which is not
happening

is there any way if amount_paid has no value, when we run the query,
then we could put it equal to 0
Last edited by khanji; 11-28-05 at 13:03.

2. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102
Provided Answers: 1
Zero is different from null. Null combined with anything = Null.

Try using the nz() function to replace null values with a numeric value:

TotalDues: (nz([SumOfamountDue], 0)-nz([SumOfAmount_Paid], 0))

Side Note: Please be more discriptive with your thread titles. The fact that you are posting a thread implies you need help. Therefore a thread title stating "HLP PLZ" is redundant. A lot of people ignore those kind of titles...

3. Registered User
Join Date
Aug 2005
Posts
18
great help thanks man, i have solved my problem, god bless you. can you answer me one more question. How to take automatic backup of my database at certain time. I want to save only my data in the table on a backup drive or some other drive can you help plz

4. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102
Provided Answers: 1
There is quite a variety of ways to do this. One of my personal favorites is another db with an autoexec macro (well, I use a little vb6 executable, same difference) that opens your target db and dumps all of it's tables to an xml or excel file. I then use windows scheduling to launch my "backup" application. How do you envision your desired process working, in general?

5. Registered User
Join Date
Aug 2005
Posts
18
man any thing easy, which is reliable. I want back up on zip drive and thats it. I dont care which way. In any way.

6. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102
Provided Answers: 1
In that case, I've already given you a fairly easy and reliable method...

7. Jaded Developer
Join Date
Nov 2004
Location
out on a limb
Posts
13,692
Provided Answers: 59
Originally Posted by Teddy
.....One of my personal favorites
.... that opens your target db and dumps all of it's tables to an xml or excel file.
would it not just be easier to take a copy to the file(s), using a batch file rather than a VB process / macro?

agree with using the windows scheduler to do the dirty deed though.....

8. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102
Provided Answers: 1
He specifically mentioned wanting data only. I used this method in a scenario where a seperate process would look at the file I was dumping too and cull differential backups to a data warehouse facility. There aren't many reasons to pull ONLY data from access when backing it up, but they exist.

#### Posting Permissions

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