Totals on crosstab

J

Jean-Marie

Hello guys.

I am building a database to manage a fleet of transport
vehicles. I have among others these tables
tblVehicles (LicensePlate, AcqDate, HorsePower, MotorType)
tblRevenue (IDRevenue, DateRev, AmountRev, VehicleRev,
DriverRev, CommentRev)
tblExpenses (IDExpenses, DateExp, AmountExp,
CostCenterExp, CommentExp)
IDRevenue and IDExpense are AutoNumber.
Vehicles are grouped in Pools under the supervision of a
lead driver
I need to build many summary reports to display these
information
Pools
Vehicles
Weeks Revenue Expenses
Week1 Rev1 Exp1
Week2 Rev2 Exp2

Weekn Revn Expn
Total Total(Rev) Total(Exp)


I can build a Union query between tblRevenue and
tblExpense to retrieve the following: Pools, Vehicles,
Weeks, Amount, Type (Revenue or Expense)
Then build a crosstab query on the result to get theses
fields: Pools, Vehicles, Weeks, Revenue, Expenses.
Finally build a report based on the crosstab query.
My problem is that I am unable to calculate the totals
for Revenue and Expenses. I tried something like Sum
([Revenue]) but it won't work.
Hope I have provided enough clarity in this.
My system is Access 2000 and Win XP Pro
Can someone help me with this? Or put me on a better
track?

Jean-Marie
Douala, Cameroon.
 
D

Duane Hookom

I'm not sure how you can report this information together since your Revenue
and Expense tables are not related to Vehicles.
 
J

Jean-Marie

