By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,453 Members | 961 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,453 IT Pros & Developers. It's quick & easy.

query group calculation

P: n/a
I have a problem with an access 97 query

I have included the current query output.

for every 'SPEC' there are 6 possible values for 'INTMAN' ( 1 to 6).
I need to group by 'SPEC' where INTMAN = 1 then total the 'TOTAL'
column for that group.
example. new group total = 3
After this group the next grouping would display info for 'SPEC' = A1
and INTMAN = BETWEEN 2 AND 6 with the new group total = 19

and so on and so on with next group havin spec C11.

Cons Spec SpecDesc IntMan Status Total
BROWN A1 General Medicine 1 ADMIT 1
SHEPHERD A1 General Medicine 1 ADMIT 1
SHEPHERD A1 General Medicine 1 REMOVE 1
BROWN A1 General Medicine 2 ADMIT 1
SHEPHERD A1 General Medicine 2 ADMIT 1
SHEPHERD A1 General Medicine 2 ADMIT 2
SHEPHERD A1 General Medicine 2 ADMIT 3
SHEPHERD A1 General Medicine 2 PRECBH 1
SHEPHERD A1 General Medicine 2 REMOVE 1
SHEPHERD A1 General Medicine 2 WLACTV 1
BROWN A1 General Medicine 3 ADMIT 1
BROWN A1 General Medicine 4 ADMIT 1
CONNACHERA1 General Medicine 4 ADMIT 1
CURRIE A1 General Medicine 4 ADMIT 1
CURRIE A1 General Medicine 4 PRECBH 1
CURRIE A1 General Medicine 5 ADMIT 1
BOYD C11 General Surgery 1 ADMIT 1
BOYD C11 General Surgery 1 ADMIT 2
any advice / pointers that will let me total by spec where intman = 1
and also total by spec where intman is between 2 and 6 would be
greatly appreciated.

Thank you.
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
fr*****@virgin.net (CitizenPayne) wrote in message news:<30**************************@posting.google. com>...
I have a problem with an access 97 query

I have included the current query output.


Hmm. Have you tried:

SELECT Spec, Sum(Total) AS NewGroupTotal FROM tblSpecData WHERE
IntMan=1 GROUP BY Spec;

SELECT Spec, Sum(Total) AS NewGroupTotal FROM tblSpecData WHERE
IntMan>=2 AND IntMan <=6 AND Spec = 'A1' GROUP BY Spec;

Does that help? BTW, kudos to Allen Browne for pointing out the
advantages of WHERE over HAVING. Also, when omitting the INTMAN = 1
values Access calculates a group total of A1 = 16 instead of 19 for
your example. I hope I understood what you were asking for. If so,
it's a good thing Access is going to do the totaling for you :-).

James A. Fortune
Nov 13 '05 #2

P: n/a

James Fortune wrote:
fr*****@virgin.net (CitizenPayne) wrote in message

news:<30**************************@posting.google. com>...
I have a problem with an access 97 query

I have included the current query output.


Hmm. Have you tried:

SELECT Spec, Sum(Total) AS NewGroupTotal FROM tblSpecData WHERE
IntMan=1 GROUP BY Spec;

SELECT Spec, Sum(Total) AS NewGroupTotal FROM tblSpecData WHERE
IntMan>=2 AND IntMan <=6 AND Spec = 'A1' GROUP BY Spec;

Does that help? BTW, kudos to Allen Browne for pointing out the
advantages of WHERE over HAVING. Also, when omitting the INTMAN = 1
values Access calculates a group total of A1 = 16 instead of 19 for
your example. I hope I understood what you were asking for. If so,
it's a good thing Access is going to do the totaling for you :-).

James A. Fortune


Nov 13 '05 #3

P: n/a

James Fortune wrote:
fr*****@virgin.net (CitizenPayne) wrote in message

news:<30**************************@posting.google. com>...
I have a problem with an access 97 query

I have included the current query output.


Hmm. Have you tried:

SELECT Spec, Sum(Total) AS NewGroupTotal FROM tblSpecData WHERE
IntMan=1 GROUP BY Spec;

SELECT Spec, Sum(Total) AS NewGroupTotal FROM tblSpecData WHERE
IntMan>=2 AND IntMan <=6 AND Spec = 'A1' GROUP BY Spec;

Does that help? BTW, kudos to Allen Browne for pointing out the
advantages of WHERE over HAVING. Also, when omitting the INTMAN = 1
values Access calculates a group total of A1 = 16 instead of 19 for
your example. I hope I understood what you were asking for. If so,
it's a good thing Access is going to do the totaling for you :-).

James A. Fortune


Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.