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.
3  2194  fr*****@virgin. net (CitizenPayne) wrote in message news:<30******* *************** ****@posting.go ogle.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
James Fortune wrote: fr*****@virgin. net (CitizenPayne) wrote in message
news:<30******* *************** ****@posting.go ogle.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
James Fortune wrote: fr*****@virgin. net (CitizenPayne) wrote in message
news:<30******* *************** ****@posting.go ogle.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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
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, Count(Closure.Closed_As) AS Closed_As_Count
FROM Closure
GROUP BY Closure.Closed_As;
and returns something like:
|
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
|
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.
However, the expectation changes - it may be 60% for a while, then
change to
50%. Initially, I was averaging the expectation, along with the
productivity, but what I'm being asked is to look at the average
productivity/performance compared to...
|
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 query it without
making a table out of query 1. I can't find a median function in the
"Total" field, so is there so way to make an expression to calculate
the median of the orignial data from query 1 in my new query?
Also, what does name by...
|
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 calculation: I.UNITS*Count(T.ENROLLMENTSTATUS)/8 AS DAYS
My query includes 6 joins but it happens with just 1 join or all of them. If I pull this calculation out, it works fine. In design view, I have tried to adjust the decimal property to every possible...
| | |
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 my
fields. When I try to run it I get #errors in my RunTot column. I'm
kinda new to this. Not sure if maybe I mistyped something wrong or is
there a better way to do this? I have pasted the code. Any help would
be greatly appreciated....
|
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 multiple instructors, which means I am getting multiple records for a single course. I need to capture all of the instructor names, but I only want a single record for each course due to the hour calculation. I would like the report to generate only one...
|
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) A ongoing list of invoice details, including a month/year period and an invoice total
At the moment, I can happily run the rebate calculation for the current month. What I would like to do is begin to track changes in supplier rebate rates...
|
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 Access help
and the Nz function seems to be the way forward.
I am summing over 6 fields, all or none of which may contain values.
The expression syntax I have used is as follows:
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| | |
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
