Monthly outstanding query

[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

 

[strive4peace]

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]

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
*

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

 

[strive4peace]

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
*

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

 

[Ramesh]

crystal , this is very gracious of you.

Thanks a million. Very helpful.

Ramesh

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

 

[strive4peace]

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

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