Group Total REPOST

[Drew]

Sorry if this is a repost, I noticed that I didn't give have a subject on
the last post, so here is the REPOST.

I have a report that shows us credit card transactions grouped by Vendor.
The data comes from SQL Server and I am using an ADP. The data structure
looks like this,

UID - auto incrementing ID number
CardholderID - foreign key from Cardholder table
PONum - Purchase Order #
PODate - Date of PO
Vendor - Vendor name
Description - Brief description
Amount - Amount of charge/carryover/credit/prior carryover
ObjectCode - the coding for the item
Complete - bit field for flagging Completed charge
Carryover - bit field for flagging charge that has been carried over
Credit - bit field for flagging a credit
PriorCO - bit field for flagging a carryover from previous statement that
has been charged.

I have the report running fine, except now the main user wants to add a
Group Total to the report. The problem is that you cannot simply add all of
these together. The equation looks something like this,

Group Total = (Complete Amount + Prior Carryover Amount) - Credit Amount

How can I make this happen?

Thanks,
Drew Laing

 

[Duane Hookom]

Did you try:
=Sum( (Complete Amount + Prior Carryover Amount) - Credit Amount )

 

[John Spencer (MVP)]

SUM(([Complete Amount] * Complete) + ([Prior Carryover Amount] * Carryover) -
([Credit Amount] * Credit))

If the bit fields are 0 and 1 (1 being "True") then the above should work. If
"true" is not 1 then you will have to adjust the formula.

 

[Drew]

I'm sorry, but the equation has thrown people off. The equation in my first
post,

Group Total = (Complete Amount + Prior Carryover Amount) - Credit Amount

is just a visual representation of the equation I need to do. The way the
database is setup, there are 4 bit fields, Complete, Carryover, Credit and
PriorCO. If Complete = 1 then the Amount column holds the completed charge
amount. If Credit = 1, then the Amount column holds the credit amount.

So I need to sum all records where Complete = 1 and PriorCO = 1 and then
subtract all amounts where Credit = 1.

Thanks,
Drew

 

[Duane Hookom]

Why don't you just provide some sample records and how & why you would
expect them to total? This would clear things up fairly quickly.

 

[Drew]

Alrighty... here you go... for sake of simplicity, I will omit some of the
columns...

Vendor Amount Complete Carryover Credit PriorCO
ACME 434.00 1 0 0 0
ACME 30.00 0 1 0 0
ACME 4.00 0 0 1 0
ACME 256.00 0 0 0 1
ACME 100.00 0 1 0 0
ACME 50.00 1 0 0 0
ACME 38.00 0 0 0 1
ACME 148.00 1 0 0 0
ACME 22.00 0 0 0 1
ACME 125.00 1 0 0 0

OK... Below is the figures for the above records,

4 Complete transactions totaling 757.00
2 Carryovers totaling 130.00
1 Credit totaling 4.00
3 PriorCO totaling 316.00

So if I use the equation, Total = (Complete + PriorCO) - Credit, it will
look like this,

(757+316)-4 = 1069.00

Thanks,
Drew

 

[Duane Hookom]

This type of reply is so much better.
Complete transactions is:
=Sum(Complete)
The Sum of complete transactions is:
=Sum(Complete * Amount)

You could try:
=Sum(Abs(Complete Or PriorCo) * Amount) - Sum(Credit * Amount)

If only one of the four fields can be 1 and the others 0, then I would have
considered creating a single field to store "complete" or "carryover" or
"Credit" or "PriorCo". This would be more normalized.

 

[Drew]

OK you have lost me about the normalizing. Do you mean have a structure
like below,

Vendor Complete Carryover Credit PriorCO
ACME 234.00 0 0 0
ACME 0 0 4.00 0
ACME 0 12.34 0 0
ACME 100.00 0 0 0

