Monthly outstanding query

  • Thread starter Thread starter Ramesh
  • Start date Start date
R

Ramesh

HI,

I have a Receivable table with CustID, DueDate and AmountDue. Another
Receipts Table with CustID, RctDate, AmountPaid.

How can I make a query to display What was collectable each month and What
was actually collected?

No problem with getting monthly collections part. Where i am getting stuck
is that I need to add the outstanding of the previous month for each month.

Any help please.

Thanks
Ramesh
 
Hi Ramesh,

try this:

PrevDue: dSum("AmountPaid", "[Receivable Table]", "CustID=" &
[tablename].CustID & " AND DueDate <=#" & DateSerial(year(date()),
month(date(),0) & "#")

PrevPaid: dSum("AmountPaid", "[Receipts Table]", "CustID=" &
[tablename].CustID & " AND RctDate <=#" & DateSerial(year(date()),
month(date(),0) & "#")

PreBalance: PrevDue - PrevPaid

where
tablename is one of the tables in the source with CustID (since both
tables will have it, you need to qualify the reference)


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
thanks crystal.

there was some problem regarding the argumetns in the expression. will read
up on the dsum and try to figure it out. this is a new function for me.

thanks for your support.

Ramesh

strive4peace said:
Hi Ramesh,

try this:

PrevDue: dSum("AmountPaid", "[Receivable Table]", "CustID=" &
[tablename].CustID & " AND DueDate <=#" & DateSerial(year(date()),
month(date(),0) & "#")

PrevPaid: dSum("AmountPaid", "[Receipts Table]", "CustID=" &
[tablename].CustID & " AND RctDate <=#" & DateSerial(year(date()),
month(date(),0) & "#")

PreBalance: PrevDue - PrevPaid

where
tablename is one of the tables in the source with CustID (since both
tables will have it, you need to qualify the reference)


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


HI,

I have a Receivable table with CustID, DueDate and AmountDue. Another
Receipts Table with CustID, RctDate, AmountPaid.

How can I make a query to display What was collectable each month and
What was actually collected?

No problem with getting monthly collections part. Where i am getting
stuck is that I need to add the outstanding of the previous month for
each month.

Any help please.

Thanks
Ramesh
Click to expand...
Click to expand...
 
dSum
---

Hi Ramesh,

dSum("expression", "domain", "criteria")

WHERE
- expression is a field or an equation involving one or more fields from
the domain
- domain is a tablename or a queryname
- criteria is how to limit the records (same as the Where clause of an
SQL statement without the word Where)

You must substitute YOUR fieldnames and YOUR tablenames <smile>

if a name contains a space or special character, it must be enclosed in
square brackets

"AmountPaid" and "[AmountPaid]" are both ok
"Receivable Table" is NOT okay, it must be "[Receivable Table]"
(but I did not know the name of your table, it is probably different)

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


thanks crystal.

there was some problem regarding the argumetns in the expression. will read
up on the dsum and try to figure it out. this is a new function for me.

thanks for your support.

Ramesh

strive4peace said:
Hi Ramesh,

try this:

PrevDue: dSum("AmountPaid", "[Receivable Table]", "CustID=" &
[tablename].CustID & " AND DueDate <=#" & DateSerial(year(date()),
month(date(),0) & "#")

PrevPaid: dSum("AmountPaid", "[Receipts Table]", "CustID=" &
[tablename].CustID & " AND RctDate <=#" & DateSerial(year(date()),
month(date(),0) & "#")

PreBalance: PrevDue - PrevPaid

where
tablename is one of the tables in the source with CustID (since both
tables will have it, you need to qualify the reference)


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


HI,

I have a Receivable table with CustID, DueDate and AmountDue. Another
Receipts Table with CustID, RctDate, AmountPaid.

How can I make a query to display What was collectable each month and
What was actually collected?

No problem with getting monthly collections part. Where i am getting
stuck is that I need to add the outstanding of the previous month for
each month.

Any help please.

Thanks
Ramesh
Click to expand...
Click to expand...
Click to expand...
 
crystal , this is very gracious of you.

Thanks a million. Very helpful.

Ramesh

strive4peace said:
dSum
---

Hi Ramesh,

dSum("expression", "domain", "criteria")

WHERE
- expression is a field or an equation involving one or more fields from
the domain
- domain is a tablename or a queryname
- criteria is how to limit the records (same as the Where clause of an SQL
statement without the word Where)

You must substitute YOUR fieldnames and YOUR tablenames <smile>

if a name contains a space or special character, it must be enclosed in
square brackets

"AmountPaid" and "[AmountPaid]" are both ok
"Receivable Table" is NOT okay, it must be "[Receivable Table]"
(but I did not know the name of your table, it is probably different)

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


thanks crystal.

there was some problem regarding the argumetns in the expression. will
read up on the dsum and try to figure it out. this is a new function for
me.

thanks for your support.

Ramesh

strive4peace said:
Hi Ramesh,

try this:

PrevDue: dSum("AmountPaid", "[Receivable Table]", "CustID=" &
[tablename].CustID & " AND DueDate <=#" & DateSerial(year(date()),
month(date(),0) & "#")

PrevPaid: dSum("AmountPaid", "[Receipts Table]", "CustID=" &
[tablename].CustID & " AND RctDate <=#" & DateSerial(year(date()),
month(date(),0) & "#")

PreBalance: PrevDue - PrevPaid

where
tablename is one of the tables in the source with CustID (since both
tables will have it, you need to qualify the reference)


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Ramesh wrote:
HI,

I have a Receivable table with CustID, DueDate and AmountDue. Another
Receipts Table with CustID, RctDate, AmountPaid.

How can I make a query to display What was collectable each month and
What was actually collected?

No problem with getting monthly collections part. Where i am getting
stuck is that I need to add the outstanding of the previous month for
each month.

Any help please.

Thanks
Ramesh
Click to expand...
Click to expand...
Click to expand...
 
you're welcome, Ramesh ;) happy to help


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


crystal , this is very gracious of you.

Thanks a million. Very helpful.

Ramesh

strive4peace said:
dSum
---

Hi Ramesh,

dSum("expression", "domain", "criteria")

WHERE
- expression is a field or an equation involving one or more fields from
the domain
- domain is a tablename or a queryname
- criteria is how to limit the records (same as the Where clause of an SQL
statement without the word Where)

You must substitute YOUR fieldnames and YOUR tablenames <smile>

if a name contains a space or special character, it must be enclosed in
square brackets

"AmountPaid" and "[AmountPaid]" are both ok
"Receivable Table" is NOT okay, it must be "[Receivable Table]"
(but I did not know the name of your table, it is probably different)

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


thanks crystal.

there was some problem regarding the argumetns in the expression. will
read up on the dsum and try to figure it out. this is a new function for
me.

thanks for your support.

Ramesh

Hi Ramesh,

try this:

PrevDue: dSum("AmountPaid", "[Receivable Table]", "CustID=" &
[tablename].CustID & " AND DueDate <=#" & DateSerial(year(date()),
month(date(),0) & "#")

PrevPaid: dSum("AmountPaid", "[Receipts Table]", "CustID=" &
[tablename].CustID & " AND RctDate <=#" & DateSerial(year(date()),
month(date(),0) & "#")

PreBalance: PrevDue - PrevPaid

where
tablename is one of the tables in the source with CustID (since both
tables will have it, you need to qualify the reference)


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Ramesh wrote:
HI,

I have a Receivable table with CustID, DueDate and AmountDue. Another
Receipts Table with CustID, RctDate, AmountPaid.

How can I make a query to display What was collectable each month and
What was actually collected?

No problem with getting monthly collections part. Where i am getting
stuck is that I need to add the outstanding of the previous month for
each month.

Any help please.

Thanks
Ramesh
Click to expand...
Click to expand...
Click to expand...
 
Back
Top