Calculations

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

There is my Query:
SELECT DISTINCTROW
Sum([Lodging]+[Taxes]+[Food]+[Parking]+[Mileage]+[Rental]+[Gas]+[Taxi]+[Phone]+[Computer]+[FAX]+[Metro]+[Tips]+[Train]) AS Stotal
FROM tblTravel;

Well I just get a blank field and only one.

What am I doing wrong.

Thank You for any help....
 
Amour said:
There is my Query:
SELECT DISTINCTROW
Sum([Lodging]+[Taxes]+[Food]+[Parking]+[Mileage]+[Rental]+[Gas]+[Taxi]+[Phone]+[Computer]+[FAX]+[Metro]+[Tips]+[Train])
AS Stotal FROM tblTravel;

Well I just get a blank field and only one.

What am I doing wrong.

Thank You for any help....
Click to expand...

That is not how Sum() works in databases. It is used to sum across rows,
not to sum fields. All you should need to do is get rid of the Sum()
function.

SELECT
[Lodging]+[Taxes]+[Food]+[Parking]+[Mileage]+[Rental]+[Gas]+[Taxi]+[Phone]+[Computer]+[FAX]+[Metro]+[Tips]+[Train]
AS Stotal
FROM tblTravel;

The above assumes that none of those fields being added together will
contain a null value. If any of them can contain a null then you would need
to wrap those fields in the Nz() function...

SELECT Nz([Lodging],0) + Nz([Taxes],0) + Nz([Food],0) ...
 
I changed it to:
SELECT DISTINCTROW
[Lodging]+[Taxes]+[Food]+[Parking]+[Mileage]+[Rental]+[Gas]+[Taxi]+[Phone]+[Computer]+[FAX]+[Metro]+[Tips]+[Train] AS Stotal
FROM tblTravel;

but it still is blank

Thank You for any help...

Rick Brandt said:
Amour said:
There is my Query:
SELECT DISTINCTROW
Sum([Lodging]+[Taxes]+[Food]+[Parking]+[Mileage]+[Rental]+[Gas]+[Taxi]+[Phone]+[Computer]+[FAX]+[Metro]+[Tips]+[Train])
AS Stotal FROM tblTravel;

Well I just get a blank field and only one.

What am I doing wrong.

Thank You for any help....
Click to expand...

That is not how Sum() works in databases. It is used to sum across rows,
not to sum fields. All you should need to do is get rid of the Sum()
function.

SELECT
[Lodging]+[Taxes]+[Food]+[Parking]+[Mileage]+[Rental]+[Gas]+[Taxi]+[Phone]+[Computer]+[FAX]+[Metro]+[Tips]+[Train]
AS Stotal
FROM tblTravel;

The above assumes that none of those fields being added together will
contain a null value. If any of them can contain a null then you would need
to wrap those fields in the Nz() function...

SELECT Nz([Lodging],0) + Nz([Taxes],0) + Nz([Food],0) ...
Click to expand...
 
What I want to do is: have a calculated subtotal for all the fields within
the query. Attach a form to that so that the user can see/print the amounts
that are in the fields and also see the subtotal.

Thank You for any help...

Marshall Barton said:
Amour said:
There is my Query:
SELECT DISTINCTROW
Sum([Lodging]+[Taxes]+[Food]+[Parking]+[Mileage]+[Rental]+[Gas]+[Taxi]+[Phone]+[Computer]+[FAX]+[Metro]+[Tips]+[Train]) AS Stotal
FROM tblTravel;

Well I just get a blank field and only one.
Click to expand...


Hard to tell what might be wrong if you don't tell us what
you do want.

Maybe all you need to do is get rid of the Sum function?
Click to expand...
 
Amour said:
There is my Query:
SELECT DISTINCTROW
Sum([Lodging]+[Taxes]+[Food]+[Parking]+[Mileage]+[Rental]+[Gas]+[Taxi]+[Phone]+[Computer]+[FAX]+[Metro]+[Tips]+[Train]) AS Stotal
FROM tblTravel;

Well I just get a blank field and only one.
Click to expand...


Hard to tell what might be wrong if you don't tell us what
you do want.

Maybe all you need to do is get rid of the Sum function?
 
I changed it to:
SELECT DISTINCTROW
[Lodging]+[Taxes]+[Food]+[Parking]+[Mileage]+[Rental]+[Gas]+[Taxi]+[Phone]+[Computer]+[FAX]+[Metro]+[Tips]+[Train] AS Stotal
FROM tblTravel;

