totals (Do or die situation)

[manish]

dear all

I have a query based on two tables. One table contains the
details of all invoice raised to clients as follows:
"Invoice No"
"Inv date"
"Inv amt"
The other table contains the details of all payments
received against invoices. Sometimes there are two-three
payments against a particular invoice.
For example take an invoice details as
Inv no - 0406/01
Inv dt - 02/02/04
Inv Amt - 10000
Now against this invoice if payment has bee received as
follows:

rec date inv no amt received
10/3/04 0406/01 5000
20/3/04 0406/01 2000
30/3/04 0406/01 3000
Now i have set up a query to relate the invoices and
receipts. Similarly in my reports i have grouped it on the
basis of Inv no.
So my report shows something like this

Inv No. Date Amt Rec Date Amt rec
------ ----- ---- -------- -------
0406/01 02/02/04 10000
10/3/4 5000
20/3/4 2000
30/3/4 3000

Now my problem is that when i derive the total of the
Invoice amount the amount is three times added to the
total even though i have grouped it on invoice no. Thus if
i have five receipts the total amount will increased by
four more times of the amount. What is the solution to
this. Please Help!!!! Urgently required or else my Boss
will Kill me.
Urgently awaiting any response.

Manish

 

[Newbie]

could you change the receipt amt to be a negative so that the addition would
work?
Your query would need to be changed so the receiptcol = -receiptval

HTH

 

[manish]

Dear Newbie
I do not know how changing the receipt column to be
negative will affect the total of Invoice Amount Column??
Maybe I did not understand ur suggestion. So please
clarify and help me out.
Thanks
manish

 

[Duane Hookom]

If you only have one invoice per report then you can just use [Inv Amt]
rather than Sum([Inv Amt]). However if you have multiple invoices per
customer:
Make sure that your report has a group header for the Invoice. Then add a
new text box to the header:
Name: txtInvAmtSum
Control Source: =[Inv Amt]
Running Sum: Over Group ( or Over All)

Then add a text box in the Customer footer:
Control Source:=[ txtInvAmtSum]

 

[Newbie]

Dear Duane
I tried your suggestion, but the running sum does not work.
I will give u the example
I have three invoices in one category.
Inv no inv dt inv amt rec dt rec amt
0404/01 2/2/4 10000 5/2/4 5000
0404/01 2/2/4 10000 6/2/4 5000
0404/02 3/2/4 5000 10/5/4 5000
0404/03 5/2/4 3000 20/5/4 3000

Now in my report i have grouped it firstly on the basis of
customer then on the basis of invoice with the header and
footer as suggested by u. the report looks something like
this.

Inv no. inv dt inv amt rec dt rec amt
0404/01 2/2/4 10000
5/2/4 5000
6/2/4 5000
-----------------------------------------------------
0404/02 3/2/4 10000
10/5/4 10000
-----------------------------------------------------
0404/03 5/2/4 3000 20/5/4 3000
======================================================
33000 23000
======================================================
Now this is where the inv amount is wrong. although it
shows the amount only once in the report however it takes
it into the sum calculation. I even tried the running sum
in the group header but the running sum did not change
with the next invoice. it showed only the current invoices
total amt.
PLEASE HELP!!!!!

-----Original Message-----
If you only have one invoice per report then you can just use [Inv Amt]
rather than Sum([Inv Amt]). However if you have multiple invoices per
customer:
Make sure that your report has a group header for the Invoice. Then add a
new text box to the header:
Name: txtInvAmtSum
Control Source: =[Inv Amt]
Running Sum: Over Group ( or Over All)

Then add a text box in the Customer footer:
Control Source:=[ txtInvAmtSum]

--
Duane Hookom
MS Access MVP

dear all

I have a query based on two tables. One table contains the
details of all invoice raised to clients as follows:
"Invoice No"
"Inv date"
"Inv amt"
The other table contains the details of all payments
received against invoices. Sometimes there are two-three
payments against a particular invoice.
For example take an invoice details as
Inv no - 0406/01
Inv dt - 02/02/04
Inv Amt - 10000
Now against this invoice if payment has bee received as
follows:

rec date inv no amt received
10/3/04 0406/01 5000
20/3/04 0406/01 2000
30/3/04 0406/01 3000
Now i have set up a query to relate the invoices and
receipts. Similarly in my reports i have grouped it on the
basis of Inv no.
So my report shows something like this

Inv No. Date Amt Rec Date Amt rec
------ ----- ---- -------- -------
0406/01 02/02/04 10000
10/3/4 5000
20/3/4 2000
30/3/4 3000