Thank you Duane for your reply.
Sorry for not having indicated that in my first post, in
fact tblRevenue is related to tblVehicles through the
field VehicleRev.
In tblExpenses, CostCenterExp is related to tblVehicles
(a Cost Center belong to a vehicle, which can have more
than one Cost Center).
The structure of these tables looks like this:
tblVehicles (LicensePlate, AcqDate, HorsePower, MotorType)
tblRevenue (IDRevenue, DateRev, AmountRev, #LicensePlate,
DriverRev, CommentRev)
tblExpenses (IDExpenses, DateExp, AmountExp,
#CostCenterExp, CommentExp)
tblCostCenter(IDCostCenter, CodeCC, NameCC, #LicensePlate)
Hope this helps to understand my problem.

Jean-Marie.
-----Original Message-----
I'm not sure how you can report this information together since your Revenue
and Expense tables are not related to Vehicles.

--
Duane Hookom
MS Access MVP


Hello guys.

I am building a database to manage a fleet of transport
vehicles. I have among others these tables
tblVehicles (LicensePlate, AcqDate, HorsePower, MotorType)
tblRevenue (IDRevenue, DateRev, AmountRev, VehicleRev,
DriverRev, CommentRev)
tblExpenses (IDExpenses, DateExp, AmountExp,
CostCenterExp, CommentExp)
IDRevenue and IDExpense are AutoNumber.
Vehicles are grouped in Pools under the supervision of a
lead driver
I need to build many summary reports to display these
information
Pools
Vehicles
Weeks Revenue Expenses
Week1 Rev1 Exp1
Week2 Rev2 Exp2

Weekn Revn Expn
Total Total(Rev) Total(Exp)


I can build a Union query between tblRevenue and
tblExpense to retrieve the following: Pools, Vehicles,
Weeks, Amount, Type (Revenue or Expense)
Then build a crosstab query on the result to get theses
fields: Pools, Vehicles, Weeks, Revenue, Expenses.
Finally build a report based on the crosstab query.
My problem is that I am unable to calculate the totals
for Revenue and Expenses. I tried something like Sum
([Revenue]) but it won't work.
Hope I have provided enough clarity in this.
My system is Access 2000 and Win XP Pro
Can someone help me with this? Or put me on a better
track?

Jean-Marie
Douala, Cameroon.
Click to expand...


.
Click to expand...
 
D

Duane Hookom

Can you describe what you mean by "I am unable to calculate the totals for
Revenue and Expenses. I tried something like Sum([Revenue]) but it won't
work". What exactly did you try and where? Also what do you mean by "won't
work"?

When you view your report's record source in datasheet view, do you see a
column named Revenue? Is the column left or right aligned?

--
Duane Hookom
MS Access MVP
--

Jean-Marie said:
Thank you Duane for your reply.
Sorry for not having indicated that in my first post, in
fact tblRevenue is related to tblVehicles through the
field VehicleRev.
In tblExpenses, CostCenterExp is related to tblVehicles
(a Cost Center belong to a vehicle, which can have more
than one Cost Center).
The structure of these tables looks like this:
tblVehicles (LicensePlate, AcqDate, HorsePower, MotorType)
tblRevenue (IDRevenue, DateRev, AmountRev, #LicensePlate,
DriverRev, CommentRev)
tblExpenses (IDExpenses, DateExp, AmountExp,
#CostCenterExp, CommentExp)
tblCostCenter(IDCostCenter, CodeCC, NameCC, #LicensePlate)
Hope this helps to understand my problem.

Jean-Marie.
-----Original Message-----
I'm not sure how you can report this information together since your Revenue
and Expense tables are not related to Vehicles.

--
Duane Hookom
MS Access MVP


Hello guys.

I am building a database to manage a fleet of transport
vehicles. I have among others these tables
tblVehicles (LicensePlate, AcqDate, HorsePower, MotorType)
tblRevenue (IDRevenue, DateRev, AmountRev, VehicleRev,
DriverRev, CommentRev)
tblExpenses (IDExpenses, DateExp, AmountExp,
CostCenterExp, CommentExp)
IDRevenue and IDExpense are AutoNumber.
Vehicles are grouped in Pools under the supervision of a
lead driver
I need to build many summary reports to display these
information
Pools
Vehicles
Weeks Revenue Expenses
Week1 Rev1 Exp1
Week2 Rev2 Exp2

Weekn Revn Expn
Total Total(Rev) Total(Exp)


I can build a Union query between tblRevenue and
tblExpense to retrieve the following: Pools, Vehicles,
Weeks, Amount, Type (Revenue or Expense)
Then build a crosstab query on the result to get theses
fields: Pools, Vehicles, Weeks, Revenue, Expenses.
Finally build a report based on the crosstab query.
My problem is that I am unable to calculate the totals
for Revenue and Expenses. I tried something like Sum
([Revenue]) but it won't work.
Hope I have provided enough clarity in this.
My system is Access 2000 and Win XP Pro
Can someone help me with this? Or put me on a better
track?

Jean-Marie
Douala, Cameroon.
Click to expand...


.
Click to expand...
Click to expand...
 
J

Jean-Marie

Many thanks Duane.
Please accept my apologies for not replying right away. I
had to go to a remote site for a business trip. In
addition, the program I am developing is a off-the-job
task and I can not work on it from the office; and anyway
am not allowed to do any post from the office

My report looks like this
Pool: Pool Name
*********************************************************
Vehicle: Vehicle plate number (Vehicle group header)
**********************************************************
Week # Revenue Expense
------------------------------------------------
Week1 Rev1 Exp1
...... ...... .......
weekn Revn Expn
******************************************************
Total Sum(Rev) Sum(Exp) (Vehicle
group footer)
******************************************************
Sum(Rev) and Sum(Exp) are text boxes that I want to
display the sum of revenue and sum of expenses for the
vehicle.
I don't know how to achieve it.
I first set their Control Source to Revenue and Expense
fields and in preview they displayed the value of those
fields for Week1
I then set their Control Source properties to Sum
([Revenue]) and Sum([Expense]) an got a syntax error
message.
In my report they are left aligned, so I guess they are
not numbers as they should be.

In the meantime I copied my DB and changed the structure.
I merged the tblExpenses and tblRevenue in a new
tblTransactions (IDTransactions (PK), dtmDateTrans,
curAmountTrans, Vehicle (FK), cboTypeTrans, Comments).
The combo cboTypeTrans indicates whether the transaction
is an Expense or Revenue. With this new structure I don't
need the Union query anymore and the report to displays
the way I want.
I would however like to know what I should have done in
my first DB.

Thanks again.

Jean-Marie
Douala-Cameroon
-----Original Message-----
Can you describe what you mean by "I am unable to calculate the totals for
Revenue and Expenses. I tried something like Sum ([Revenue]) but it won't
work". What exactly did you try and where? Also what do you mean by "won't
work"?

When you view your report's record source in datasheet view, do you see a
column named Revenue? Is the column left or right aligned?

--
Duane Hookom
MS Access MVP
--

Thank you Duane for your reply.
Sorry for not having indicated that in my first post, in
fact tblRevenue is related to tblVehicles through the
field VehicleRev.
In tblExpenses, CostCenterExp is related to tblVehicles
(a Cost Center belong to a vehicle, which can have more
than one Cost Center).
The structure of these tables looks like this:
tblVehicles (LicensePlate, AcqDate, HorsePower, MotorType)
tblRevenue (IDRevenue, DateRev, AmountRev, #LicensePlate,
DriverRev, CommentRev)
tblExpenses (IDExpenses, DateExp, AmountExp,
#CostCenterExp, CommentExp)
tblCostCenter(IDCostCenter, CodeCC, NameCC, #LicensePlate)
Hope this helps to understand my problem.

Jean-Marie.
-----Original Message-----
I'm not sure how you can report this information together since your Revenue
and Expense tables are not related to Vehicles.

--
Duane Hookom
MS Access MVP


"Jean-Marie" <[email protected]>
Click to expand...
wrote
in message
Hello guys.

I am building a database to manage a fleet of transport
vehicles. I have among others these tables
tblVehicles (LicensePlate, AcqDate, HorsePower, MotorType)
tblRevenue (IDRevenue, DateRev, AmountRev, VehicleRev,
DriverRev, CommentRev)
tblExpenses (IDExpenses, DateExp, AmountExp,
CostCenterExp, CommentExp)
IDRevenue and IDExpense are AutoNumber.
Vehicles are grouped in Pools under the supervision
Click to expand...
of
a
lead driver
I need to build many summary reports to display these
information
Pools
Vehicles
Weeks Revenue Expenses
Week1 Rev1 Exp1
Week2 Rev2 Exp2

Weekn Revn Expn
Total Total(Rev) Total(Exp)


I can build a Union query between tblRevenue and
tblExpense to retrieve the following: Pools, Vehicles,
Weeks, Amount, Type (Revenue or Expense)
Then build a crosstab query on the result to get theses
fields: Pools, Vehicles, Weeks, Revenue, Expenses.
Finally build a report based on the crosstab query.
My problem is that I am unable to calculate the totals
for Revenue and Expenses. I tried something like Sum
([Revenue]) but it won't work.
Hope I have provided enough clarity in this.
My system is Access 2000 and Win XP Pro
Can someone help me with this? Or put me on a better
track?

Jean-Marie
Douala, Cameroon.




.
Click to expand...
Click to expand...


.
Click to expand...
 
D

Duane Hookom

You didn't state that you placed "=" to the left of Sum([Revenue]). This
would cause the issue. Also, since the numbers appear left aligned, you may
need to use:
=Sum(Val(Revenue))

--
Duane Hookom
MS Access MVP


Jean-Marie said:
Many thanks Duane.
Please accept my apologies for not replying right away. I
had to go to a remote site for a business trip. In
addition, the program I am developing is a off-the-job
task and I can not work on it from the office; and anyway
am not allowed to do any post from the office

My report looks like this
Pool: Pool Name
*********************************************************
Vehicle: Vehicle plate number (Vehicle group header)
**********************************************************
Week # Revenue Expense
------------------------------------------------
Week1 Rev1 Exp1
...... ...... .......
weekn Revn Expn
******************************************************
Total Sum(Rev) Sum(Exp) (Vehicle
group footer)
******************************************************
Sum(Rev) and Sum(Exp) are text boxes that I want to
display the sum of revenue and sum of expenses for the
vehicle.
I don't know how to achieve it.
I first set their Control Source to Revenue and Expense
fields and in preview they displayed the value of those
fields for Week1
I then set their Control Source properties to Sum
([Revenue]) and Sum([Expense]) an got a syntax error
message.
In my report they are left aligned, so I guess they are
not numbers as they should be.

In the meantime I copied my DB and changed the structure.
I merged the tblExpenses and tblRevenue in a new
tblTransactions (IDTransactions (PK), dtmDateTrans,
curAmountTrans, Vehicle (FK), cboTypeTrans, Comments).
The combo cboTypeTrans indicates whether the transaction
is an Expense or Revenue. With this new structure I don't
need the Union query anymore and the report to displays
the way I want.
I would however like to know what I should have done in
my first DB.

Thanks again.

Jean-Marie
Douala-Cameroon
-----Original Message-----
Can you describe what you mean by "I am unable to calculate the totals for
Revenue and Expenses. I tried something like Sum ([Revenue]) but it won't
work". What exactly did you try and where? Also what do you mean by "won't
work"?

When you view your report's record source in datasheet view, do you see a
column named Revenue? Is the column left or right aligned?

--
Duane Hookom
MS Access MVP
--

Thank you Duane for your reply.
Sorry for not having indicated that in my first post, in
fact tblRevenue is related to tblVehicles through the
field VehicleRev.
In tblExpenses, CostCenterExp is related to tblVehicles
(a Cost Center belong to a vehicle, which can have more
than one Cost Center).
The structure of these tables looks like this:
tblVehicles (LicensePlate, AcqDate, HorsePower, MotorType)
tblRevenue (IDRevenue, DateRev, AmountRev, #LicensePlate,
DriverRev, CommentRev)
tblExpenses (IDExpenses, DateExp, AmountExp,
#CostCenterExp, CommentExp)
tblCostCenter(IDCostCenter, CodeCC, NameCC, #LicensePlate)
Hope this helps to understand my problem.

Jean-Marie.

-----Original Message-----
I'm not sure how you can report this information
together since your Revenue
and Expense tables are not related to Vehicles.

--
Duane Hookom
MS Access MVP


in message
Hello guys.

I am building a database to manage a fleet of transport
vehicles. I have among others these tables
tblVehicles (LicensePlate, AcqDate, HorsePower,
MotorType)
tblRevenue (IDRevenue, DateRev, AmountRev, VehicleRev,
DriverRev, CommentRev)
tblExpenses (IDExpenses, DateExp, AmountExp,
CostCenterExp, CommentExp)
IDRevenue and IDExpense are AutoNumber.
Vehicles are grouped in Pools under the supervision of
a
lead driver
I need to build many summary reports to display these
information
Pools
Vehicles
Weeks Revenue Expenses
Week1 Rev1 Exp1
Week2 Rev2 Exp2

Weekn Revn Expn
Total Total(Rev) Total(Exp)


I can build a Union query between tblRevenue and
tblExpense to retrieve the following: Pools, Vehicles,
Weeks, Amount, Type (Revenue or Expense)
Then build a crosstab query on the result to get theses
fields: Pools, Vehicles, Weeks, Revenue, Expenses.
Finally build a report based on the crosstab query.
My problem is that I am unable to calculate the totals
for Revenue and Expenses. I tried something like Sum
([Revenue]) but it won't work.
Hope I have provided enough clarity in this.
My system is Access 2000 and Win XP Pro
Can someone help me with this? Or put me on a better
track?

Jean-Marie
Douala, Cameroon.




.
Click to expand...


.
Click to expand...
Click to expand...
 
J

Jean-Marie

I am feeling ashamed. I did not put the "=" sign. Now it
works perfect. I am using the alignment tool of the
formatting toolbar in design to right align my controls.

Thank you so much Duane for your patience.

Have a merry Christmas and a very Happy New year.

Jean-Marie
-----Original Message-----
You didn't state that you placed "=" to the left of Sum ([Revenue]). This
would cause the issue. Also, since the numbers appear left aligned, you may
need to use:
=Sum(Val(Revenue))

--
Duane Hookom
MS Access MVP


Many thanks Duane.
Please accept my apologies for not replying right away. I
had to go to a remote site for a business trip. In
addition, the program I am developing is a off-the-job
task and I can not work on it from the office; and anyway
am not allowed to do any post from the office

My report looks like this
Pool: Pool Name
*********************************************************
Vehicle: Vehicle plate number (Vehicle group header)
**********************************************************
Week # Revenue Expense
----------------------------------------------- -
Week1 Rev1 Exp1
...... ...... .......
weekn Revn Expn
******************************************************
Total Sum(Rev) Sum(Exp) (Vehicle
group footer)
******************************************************
Sum(Rev) and Sum(Exp) are text boxes that I want to
display the sum of revenue and sum of expenses for the
vehicle.
I don't know how to achieve it.
I first set their Control Source to Revenue and Expense
fields and in preview they displayed the value of those
fields for Week1
I then set their Control Source properties to Sum
([Revenue]) and Sum([Expense]) an got a syntax error
message.
In my report they are left aligned, so I guess they are
not numbers as they should be.

In the meantime I copied my DB and changed the structure.
I merged the tblExpenses and tblRevenue in a new
tblTransactions (IDTransactions (PK), dtmDateTrans,
curAmountTrans, Vehicle (FK), cboTypeTrans, Comments).
The combo cboTypeTrans indicates whether the transaction
is an Expense or Revenue. With this new structure I don't
need the Union query anymore and the report to displays
the way I want.
I would however like to know what I should have done in
my first DB.

Thanks again.

Jean-Marie
Douala-Cameroon
-----Original Message-----
Can you describe what you mean by "I am unable to calculate the totals for
Revenue and Expenses. I tried something like Sum ([Revenue]) but it won't
work". What exactly did you try and where? Also what do you mean by "won't
work"?

When you view your report's record source in datasheet view, do you see a
column named Revenue? Is the column left or right aligned?
Click to expand...
wrote
in message
Thank you Duane for your reply.
Sorry for not having indicated that in my first post, in
fact tblRevenue is related to tblVehicles through the
field VehicleRev.
In tblExpenses, CostCenterExp is related to tblVehicles
(a Cost Center belong to a vehicle, which can have more
than one Cost Center).
The structure of these tables looks like this:
tblVehicles (LicensePlate, AcqDate, HorsePower, MotorType)
tblRevenue (IDRevenue, DateRev, AmountRev, #LicensePlate,
DriverRev, CommentRev)
tblExpenses (IDExpenses, DateExp, AmountExp,
#CostCenterExp, CommentExp)
tblCostCenter(IDCostCenter, CodeCC, NameCC, #LicensePlate)
Hope this helps to understand my problem.

Jean-Marie.

-----Original Message-----
I'm not sure how you can report this information
together since your Revenue
and Expense tables are not related to Vehicles.

--
Duane Hookom
MS Access MVP


in message
Hello guys.

I am building a database to manage a fleet of transport
vehicles. I have among others these tables
tblVehicles (LicensePlate, AcqDate, HorsePower,
MotorType)
tblRevenue (IDRevenue, DateRev, AmountRev, VehicleRev,
DriverRev, CommentRev)
tblExpenses (IDExpenses, DateExp, AmountExp,
CostCenterExp, CommentExp)
IDRevenue and IDExpense are AutoNumber.
Vehicles are grouped in Pools under the supervision of
a
lead driver
I need to build many summary reports to display these
information
Pools
Vehicles
Weeks Revenue Expenses
Week1 Rev1 Exp1
Week2 Rev2 Exp2

Weekn Revn Expn
Total Total(Rev) Total(Exp)


I can build a Union query between tblRevenue and
tblExpense to retrieve the following: Pools, Vehicles,
Weeks, Amount, Type (Revenue or Expense)
Then build a crosstab query on the result to get theses
fields: Pools, Vehicles, Weeks, Revenue, Expenses.
Finally build a report based on the crosstab query.
My problem is that I am unable to calculate the totals
for Revenue and Expenses. I tried something like Sum
([Revenue]) but it won't work.
Hope I have provided enough clarity in this.
My system is Access 2000 and Win XP Pro
Can someone help me with this? Or put me on a better
track?

Jean-Marie
Douala, Cameroon.




.



.
Click to expand...
Click to expand...


.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Cumulative sum in query 3
Dynamically filtering a report 5

Top