I was siding on the space issue. Since each record has only one selected,
then there will be empty space not being used. Since there are 4 bit fields
per record, this is stored as a byte. If I used int datatype instead, then
I would have 4+4+4+4 = 16 bytes per record (disregarding the Amount column,
which would be deleted). Therefore it would be smaller footprint. But then
again, I may be completely wrong...

I would like to find a solution where I wouldn't have to re-develop the
application. My app starts with an Excel file that is DTS'd into SQL Server
then spit back out into the ADP.

But, if the only solution would be to re-do this column, then I guess that
would work.

Thanks,
Drew

 

[Duane Hookom]

Did you try the expressions that I suggested?

My thought on normalization would be
Vendor Amount TranType
ACME 234.00 Complete
ACME 4.00 Credit
ACME 12.34 Carryover
ACME 100.00 Complete

 

[Drew]

I see what you are talking about on the normalization now. I will see what
can be done to change the current structure

Using your expression, I get the following error,

The expression 'Sum(Abs([Complete] Or [PriorCO])*[Amount]' is invalid

Aggregate functions are only allowed on output fields of the Record Source.

Thanks,
Drew

 

[Duane Hookom]

Are Complete, PriorCo, and Amount all fields in your report's record source?

--
Duane Hookom
MS Access MVP
--

I see what you are talking about on the normalization now. I will see what
can be done to change the current structure

Using your expression, I get the following error,

The expression 'Sum(Abs([Complete] Or [PriorCO])*[Amount]' is invalid

Aggregate functions are only allowed on output fields of the Record
Source.

Thanks,
Drew

Did you try the expressions that I suggested?

My thought on normalization would be
Vendor Amount TranType
ACME 234.00 Complete
ACME 4.00 Credit
ACME 12.34 Carryover
ACME 100.00 Complete

 

[Drew]

Yep... I think the problem is that OR cannot be used with an ADP file. I
read this somewhere on the internet while researching this error.

Is there anything else that would work?

Thanks,
Drew

Are Complete, PriorCo, and Amount all fields in your report's record
source?

--
Duane Hookom
MS Access MVP
--

I see what you are talking about on the normalization now. I will see
what can be done to change the current structure

Using your expression, I get the following error,

The expression 'Sum(Abs([Complete] Or [PriorCO])*[Amount]' is invalid

Aggregate functions are only allowed on output fields of the Record
Source.

Thanks,
Drew

Did you try the expressions that I suggested?

My thought on normalization would be
Vendor Amount TranType
ACME 234.00 Complete
ACME 4.00 Credit
ACME 12.34 Carryover
ACME 100.00 Complete
--
Duane Hookom
MS Access MVP
--

OK you have lost me about the normalizing. Do you mean have a
structure like below,

Vendor Complete Carryover Credit PriorCO
ACME 234.00 0 0 0
ACME 0 0 4.00 0
ACME 0 12.34 0 0
ACME 100.00 0 0 0

I was siding on the space issue. Since each record has only one
selected, then there will be empty space not being used. Since there
are 4 bit fields per record, this is stored as a byte. If I used int
datatype instead, then I would have 4+4+4+4 = 16 bytes per record
(disregarding the Amount column, which would be deleted). Therefore it
would be smaller footprint. But then again, I may be completely
wrong...

I would like to find a solution where I wouldn't have to re-develop the
application. My app starts with an Excel file that is DTS'd into SQL
Server then spit back out into the ADP.

But, if the only solution would be to re-do this column, then I guess
that would work.

Thanks,
Drew


This type of reply is so much better.
Complete transactions is:
=Sum(Complete)
The Sum of complete transactions is:
=Sum(Complete * Amount)

You could try:
=Sum(Abs(Complete Or PriorCo) * Amount) - Sum(Credit * Amount)

If only one of the four fields can be 1 and the others 0, then I would
have considered creating a single field to store "complete" or
"carryover" or "Credit" or "PriorCo". This would be more normalized.

--
Duane Hookom
MS Access MVP
--

Alrighty... here you go... for sake of simplicity, I will omit some
of the columns...