Now my problem is that when i derive the total of the
Invoice amount the amount is three times added to the
total even though i have grouped it on invoice no. Thus if
i have five receipts the total amount will increased by
four more times of the amount. What is the solution to
this. Please Help!!!! Urgently required or else my Boss
will Kill me.
Urgently awaiting any response.

Manish

.

 

[Newbie]

Based on Waynes suggestion here is how I got it to work . . .

In the Invoice Group Header you should have 2 textboxes
based on the InvAmt
1) controlsource = InvAmt
RunningSum = No

2) controlsource =InvAmt
RunningSum = OverGroup (or OverAll)
Name = txtInvAmtSum
Visible = False

In the Customer Footer put a textbox
Control Source:=[txtInvAmtSum]
RunningSum = No

HTH

Dear Duane
I tried your suggestion, but the running sum does not work.
I will give u the example
I have three invoices in one category.
Inv no inv dt inv amt rec dt rec amt
0404/01 2/2/4 10000 5/2/4 5000
0404/01 2/2/4 10000 6/2/4 5000
0404/02 3/2/4 5000 10/5/4 5000
0404/03 5/2/4 3000 20/5/4 3000

Now in my report i have grouped it firstly on the basis of
customer then on the basis of invoice with the header and
footer as suggested by u. the report looks something like
this.

Inv no. inv dt inv amt rec dt rec amt
0404/01 2/2/4 10000
5/2/4 5000
6/2/4 5000
-----------------------------------------------------
0404/02 3/2/4 10000
10/5/4 10000
-----------------------------------------------------
0404/03 5/2/4 3000 20/5/4 3000
======================================================
33000 23000
======================================================
Now this is where the inv amount is wrong. although it
shows the amount only once in the report however it takes
it into the sum calculation. I even tried the running sum
in the group header but the running sum did not change
with the next invoice. it showed only the current invoices
total amt.
PLEASE HELP!!!!!

-----Original Message-----
If you only have one invoice per report then you can just use [Inv Amt]
rather than Sum([Inv Amt]). However if you have multiple invoices per
customer:
Make sure that your report has a group header for the Invoice. Then add a
new text box to the header:
Name: txtInvAmtSum
Control Source: =[Inv Amt]
Running Sum: Over Group ( or Over All)

Then add a text box in the Customer footer:
Control Source:=[ txtInvAmtSum]

--
Duane Hookom
MS Access MVP

dear all

I have a query based on two tables. One table contains the
details of all invoice raised to clients as follows:
"Invoice No"
"Inv date"
"Inv amt"
The other table contains the details of all payments
received against invoices. Sometimes there are two-three
payments against a particular invoice.
For example take an invoice details as
Inv no - 0406/01
Inv dt - 02/02/04
Inv Amt - 10000
Now against this invoice if payment has bee received as
follows:

rec date inv no amt received
10/3/04 0406/01 5000
20/3/04 0406/01 2000
30/3/04 0406/01 3000
Now i have set up a query to relate the invoices and
receipts. Similarly in my reports i have grouped it on the
basis of Inv no.
So my report shows something like this

Inv No. Date Amt Rec Date Amt rec
------ ----- ---- -------- -------
0406/01 02/02/04 10000
10/3/4 5000
20/3/4 2000
30/3/4 3000

Now my problem is that when i derive the total of the
Invoice amount the amount is three times added to the
total even though i have grouped it on invoice no. Thus if
i have five receipts the total amount will increased by
four more times of the amount. What is the solution to
this. Please Help!!!! Urgently required or else my Boss
will Kill me.
Urgently awaiting any response.

Manish

.

 

[Newbie]

Sorry Duane - I got your name wrong

Based on Waynes suggestion here is how I got it to work . . .

In the Invoice Group Header you should have 2 textboxes
based on the InvAmt
1) controlsource = InvAmt
RunningSum = No

2) controlsource =InvAmt
RunningSum = OverGroup (or OverAll)
Name = txtInvAmtSum
Visible = False

In the Customer Footer put a textbox
Control Source:=[txtInvAmtSum]
RunningSum = No

HTH

Dear Duane
I tried your suggestion, but the running sum does not work.
I will give u the example
I have three invoices in one category.
Inv no inv dt inv amt rec dt rec amt
0404/01 2/2/4 10000 5/2/4 5000
0404/01 2/2/4 10000 6/2/4 5000
0404/02 3/2/4 5000 10/5/4 5000
0404/03 5/2/4 3000 20/5/4 3000

Now in my report i have grouped it firstly on the basis of
customer then on the basis of invoice with the header and
footer as suggested by u. the report looks something like
this.

