A very simple query re totals........

[txacoli]

At work, we time when our referrals come in and at the end of the year, we have to produce charts to show things like this. The query I've developed shows me how many referrals we get between each hour block (e.g. 07.00 - 07.59) which is fine, but I would also like it to total up the grand total of referrals at the bottom of that column.

Basically, this is my code (don't laugh, I'm a total newbie and it's very basic).

SELECT [Referrals 2010].Assess, Count(*) AS [Number], [Referrals 2010].Time
FROM [Referrals 2010]
GROUP BY [Referrals 2010].Assess, [Referrals 2010].Time


HAVING ((([Referrals 2010].Assess)="No") AND (([Referrals 2010].Time) Between "07.00-07.59" And "19.00-19.59"));

I can add an autosum into my 2007 Access at home, but not on the 2003 Access I'm running at work. Can someone tell me how to make it do this simple little thing please?

Many thanks in advance

Mark

[Rixxe]

Might be an idea to look at the DSUM() and SUM() functions.

Good luck

[txacoli]

I've done a lot since you helped me last week Rixxe, but this has me stumped. I know it's simple, but I can't get through it. I've looked and looked on Google and while there are loads of suggestions, there's nothing I can find there that I can get to work.

Is there any chance, using the code in my original post, that you - or anyone - could suggest where I could put my DSUM or SUM please? (Polite answers only please!)

[Rixxe]

At least you have been looking (Thats what learning new stuff is all about!)
This site might help you:
http://www.techonthenet.com/sql/sum.php

But what you will need to do is put something like this into your query:
I havent actually tried this, but it makes sence. (Don't often use SUM function)

 
SELECT SUM(Referrals 2010) as "Total referrals"
, [Referrals 2010].Assess, Count(*) 
AS [Number]
, [Referrals 2010].Time
FROM [Referrals 2010]
GROUP BY [Referrals 2010].Assess, [Referrals 2010].Time
HAVING ((([Referrals 2010].Assess)="No")
AND (([Referrals 2010].Time) 
Between "07.00-07.59" And "19.00-19.59")); 

I'm not 100% sure about what field you wish to count, so you can change that accordingly.

Good luck, and if you get stuck im sure someone will be able to help you out.

Rixxe.

[txacoli]

Hi Rixxe

Thanks for your continued help. I spent about 3 (frustrating!) hours today trying to use the code you'd given me, but to no avail. It adds up the amount of referrals in each time block - which I can already do - but doesn't give me a total number of referrals at the bottom of the 'Number' column, which is what I want to do. I've attached a pic of how I'd like it to look, which is something I can do easily in Access 2007, but not in 2003, which is the system we have at work. (I don't need the word TOTAL in there, it's just that 2007 automatically puts it in)



If you can help me get that total at the bottom of the 'Number' column using code in 2003, I will be so grateful.

Thanks in anticipation

Mark

[Rixxe]

Hmm, can't say i've ever wanted to do that in access.

You can just use this:

=Sum([Column name])

In the design view i believe, which gives you a total of the whole column..
For this case i think its best either to run two queries, the one with count, then run SUM (Numbers) as another one.

Although using the UI isn't great, it might be the best thing to get that totals column. (Even in access 2003!)

You can get that total to be in a different location, say in another column... if thats any use to you.

Otherwise, hopefully someone else might have the answer.

Good luck, sorry i can't give you a definite answer.

Rixxe.

[jo15765]

The =Sum([Total]) ((Total) here is my column name in the query I want to total))does not work for me, I get an error stating "Subqueries cannot be used in the expression (Sum([Total]))). I think it is because I am already using the expression Total to "Count" maybe? But I am wanting the same thing, to be able to have a "Grand Total" in the same format just by using a query.