D
D. Austin
I am trying to create a bad debt report and I am having some difficulties
and I was hoping I could get some help. I started out with bunch of raw
data listing customer number, Invoice number, invoice date, invoice amount.
I have taken this data and made query that inputs the current date adds a
company code, calculates the age of each invoice and then base series of if
statements adds a reserve percentage , 60 to 90 = 2%, 90-120 = 10% and so
on up to 100%. I also created another query that adds a special reserve
code, The query looks at a table of customers that are in collections or
bankruptcy and adds a code, BANK for bankruptcy and COLL for collections.
From these two queries I made several more queries that summarized the data
by company code by each of the different reserve amounts i.e 2% , 10%
......."BANK" and "COLL" these queries only contained the company codes and
the total reserve amount. From this I created a final query that would take
all theses queries and base on company code be able to tell me in summary
what each reserve amount is. I then create a report that would summarize
the data by company code into each of the different reserve buckets. Then
the subtotal by company, gross invoice and total reserve amount.
Everything works fine as long as I have data in each one of my queries, I
have a few companies that do not, some might have customers that have
nothing in the 2% reserve amount or may have no customers in bankruptcy so
when I run the data through my queries will have no data and in turn my
formulas that will not foot it just leaves them blank. the only way I have
been able to make it foot is if I exclude in my formula the queries that
have no data. I have tried several things. I tried to us a if statement
say If(BANK Is Null,0,[BANK] Amt) I have also tries several other things
but I can't get anything to work.
So to make a long story short is there a way, if a query has no data, the
value of that no data query is zero or something similar.
I hope I explained my problem well enough
Thank you
and I was hoping I could get some help. I started out with bunch of raw
data listing customer number, Invoice number, invoice date, invoice amount.
I have taken this data and made query that inputs the current date adds a
company code, calculates the age of each invoice and then base series of if
statements adds a reserve percentage , 60 to 90 = 2%, 90-120 = 10% and so
on up to 100%. I also created another query that adds a special reserve
code, The query looks at a table of customers that are in collections or
bankruptcy and adds a code, BANK for bankruptcy and COLL for collections.
From these two queries I made several more queries that summarized the data
by company code by each of the different reserve amounts i.e 2% , 10%
......."BANK" and "COLL" these queries only contained the company codes and
the total reserve amount. From this I created a final query that would take
all theses queries and base on company code be able to tell me in summary
what each reserve amount is. I then create a report that would summarize
the data by company code into each of the different reserve buckets. Then
the subtotal by company, gross invoice and total reserve amount.
Everything works fine as long as I have data in each one of my queries, I
have a few companies that do not, some might have customers that have
nothing in the 2% reserve amount or may have no customers in bankruptcy so
when I run the data through my queries will have no data and in turn my
formulas that will not foot it just leaves them blank. the only way I have
been able to make it foot is if I exclude in my formula the queries that
have no data. I have tried several things. I tried to us a if statement
say If(BANK Is Null,0,[BANK] Amt) I have also tries several other things
but I can't get anything to work.
So to make a long story short is there a way, if a query has no data, the
value of that no data query is zero or something similar.
I hope I explained my problem well enough
Thank you