Inv no. inv dt inv amt rec dt rec amt
0404/01 2/2/4 10000
5/2/4 5000
6/2/4 5000
-----------------------------------------------------
0404/02 3/2/4 10000
10/5/4 10000
-----------------------------------------------------
0404/03 5/2/4 3000 20/5/4 3000
======================================================
33000 23000
======================================================
Now this is where the inv amount is wrong. although it
shows the amount only once in the report however it takes
it into the sum calculation. I even tried the running sum
in the group header but the running sum did not change
with the next invoice. it showed only the current invoices
total amt.
PLEASE HELP!!!!!

-----Original Message-----
If you only have one invoice per report then you can just use [Inv Amt]
rather than Sum([Inv Amt]). However if you have multiple invoices per
customer:
Make sure that your report has a group header for the Invoice. Then add a
new text box to the header:
Name: txtInvAmtSum
Control Source: =[Inv Amt]
Running Sum: Over Group ( or Over All)

Then add a text box in the Customer footer:
Control Source:=[ txtInvAmtSum]

--
Duane Hookom
MS Access MVP


dear all

I have a query based on two tables. One table contains the
details of all invoice raised to clients as follows:
"Invoice No"
"Inv date"
"Inv amt"
The other table contains the details of all payments
received against invoices. Sometimes there are two-three
payments against a particular invoice.
For example take an invoice details as
Inv no - 0406/01
Inv dt - 02/02/04
Inv Amt - 10000
Now against this invoice if payment has bee received as
follows:

rec date inv no amt received
10/3/04 0406/01 5000
20/3/04 0406/01 2000
30/3/04 0406/01 3000
Now i have set up a query to relate the invoices and
receipts. Similarly in my reports i have grouped it on the
basis of Inv no.
So my report shows something like this

Inv No. Date Amt Rec Date Amt rec
------ ----- ---- -------- -------
0406/01 02/02/04 10000
10/3/4 5000
20/3/4 2000
30/3/4 3000

Now my problem is that when i derive the total of the
Invoice amount the amount is three times added to the
total even though i have grouped it on invoice no. Thus if
i have five receipts the total amount will increased by
four more times of the amount. What is the solution to
this. Please Help!!!! Urgently required or else my Boss
will Kill me.
Urgently awaiting any response.

Manish


.

 

[Duane Hookom]

What is the control source of the invoices total in the customer footer?
It looks like your rec amount total is not adding correctly. Shouldn't the
total be 18,000?
What do you mean by "category"?
Are you displaying your running sum so you can view the total in your group
header?

--
Duane Hookom
MS Access MVP

Dear Duane
I tried your suggestion, but the running sum does not work.
I will give u the example
I have three invoices in one category.
Inv no inv dt inv amt rec dt rec amt
0404/01 2/2/4 10000 5/2/4 5000
0404/01 2/2/4 10000 6/2/4 5000
0404/02 3/2/4 5000 10/5/4 5000
0404/03 5/2/4 3000 20/5/4 3000

Now in my report i have grouped it firstly on the basis of
customer then on the basis of invoice with the header and
footer as suggested by u. the report looks something like
this.

Inv no. inv dt inv amt rec dt rec amt
0404/01 2/2/4 10000
5/2/4 5000
6/2/4 5000
-----------------------------------------------------
0404/02 3/2/4 10000
10/5/4 10000
-----------------------------------------------------
0404/03 5/2/4 3000 20/5/4 3000
======================================================
33000 23000
======================================================
Now this is where the inv amount is wrong. although it
shows the amount only once in the report however it takes
it into the sum calculation. I even tried the running sum
in the group header but the running sum did not change
with the next invoice. it showed only the current invoices
total amt.
PLEASE HELP!!!!!

-----Original Message-----
If you only have one invoice per report then you can just use [Inv Amt]
rather than Sum([Inv Amt]). However if you have multiple invoices per
customer:
Make sure that your report has a group header for the Invoice. Then add a
new text box to the header:
Name: txtInvAmtSum
Control Source: =[Inv Amt]
Running Sum: Over Group ( or Over All)

Then add a text box in the Customer footer:
Control Source:=[ txtInvAmtSum]

--
Duane Hookom
MS Access MVP

dear all

I have a query based on two tables. One table contains the
details of all invoice raised to clients as follows:
"Invoice No"
"Inv date"
"Inv amt"
The other table contains the details of all payments
received against invoices. Sometimes there are two-three
payments against a particular invoice.
For example take an invoice details as
Inv no - 0406/01
Inv dt - 02/02/04
Inv Amt - 10000
Now against this invoice if payment has bee received as
follows:

