Adding Totals to a Query

[FranCorona]

I am creating a query with the following fields: Model, Admin, Issue Miles, Turn In Miles, Issue Hours, Turn In Hours


I need to create a report showing all of these fields but with a total column next to Turn In Miles showing the total of miles used and then beside the Turn In Hours to show total of hours used. It has been a few years since I have "played around" in MS Access and I know this is probably something simple but I don't really have the time to search and read through results.... If anyone can help I would really appreciate it!

Thanks!

[aytee111]

Do it in the report, add a textbox with its control source having the calculation.

[FranCorona]

Do it in the report, add a textbox with its control source having the calculation.

I did what you said and put this formula in the text box for the total miles: Sum([BOSS Turn In Miles])-Sum([BOSS Issue Mile])
And in the text box for total hours: Sum([BOSS Turn In Hours])-Sum(BOSS Issue Hours])

But it looks like it is calculating all? I need it to show total for that specific admin number. For example HMEE: BOSS Issue Mile = 412 and BOSS Turn In Miles = 743 so I need it to show 743-412=331 and the same thing for total hours. How do I do that? here is screen shot of report how it is now.

[aytee111]

Your example as you write it - "BOSS Issue Mile = 412 and BOSS Turn In Miles = 743 so I need it to show 743-412=331" - is the way you want it. Why are you adding "SUM"? These calculations are coming from the query, so SUM means total of all records.

[FranCorona]

Your example as you write it - "BOSS Issue Mile = 412 and BOSS Turn In Miles = 743 so I need it to show 743-412=331" - is the way you want it. Why are you adding "SUM"? These calculations are coming from the query, so SUM means total of all records.

I see, so how do I get it to show the total miles used? (331)

[FranCorona]

I need it show the calculation for each particular admin number, I just used that one for an example.

[aytee111]

=Nz([BOSS Turn In Miles]) - Nz([BOSS Issue Mile])

[FranCorona]

Thank you so much! Your the best!

[FranCorona]

I have another question. I need to capture daily totals, which I have done in the query but I also need a cumulative total as well as a remaining total. Any help would be greatly appreciated!

[aytee111]

Group the report on day, in the group footer add textboxes with the calculations (now you use SUM).

[FranCorona]

Thank you! I have another question. I have a recap report that I need to calculate fields with check boxes. So the fields I have are Position-text, move-check box, add-check box, model-text, drop-check box. In the query I have all of these fields grouped then I have them but with count. Then in the report I have formulas to calculate but now that I changed the data type from text to check boxes it isn't calculating correctly. This is one formula I have: Initial request: =Sum([CountOfPosition])-Sum([CountOfAdd]) but it is counting all of the check boxes even if there is not a check in it. Then for the Moves: =Sum([CountOfMove]) and Drops: =Sum([CountOfDrop]) and Adds: =Sum([CountOfAdd]) and then Final Issue Total: =Sum([CountOfPosition])+Sum([CountOfAdd])-Sum([CountOfDrop])

[aytee111]

Where did you change the data type to check box? Where is the issue happening, in the query?

[FranCorona]

I changed it after importing it from excel in design view. I am thinking that I am not going to change it to a checkbox and just have the input person mark it with an X instead. Thank you so much for your help with these.

[aytee111]

There is a way to fix this, don't give up! You are still saying you changed "it", whatever that is?

[FranCorona]

LOL Yes. First it is created in excel and then imported into Access. After importing I went into design view of the table and changed the data type from short text to yes/no with a check box for the look up.