Vendor Amount Complete Carryover Credit PriorCO
ACME 434.00 1 0 0 0
ACME 30.00 0 1 0 0
ACME 4.00 0 0 1 0
ACME 256.00 0 0 0 1
ACME 100.00 0 1 0 0
ACME 50.00 1 0 0 0
ACME 38.00 0 0 0 1
ACME 148.00 1 0 0 0
ACME 22.00 0 0 0 1
ACME 125.00 1 0 0 0

OK... Below is the figures for the above records,

4 Complete transactions totaling 757.00
2 Carryovers totaling 130.00
1 Credit totaling 4.00
3 PriorCO totaling 316.00

So if I use the equation, Total = (Complete + PriorCO) - Credit, it
will look like this,

(757+316)-4 = 1069.00

Thanks,
Drew

Why don't you just provide some sample records and how & why you
would expect them to total? This would clear things up fairly
quickly.

--
Duane Hookom
MS Access MVP


message I'm sorry, but the equation has thrown people off. The equation in
my first post,

Group Total = (Complete Amount + Prior Carryover Amount) - Credit
Amount

is just a visual representation of the equation I need to do. The
way the database is setup, there are 4 bit fields, Complete,
Carryover, Credit and PriorCO. If Complete = 1 then the Amount
column holds the completed charge amount. If Credit = 1, then the
Amount column holds the credit amount.

So I need to sum all records where Complete = 1 and PriorCO = 1 and
then subtract all amounts where Credit = 1.

Thanks,
Drew


Did you try:
=Sum( (Complete Amount + Prior Carryover Amount) - Credit
mount )

--
Duane Hookom
MS Access MVP
--

message Sorry if this is a repost, I noticed that I didn't give have a
subject on the last post, so here is the REPOST.

I have a report that shows us credit card transactions grouped by
Vendor.
The data comes from SQL Server and I am using an ADP. The data
structure
looks like this,

UID - auto incrementing ID number
CardholderID - foreign key from Cardholder table
PONum - Purchase Order #
PODate - Date of PO
Vendor - Vendor name
Description - Brief description
Amount - Amount of charge/carryover/credit/prior carryover
ObjectCode - the coding for the item
Complete - bit field for flagging Completed charge
Carryover - bit field for flagging charge that has been carried
over
Credit - bit field for flagging a credit
PriorCO - bit field for flagging a carryover from previous
statement that
has been charged.

I have the report running fine, except now the main user wants to
add a
Group Total to the report. The problem is that you cannot simply
add all of
these together. The equation looks something like this,

Group Total = (Complete Amount + Prior Carryover Amount) - Credit
Amount

How can I make this happen?

Thanks,
Drew Laing

 

[Duane Hookom]

=Sum(Abs(Complete=1 Or PriorCo=1) * Amount) - Sum(Credit * Amount)
or
=Sum(IIf(Complete=1 Or PriorCo=1),0,Amount) - Sum(Credit * Amount)


--
Duane Hookom
MS Access MVP
--

Yep... I think the problem is that OR cannot be used with an ADP file. I
read this somewhere on the internet while researching this error.

Is there anything else that would work?

Thanks,
Drew

Are Complete, PriorCo, and Amount all fields in your report's record
source?

--
Duane Hookom
MS Access MVP
--

I see what you are talking about on the normalization now. I will see
what can be done to change the current structure

Using your expression, I get the following error,

The expression 'Sum(Abs([Complete] Or [PriorCO])*[Amount]' is invalid

Aggregate functions are only allowed on output fields of the Record
Source.

Thanks,
Drew


Did you try the expressions that I suggested?

My thought on normalization would be
Vendor Amount TranType
ACME 234.00 Complete
ACME 4.00 Credit
ACME 12.34 Carryover
ACME 100.00 Complete
--
Duane Hookom
MS Access MVP
--

OK you have lost me about the normalizing. Do you mean have a
structure like below,