rec date inv no amt received
10/3/04 0406/01 5000
20/3/04 0406/01 2000
30/3/04 0406/01 3000
Now i have set up a query to relate the invoices and
receipts. Similarly in my reports i have grouped it on the
basis of Inv no.
So my report shows something like this

Inv No. Date Amt Rec Date Amt rec
------ ----- ---- -------- -------
0406/01 02/02/04 10000
10/3/4 5000
20/3/4 2000
30/3/4 3000

Now my problem is that when i derive the total of the
Invoice amount the amount is three times added to the
total even though i have grouped it on invoice no. Thus if
i have five receipts the total amount will increased by
four more times of the amount. What is the solution to
this. Please Help!!!! Urgently required or else my Boss
will Kill me.
Urgently awaiting any response.

Manish

.

 

[manish]

Dear all,
Firstly thanks for attending to my request. However I
still couldn't work it out. If i follow the suggestion of
having two textboxes in the Invoice Group header and make
one of them on the basis of the maintextbox then too the
second box doesn't show the running sum but only the
invoice amount under that group. The running sum is not
working.
Also category is just another group.
The main groups are

Company Name - contains the co. name
Project - contains a particular project of a company
Category - In a particular project there can be two
categories such as Contract, Extra work.
Inv No - the basic no of invoices.

So why is my running sum not working.
The details of reciepts are given in Detail Row.
The Invoice details in the Inv header row.

Please ignore the calculation. I was just making a point.
It was my mistake.
So how do i do it. Please Help.
Manish

-----Original Message-----
Based on Waynes suggestion here is how I got it to work . . .

In the Invoice Group Header you should have 2 textboxes
based on the InvAmt
1) controlsource = InvAmt
RunningSum = No

2) controlsource =InvAmt
RunningSum = OverGroup (or OverAll)
Name = txtInvAmtSum
Visible = False

In the Customer Footer put a textbox
Control Source:=[txtInvAmtSum]
RunningSum = No

HTH

Dear Duane
I tried your suggestion, but the running sum does not work.
I will give u the example
I have three invoices in one category.
Inv no inv dt inv amt rec dt rec amt
0404/01 2/2/4 10000 5/2/4 5000
0404/01 2/2/4 10000 6/2/4 5000
0404/02 3/2/4 5000 10/5/4 5000
0404/03 5/2/4 3000 20/5/4 3000

Now in my report i have grouped it firstly on the basis of
customer then on the basis of invoice with the header and
footer as suggested by u. the report looks something like
this.

Inv no. inv dt inv amt rec dt rec amt
0404/01 2/2/4 10000
5/2/4 5000
6/2/4 5000
-----------------------------------------------------
0404/02 3/2/4 10000
10/5/4 10000
-----------------------------------------------------
0404/03 5/2/4 3000 20/5/4 3000
======================================================
33000 23000
======================================================
Now this is where the inv amount is wrong. although it
shows the amount only once in the report however it takes
it into the sum calculation. I even tried the running sum
in the group header but the running sum did not change
with the next invoice. it showed only the current invoices
total amt.
PLEASE HELP!!!!!

-----Original Message-----
If you only have one invoice per report then you can

just
use [Inv Amt]

rather than Sum([Inv Amt]). However if you have

multiple
invoices per

customer:
Make sure that your report has a group header for the Invoice. Then add a
new text box to the header:
Name: txtInvAmtSum
Control Source: =[Inv Amt]
Running Sum: Over Group ( or Over All)

Then add a text box in the Customer footer:
Control Source:=[ txtInvAmtSum]

--
Duane Hookom
MS Access MVP


dear all

I have a query based on two tables. One table

contains
the

details of all invoice raised to clients as follows:
"Invoice No"
"Inv date"
"Inv amt"
The other table contains the details of all payments
received against invoices. Sometimes there are two- three
payments against a particular invoice.
For example take an invoice details as
Inv no - 0406/01
Inv dt - 02/02/04
Inv Amt - 10000
Now against this invoice if payment has bee received as
follows:

rec date inv no amt received
10/3/04 0406/01 5000
20/3/04 0406/01 2000
30/3/04 0406/01 3000
Now i have set up a query to relate the invoices and
receipts. Similarly in my reports i have grouped it

on
the

basis of Inv no.
So my report shows something like this

Inv No. Date Amt Rec Date Amt rec
------ ----- ---- -------- -------
0406/01 02/02/04 10000
10/3/4 5000
20/3/4 2000
30/3/4 3000

Now my problem is that when i derive the total of the
Invoice amount the amount is three times added to the
total even though i have grouped it on invoice no.

Thus
if