but it still is blank
Click to expand...

It will be if ANY of the non-normalized, improperly structured fields is NULL.
Anything plus NULL is NULL!

Try (as suggested, which you may not have read)

SELECT DISTINCTROW
NZ([Lodging])+NZ([Taxes])+NZ([Food])+NZ([Parking])+NZ([Mileage])+NZ([Rental])+NZ([Gas])+NZ([Taxi])+NZ([Phone])+NZ([Computer])+NZ([FAX])+NZ([Metro])+NZ([Tips])+NZ([Train])
AS Stotal
FROM tblTravel;


Much better, though, would be to correct your table design so that tblTravel
is related one-to-many to tblTravelExpenses; this table would have a field for
ExpenseType and another for Amount. Rather than 14 fields (some of them null)
you would have between zero and 14 *RECORDS*, one for each expense type
actually incurred. *Then* you could use Sum([Amount]) because you'ld be
summing across records rather than across fields.

John W. Vinson [MVP]
 
See John and Rick's replies about using the Nz() function.

Then spend some serious thinking time considering John's
recommendation about using a different table structure. A
relational database is not a fancy spreadsheet and you will
have a never ending list of problems if you treat it as if
it were.
--
Marsh
MVP [MS Access]

What I want to do is: have a calculated subtotal for all the fields within
the query. Attach a form to that so that the user can see/print the amounts
that are in the fields and also see the subtotal.


Marshall Barton said:
Amour said:
There is my Query:
SELECT DISTINCTROW
Sum([Lodging]+[Taxes]+[Food]+[Parking]+[Mileage]+[Rental]+[Gas]+[Taxi]+[Phone]+[Computer]+[FAX]+[Metro]+[Tips]+[Train]) AS Stotal
FROM tblTravel;

Well I just get a blank field and only one.
Click to expand...


Hard to tell what might be wrong if you don't tell us what
you do want.

Maybe all you need to do is get rid of the Sum function?
Click to expand...
Click to expand...
 
Thank You for your help this is great. As for your commit about the database
design, I did not give all information only what I needed to resolve the
problem. So far this is what I have going:

SELECT DISTINCTROW tblTravel.*,
NZ([Lodging])+NZ([Taxes])+NZ([Food])+NZ([Parking])+NZ([Mileage])+NZ([Rental])+NZ([Gas])+NZ([Taxi])+NZ([Phone])+NZ([Computer])+NZ([FAX])+NZ([Metro])+NZ([Tips])+NZ([Train]) AS Stotal, NZ([Stotal])+NZ([Airfare]) AS GTotal
FROM tblAttendan LEFT JOIN tblTravel ON (tblAttendan.SSN = tblTravel.SSN)
AND (tblAttendan.MEET_NUM = tblTravel.MEET_NUM);


I hope I am doing as you suggested. All the other info comes from other
tables. so as you say table tblTravel is really the expense table. But for
each expense being a record(see commit below), Can I do this? Don't I have
to link this all up?

My next step is to do some VBA and make it so that there is no duplications
of:
(SSN + MEET_NUM + Order_Num) or (SSN + MEET_NUM) within the table, if there
are then don't add the record (already exist).

Thank You


John W. Vinson said:
I changed it to:
SELECT DISTINCTROW
[Lodging]+[Taxes]+[Food]+[Parking]+[Mileage]+[Rental]+[Gas]+[Taxi]+[Phone]+[Computer]+[FAX]+[Metro]+[Tips]+[Train] AS Stotal
FROM tblTravel;

but it still is blank
Click to expand...

It will be if ANY of the non-normalized, improperly structured fields is NULL.
Anything plus NULL is NULL!

Try (as suggested, which you may not have read)

SELECT DISTINCTROW
NZ([Lodging])+NZ([Taxes])+NZ([Food])+NZ([Parking])+NZ([Mileage])+NZ([Rental])+NZ([Gas])+NZ([Taxi])+NZ([Phone])+NZ([Computer])+NZ([FAX])+NZ([Metro])+NZ([Tips])+NZ([Train])
AS Stotal
FROM tblTravel;


Much better, though, would be to correct your table design so that tblTravel
is related one-to-many to tblTravelExpenses; this table would have a field for
ExpenseType and another for Amount. Rather than 14 fields (some of them null)
you would have between zero and 14 *RECORDS*, one for each expense type
actually incurred. *Then* you could use Sum([Amount]) because you'ld be
summing across records rather than across fields.

John W. Vinson [MVP]
Click to expand...
 
Back
Top