473,320 Members | 2,071 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

query group calculation

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
3 2174
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

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

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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: cwhite | last post by:
The answer to this is probably very simple, but it has been a while since I have had to use access. I have two different queries, one counts records: SELECT Closure.Closed_As,...
6
by: windandwaves | last post by:
Hi Folk I have a query: SELECT COUNT( `SIS`.`ID` ) c, D FROM `SIS` , `SID` WHERE `SID_ID` = `SID`.`ID` AND `BRO` <> "bot" GROUP BY SID.ID
14
by: Tina | last post by:
My employer tracks productivity/performance of clinicians (how much they bill) each week, its averages for the month, and the 6 months. These averages are compared to their expected productivity....
5
by: jonm4102 | last post by:
I'm trying to calculate the median of some numerical data. The data can only be found in a query (henceforth query 1) field I previously made, and I would prefer to calculate the median in a new...
4
by: heckstein | last post by:
I am a novice working in Access 2002 trying to run a query I created. I am receiving the error "scaling of decimal value resulted in data trunction", which I have determined is due to this...
9
by: JJM0926 | last post by:
I'm trying to create a running totals query in access 97. I have followed the directions on how to do it from Microsofts website article id 138911. I took their code they had and replaced it with...
3
by: heckstein | last post by:
I have created a query in MS Access 2003 that is pulling training records for our company that includes training hour calculation. One field I am pulling is the instructor name. Many courses have...
15
by: Widge | last post by:
Hi, I wondered if you could help me with an issue I'm having. Currently I have a rebate calculation that is running off two tables: 1) A list of suppliers and the rebate %ages relevant to them...
2
by: Will | last post by:
Hi, I need to handle blank values in a query calculation. I have 636 records at the moment but when I sum over these records the blank fields are not returned. I have looked around here and on...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.