i have five receipts the total amount will increased by
four more times of the amount. What is the solution to
this. Please Help!!!! Urgently required or else my Boss
will Kill me.
Urgently awaiting any response.

Manish


.

.

 

[Duane Hookom]

I have no idea what you mean by "one of them on the basis of the maintextbox
then too the second box doesn't show the running sum but only the invoice
amount under that group."

Add a text box in your Invoice Group Header section and set it:
Name: txtInvAmtSum
Control Source: [Inv Amt]
Running Sum: Over Group ( or Over All)
FontColor: make it red so it stands out. Make it invisible later

Then add a text box in the Category footer:
Control Source:=[ txtInvAmtSum]


--
Duane Hookom
MS Access MVP

Dear all,
Firstly thanks for attending to my request. However I
still couldn't work it out. If i follow the suggestion of
having two textboxes in the Invoice Group header and make
one of them on the basis of the maintextbox then too the
second box doesn't show the running sum but only the
invoice amount under that group. The running sum is not
working.
Also category is just another group.
The main groups are

Company Name - contains the co. name
Project - contains a particular project of a company
Category - In a particular project there can be two
categories such as Contract, Extra work.
Inv No - the basic no of invoices.

So why is my running sum not working.
The details of reciepts are given in Detail Row.
The Invoice details in the Inv header row.

Please ignore the calculation. I was just making a point.
It was my mistake.
So how do i do it. Please Help.
Manish

-----Original Message-----
Based on Waynes suggestion here is how I got it to work . . .

In the Invoice Group Header you should have 2 textboxes
based on the InvAmt
1) controlsource = InvAmt
RunningSum = No

2) controlsource =InvAmt
RunningSum = OverGroup (or OverAll)
Name = txtInvAmtSum
Visible = False

In the Customer Footer put a textbox
Control Source:=[txtInvAmtSum]
RunningSum = No

HTH

Dear Duane
I tried your suggestion, but the running sum does not work.
I will give u the example
I have three invoices in one category.
Inv no inv dt inv amt rec dt rec amt
0404/01 2/2/4 10000 5/2/4 5000
0404/01 2/2/4 10000 6/2/4 5000
0404/02 3/2/4 5000 10/5/4 5000
0404/03 5/2/4 3000 20/5/4 3000

Now in my report i have grouped it firstly on the basis of
customer then on the basis of invoice with the header and
footer as suggested by u. the report looks something like
this.

Inv no. inv dt inv amt rec dt rec amt
0404/01 2/2/4 10000
5/2/4 5000
6/2/4 5000
-----------------------------------------------------
0404/02 3/2/4 10000
10/5/4 10000
-----------------------------------------------------
0404/03 5/2/4 3000 20/5/4 3000
======================================================
33000 23000
======================================================
Now this is where the inv amount is wrong. although it
shows the amount only once in the report however it takes
it into the sum calculation. I even tried the running sum
in the group header but the running sum did not change
with the next invoice. it showed only the current invoices
total amt.
PLEASE HELP!!!!!


-----Original Message-----
If you only have one invoice per report then you can just
use [Inv Amt]
rather than Sum([Inv Amt]). However if you have multiple
invoices per
customer:
Make sure that your report has a group header for the
Invoice. Then add a
new text box to the header:
Name: txtInvAmtSum
Control Source: =[Inv Amt]
Running Sum: Over Group ( or Over All)

Then add a text box in the Customer footer:
Control Source:=[ txtInvAmtSum]

--
Duane Hookom
MS Access MVP


message
dear all

I have a query based on two tables. One table contains
the
details of all invoice raised to clients as follows:
"Invoice No"
"Inv date"
"Inv amt"
The other table contains the details of all payments
received against invoices. Sometimes there are two- three
payments against a particular invoice.
For example take an invoice details as
Inv no - 0406/01
Inv dt - 02/02/04
Inv Amt - 10000
Now against this invoice if payment has bee received as
follows:

rec date inv no amt received
10/3/04 0406/01 5000
20/3/04 0406/01 2000
30/3/04 0406/01 3000
Now i have set up a query to relate the invoices and
receipts. Similarly in my reports i have grouped it on
the
basis of Inv no.
So my report shows something like this

Inv No. Date Amt Rec Date Amt rec
------ ----- ---- -------- -------
0406/01 02/02/04 10000
10/3/4 5000
20/3/4 2000
30/3/4 3000

Now my problem is that when i derive the total of the
Invoice amount the amount is three times added to the
total even though i have grouped it on invoice no. Thus
if
i have five receipts the total amount will increased by
four more times of the amount. What is the solution to
this. Please Help!!!! Urgently required or else my Boss
will Kill me.
Urgently awaiting any response.

Manish


.

.