Vendor Complete Carryover Credit PriorCO
ACME 234.00 0 0 0
ACME 0 0 4.00 0
ACME 0 12.34 0 0
ACME 100.00 0 0 0

I was siding on the space issue. Since each record has only one
selected, then there will be empty space not being used. Since there
are 4 bit fields per record, this is stored as a byte. If I used int
datatype instead, then I would have 4+4+4+4 = 16 bytes per record
(disregarding the Amount column, which would be deleted). Therefore
it would be smaller footprint. But then again, I may be completely
wrong...

I would like to find a solution where I wouldn't have to re-develop
the application. My app starts with an Excel file that is DTS'd into
SQL Server then spit back out into the ADP.

But, if the only solution would be to re-do this column, then I guess
that would work.

Thanks,
Drew


This type of reply is so much better.
Complete transactions is:
=Sum(Complete)
The Sum of complete transactions is:
=Sum(Complete * Amount)

You could try:
=Sum(Abs(Complete Or PriorCo) * Amount) - Sum(Credit * Amount)

If only one of the four fields can be 1 and the others 0, then I
would have considered creating a single field to store "complete" or
"carryover" or "Credit" or "PriorCo". This would be more normalized.

--
Duane Hookom
MS Access MVP
--

Alrighty... here you go... for sake of simplicity, I will omit some
of the columns...

Vendor Amount Complete Carryover Credit PriorCO
ACME 434.00 1 0 0 0
ACME 30.00 0 1 0 0
ACME 4.00 0 0 1
0
ACME 256.00 0 0 0 1
ACME 100.00 0 1 0 0
ACME 50.00 1 0 0 0
ACME 38.00 0 0 0 1
ACME 148.00 1 0 0 0
ACME 22.00 0 0 0 1
ACME 125.00 1 0 0 0

OK... Below is the figures for the above records,

4 Complete transactions totaling 757.00
2 Carryovers totaling 130.00
1 Credit totaling 4.00
3 PriorCO totaling 316.00

So if I use the equation, Total = (Complete + PriorCO) - Credit, it
will look like this,

(757+316)-4 = 1069.00

Thanks,
Drew

Why don't you just provide some sample records and how & why you
would expect them to total? This would clear things up fairly
quickly.

--
Duane Hookom
MS Access MVP


message I'm sorry, but the equation has thrown people off. The equation
in my first post,

Group Total = (Complete Amount + Prior Carryover Amount) - Credit
Amount

is just a visual representation of the equation I need to do. The
way the database is setup, there are 4 bit fields, Complete,
Carryover, Credit and PriorCO. If Complete = 1 then the Amount
column holds the completed charge amount. If Credit = 1, then the
Amount column holds the credit amount.

So I need to sum all records where Complete = 1 and PriorCO = 1
and then subtract all amounts where Credit = 1.

Thanks,
Drew


Did you try:
=Sum( (Complete Amount + Prior Carryover Amount) - Credit
ount )

--
Duane Hookom
MS Access MVP
--

message Sorry if this is a repost, I noticed that I didn't give have a
subject on the last post, so here is the REPOST.

I have a report that shows us credit card transactions grouped
by Vendor.
The data comes from SQL Server and I am using an ADP. The data
structure
looks like this,

UID - auto incrementing ID number
CardholderID - foreign key from Cardholder table
PONum - Purchase Order #
PODate - Date of PO
Vendor - Vendor name
Description - Brief description
Amount - Amount of charge/carryover/credit/prior carryover
ObjectCode - the coding for the item
Complete - bit field for flagging Completed charge
Carryover - bit field for flagging charge that has been carried
over
Credit - bit field for flagging a credit
PriorCO - bit field for flagging a carryover from previous
statement that
has been charged.

I have the report running fine, except now the main user wants
to add a
Group Total to the report. The problem is that you cannot
simply add all of
these together. The equation looks something like this,

Group Total = (Complete Amount + Prior Carryover Amount) -
Credit Amount

How can I make this happen?

Thanks,
Drew Laing