Query for Summing individual records on their own

[rezza]

Hello Everyone,

I am trying to perform a sum of records for the table below

SeatType | Ext rent | |Internal rent |
Work Transfer 2 2
Vendor
Other 2 3
LOB BRF
GTI BRF 2 5
Displacement


so that when I run the query I get the following following


SeatType | Ext rent| |Internal rent| | Total |
Work Transfer 2 2 4
Vendor
Other 2 3 5
LOB BRF
GTI BRF 2 5 7
Displaceme


What I mean is that from the first table how is it possible to get the results below. The query would generate a new column and place the sum of the data in the each row(record) in the same row in the total column. I would be really grateful. Have been bashing my head all day over this. PS I dont know any VBA. Is it possible without VBA?

[GolferGuy]

Here is the sql that will do what you have asked. I don't know your table name, so I used tblSeats as the table name.

SELECT tblSeats.SeatType, tblSeats.ExtRent, tblSeats.InternalRent, [ExtRent]+[InternalRent] AS Total
FROM tblSeats;

I'm not sure that is truly what you are looking for. I was thinking that you might be looking for a summary query that will give you totals for each seat type for ExtRent and InternalRent, then the total of both fields, but the way your question is worded, as least as I understand it, the SQL above will do what you asked.

HTH,
Vic

[rezza]

Thanks for reply. Yes if u look at table 1st (topmost) it doesnt have a total column. I want a table wich shows all the columns in the first table and a total column created in the query which shows totals for each seat type. so it (totals column) would have three entries in that column. 4 and 5 and 7 in the above example.

[rezza]

I was thinking that you might be looking for a summary query that will give you totals for each seat type for ExtRent and InternalRent,

HTH,
Vic

yes this is what I would like please.

regards

[GolferGuy]

rezza,

In your two posts, I see nothing different than what you asked for in your first request. The SQL I gave you will do just that, give you a total for each seat type. Try that SQL and see if it is what you want. If it is not, then let me know what you want different that what the SQL will give you.

Vic

[rezza]

I have applied your SQL but it only sums up the rent of one seat type. The other seat rents are not summed.

[GolferGuy]

Could you show me what the first 4 or 5 lines of the output of the SQL startement give you?

[rezza]

Could you show me what the first 4 or 5 lines of the output of the SQL startement give you?

SeatType SumOfOther$PM SumOfExtRent$PM SumOfTechSpt$PM Total
Work Transfer
Vendor 4833 19540.32
Other
LOB BRF 11681 5417 30296 47394
GTI BRF
Displacement


The output is a bit screwed as the text pane is unable to show in full width but SumOfTechSpt$PM and Total are two other columns. I hope u understand what i mean. The query only shows sum of LOB BRF which is
11681+5417+30296= 47394 but not for Vendor.

[GolferGuy]

I'm sorry, I'm just not getting it yet. In your first request, you showed that you had 6 records, each with 3 columns. Columns were Seat Type, Ext Rent, and Internal Rent. The 6 types of seats were: WorkTransfer, Vendor, Other, LOB BRF, GTI BRF, and Displacement.

In your last post, I see columns of Seat Type, SumOfOther$PM, SumOfExtRent$PM, SumOfTechSpt$PM, and then the Total (which is what I understand is what you wanted in the first place). I still see the same six records of "seat types" which are Work Transfer, Vendor, Other, LOB BRF, GTI BRF, and Displacement.

Please answer the following questions. Specific answers will be a BIG help.
Do you have only six records? Yes/No
If you have more than 6 records, do you have more than 6 types of seats (Seat Type) Yes/No
How many records do you have, at this very moment? _______
How many fields do you have in each record that you want summed together within the one record? _________
If you could post the actual layout of a record, that would be a big help too.

Once you start with a certain senerio, best to stay with it rather than changing field names, etc. VERY CONFUSING on this end.

Your last statement is that "the output is a bit screwed as the text pane is unable to show in full width but SumOfTechSpt$PM and Total are two other columns." OK, so you can not see the entire horizonal width of the query output. There should be a scroll bar at the bottom that would allow you to see the remainder of the fields. You also said the query only shows sum of LOB BRF which is 11681+5417+30296=47394 BUT NOT FOR VENDOR. What do you mean by NOT FOR VENDOR. You showed two numbers for vendor, so I am assuming there were two numbers in that record, but no total. If that is the case, then within the SQL, change it to be like this:
SELECT tblSeats.SeatType, tblSeats.ExtRent, tblSeats.InternalRent, Nz([ExtRent])+Nz([InternalRent]) AS Total FROM tblSeats;

The difference here is that I put each column name into a function. The function is Nz(). The Nz function will change any nulls into zeros (0). If they are left as nulls, then the addition of the fields will ALWAYS add up to NULL. Therefore, just one Null makes it so no addition or summing will take place and we end up with nothing for an answer.

Hope this helps.
Vic

[rezza]

You also said the query only shows sum of LOB BRF which is 11681+5417+30296=47394 BUT NOT FOR VENDOR. What do you mean by NOT FOR VENDOR. You showed two numbers for vendor, so I am assuming there were two numbers in that record, but no total. If that is the case, then within the SQL, change it to be like this:
SELECT tblSeats.SeatType, tblSeats.ExtRent, tblSeats.InternalRent, Nz([ExtRent])+Nz([InternalRent]) AS Total FROM tblSeats;

The difference here is that I put each column name into a function. The function is Nz(). The Nz function will change any nulls into zeros (0). If they are left as nulls, then the addition of the fields will ALWAYS add up to NULL. Therefore, just one Null makes it so no addition or summing will take place and we end up with nothing for an answer.

Hope this helps.
Vic

First of all many thanks for replying again. There arent many people who follow up on posts on forums except people who really like to help. Yeh I think that was the problem solved for me. The fields for many records infact havnt got any data in them thats why there was the weirdness in the output. Thanks golfer guy. I'll trouble u again if i need more info on my project.