Group by and Sum in same query

[Guest]

I need to get the total of one field based on another field in a query. In
other words sum one field by grouping on another. Is this done by count, sum
or? and where and how do I group while trying to total by another field? I
will also need to sum by an expression while grouping on another field but If
someone can get me started I can take it from there. Thanks in advance.

 

[Guest]

use sum for the column you're totalling and in the other field you're trying
to group by use the 'group by' option and set your criteria; ie in the
criteria property of that field say that field assigned a color and if you
wanted to see only the total for one color, you'd enter 'blue' for the
criteria.

 

[Guest]

Thanks for your help but I am looking to group by a field in a table not the
data in the table and sum it against another field in a different table.

Example:
I have a field named Process date. I need to sum (get a total) of all those
dates based off another field in a table I have named PlanID. So I think you
need to group on PlanID and somehow sum another field Process date to get the
results.

In a nutshell. Retrieve a total from one field based off a field from
another table which I think is goup by clause?

 

[Guest]

Thank you for your hlep. You pretty much understand what I am looking for. I
just need a total (count) of all the Process Dates, based off all of the Plan
ID's. There are over 200 different plan ID's.
Example below..

Plan ID number 89
total process dates (34)

Plan ID number 210
total process dates (105) etc

I will need to collect effective dates and a few others the same way but
once I get the above down I can run with it from there. For some reason I
cant get your SQL to work. It is pretty basic so dont know whats wrong.
Thanks again for your help

Thanks for your help but I am looking to group by a field in a table not the
data in the table

TKM,

As far as I know, it's the same thing.

That is to say, "field in a table" and, "data in a table", are the
same, because all data in tables are found in columns.

and sum it against another field in a different table.

Can you provide a more detailed description? What does "against"
mean in this case?

Example:
I have a field named Process date. I need to sum (get a total) of all those
dates

I think you may want to COUNT your dates. (I am unsure of what
SUMed dates would produce.)

based off another field in a table I have named PlanID.

What is the comparison you wish to make?

PlanID sounds like a Long Integer, and [Process date] sounds like a
date.

I don't think you can compare them.

So I think you
need to group on PlanID and somehow sum another field Process date to get the
results.

Open a new query, switch to SQL VIEW (where you can copy and paste
the query given below), and you will need to subtitute your table
names and column names with ones I've guessed at below where
necessary. You many need to change the ON comparison operator.

(The following *is* a guess. Please take it as such.)


SELECT T1.PlanID
,COUNT(T1.[Process date])
FROM table1 as T1
INNER JOIN
table2 as T2
ON T1.PlanID = T2.PlanID
GROUP BY T2.PlanID

In a nutshell. Retrieve a total from one field based off a field from
another table which I think is goup by clause?

Sincerely,

Chris O.

 

[Chris2]

Thanks for your help but I am looking to group by a field in a table not the
data in the table

TKM,

As far as I know, it's the same thing.

That is to say, "field in a table" and, "data in a table", are the
same, because all data in tables are found in columns.

and sum it against another field in a different table.

Can you provide a more detailed description? What does "against"
mean in this case?

Example:
I have a field named Process date. I need to sum (get a total) of all those
dates

I think you may want to COUNT your dates. (I am unsure of what
SUMed dates would produce.)

based off another field in a table I have named PlanID.

What is the comparison you wish to make?

PlanID sounds like a Long Integer, and [Process date] sounds like a
date.

I don't think you can compare them.

So I think you
need to group on PlanID and somehow sum another field Process date to get the
results.

Open a new query, switch to SQL VIEW (where you can copy and paste
the query given below), and you will need to subtitute your table
names and column names with ones I've guessed at below where
necessary. You many need to change the ON comparison operator.

(The following *is* a guess. Please take it as such.)


SELECT T1.PlanID
,COUNT(T1.[Process date])
FROM table1 as T1
INNER JOIN
table2 as T2
ON T1.PlanID = T2.PlanID
GROUP BY T2.PlanID

In a nutshell. Retrieve a total from one field based off a field from
another table which I think is goup by clause?

Sincerely,

Chris O.

 

[Guest]

Chris,

Thanks for your reply. Your helped me along until I got it but one thing as
I thought would happen. Because I need a total count for each plan it would
only work in a report. When you have multiple of anything you will get that
in your results as you already know. Anyway here is what I came up with that
works almost what I needed but still not exactly..

SELECT CMPSOWNER_FUNDDESC.FD_DESC_CD,
Count(CMPSOWNER_UNITS_BUY_SELL.BUY_SELL_UNIT_CT) AS [TOTAL FUNDS]
FROM CMPSOWNER_FUNDDESC INNER JOIN CMPSOWNER_UNITS_BUY_SELL ON
CMPSOWNER_FUNDDESC.FD_DESC_CD = CMPSOWNER_UNITS_BUY_SELL.FD_DESC_CD
GROUP BY CMPSOWNER_FUNDDESC.FD_DESC_CD;

thanks again for your help!

Thanks for your help but I am looking to group by a field in a table not the
data in the table

TKM,

As far as I know, it's the same thing.

That is to say, "field in a table" and, "data in a table", are the
same, because all data in tables are found in columns.

and sum it against another field in a different table.

Can you provide a more detailed description? What does "against"
mean in this case?

Example:
I have a field named Process date. I need to sum (get a total) of all those
dates

I think you may want to COUNT your dates. (I am unsure of what
SUMed dates would produce.)

based off another field in a table I have named PlanID.

What is the comparison you wish to make?

PlanID sounds like a Long Integer, and [Process date] sounds like a
date.

I don't think you can compare them.

So I think you
need to group on PlanID and somehow sum another field Process date to get the
results.

Open a new query, switch to SQL VIEW (where you can copy and paste
the query given below), and you will need to subtitute your table
names and column names with ones I've guessed at below where
necessary. You many need to change the ON comparison operator.

(The following *is* a guess. Please take it as such.)


SELECT T1.PlanID
,COUNT(T1.[Process date])
FROM table1 as T1
INNER JOIN
table2 as T2
ON T1.PlanID = T2.PlanID
GROUP BY T2.PlanID

In a nutshell. Retrieve a total from one field based off a field from
another table which I think is goup by clause?

Sincerely,

Chris O.

 

[Chris2]

Thank you for your hlep. You pretty much understand what I am looking for. I
just need a total (count) of all the Process Dates, based off all of the Plan
ID's. There are over 200 different plan ID's.
Example below..

Plan ID number 89
total process dates (34)

Plan ID number 210
total process dates (105) etc

I will need to collect effective dates and a few others the same way but
once I get the above down I can run with it from there. For some reason I
cant get your SQL to work. It is pretty basic so dont know whats wrong.
Thanks again for your help

TKM,

My SQL was only a "shot in the dark".

-------------------------------

The following contains information on how you can improve your
chances of getting your question answered:

-------------------------------

Formatting:

Please use a monospace font (Courier New, etc.) when writing out
your examples (all descriptions, charts, SQL, etc.).

-------------------------------

Process Description:

Please only include the shortest possible narrative of what is going
on with the query. (Include all that is necessary, and nothing
more.)

When parts of your query make calculations, show the exact code or
nearest readable plain-text math formula you can create.

When you are done with this section, re-read it several times before
posting to assure yourself that you are accurately describing the
situation in a way you believe others will understand.

-------------------------------

Table Structures/Description:

Post a description of your table structures.

Open your table in Design View, view the column names and data types
in it, and then type out the column names and data types *that are
necessary* (do not include columns that are not absolutely necessary
for the query). Use the Index dialog box (you can get at it by
clicking on the "lightning bolt and stacked lines" icon on the
toolbar) to locate information on primary and foreign keys and other
indexes and type out that information, as well.

Note: For table descriptions (or DDL) lining up the column names,
data type names, and key/index information in neat columns is quite
helpful.

Note: If you know how to write DDL SQL (CREATE TABLE), please post
that (including constraints) instead of text descriptions. (Please
post only the portion of the DDL that is relevant.)


Example (text description):

MyTableOne
MyTableOneID AUTOINCREMENT PK
ColTwo INTEGER NOT NULL
ColThree TEXT(10)

MyTableTwo
MyTableTwoID AUTOINCREMENT PK\
MyTableOneID INTEGER PK/-- Composite Primary Key
FK -- MyTableOne MyTableOneID
ColThree INTEGER
ColFour DATETIME
ColFive CURRENCY
ColSix BIT
ColSeven TEXT(1)
ColEight TEXT(1)

etc., etc., etc.


Example (DDL SQL/CREATE TABLE):

CREATE TABLE MyTableOne
(MyTableOneID AUTOINCREMENT
,ColTwo INTEGER NOT NULL
,ColThree TEXT(10)
,CONSTRAINT pk_MyTableOne
PRIMARY KEY (MyTableOneID)
)

CREATE TABLE MyTableTwo
(MyTableTwoID AUTOINCREMENT
,MyTableOneID INTEGER
,ColThree INTEGER
,ColFour DATETIME
,ColFive CURRENCY
,ColSix BIT
,ColSeven TEXT(1)
,ColEight TEXT(1)
,CONSTRAINT pk_MyTableTwo
PRIMARY KEY (MyTableTwoID)
,CONSTRAINT fk_MyTableTwo_MyTableOne_MyTableOneID
FOREIGN KEY (MyTableOneID)
REFERENCES MyTableOne (MyTableOneID)
)

The Primary Key and Foreign Key notes (or constraints in the DDL
SQL) are *critical*.

Although it can be a source of information, please do not copy and
paste information directly from MS Access' Documenter. It is
virtually unreadable. If possible, do not use the Dcoumenter at
all. If you absolutely must use it, please distill down and legibly
format only the relevant table information.

-------------------------------

Sample Data (using comma delimited lists):

Note: If your sample data is "wide" across the screen, and you can't
trim out any columns because they are needed, make *two* (or more)
charts, and then clearly note that the second chart is the
continuation of the first chart for the same table. It is far
easier to convert a comma delimited chart into a table in MS Word or
import it directly into MS Excel (where the data can be copied and
pasted into a new table in MS Access) or even MS Access than it is
to manually undo the line-break on *every* row of a line-wrapped
chart (in fact, manually undoing the line-breaks caused by newsgroup
posting is a huge pain in the neck).

Note: In a comma delimited list, it is not absolutely necessary
(although it is nice) to have the data in the columns lined straight
up and down, like I have in my examples below. When the data is
finally imported into MS Access, a quick glance at the table in
datasheet view will show things lined up straight. It is not
necessary to expend extra effort on your chart here. (The right
data does have to be in the right position of each row of the chart,
of course.)

Note: Use the real table and column names whenever possible. Use
invented table names and column names (like I use below in my
example) only when you absolutely have to.

Note: When naming the columns on this chart, use the same column
names as is the table structures above. Using shortened names (or
completely different names, for whatever reason) may save space and
prevent line-wraps, but it can be severely confusing. If the chart
gets too "wide", make two (or more) charts if you have to, as noted
above in Table Structures.

Note: Please include just enough rows of sample data so that
sufficient tests of the various possibilities ("test cases") can be
made.

Note: Please do not attempt to post endless rows of data. 3-5 rows
are probably the minimum, and 10-20 row are probably the normal
maximum. (Post only what is necessary, and no more.)

Note: Please try and use real data when possible. However, real
people's personal information, or private information (banking,
proprietary, etc.), should never be posted. When you have
information that cannot be posted, you will have to invent test data
that can produce results similar to what the real data would
produce.


MyTableOne
MyTableOneID, ColTwo, ColThree
1, 2, a
3, 4, b
5, 6, c

MyTableTwo (Part One)
MyTableTwoID, MyTableOneID, ColThree, ColFour, ColFive
1, 5, 1, 01/01/06, 1.01


MyTableTwo (Part Two)
ColSix, ColSeven, ColEight
-1, g, h

-------------------------------

Desired Results

.. . . <whatever it is you want your query to produce; "the right
stuff", if you will forgive the pun>

(Same chart style as found in the Sample Data section.)

-------------------------------

Query:

Your SQL query code attempts to date. (If "SQL code" throws you for
a loop, open your Query in Design View, and then use the menus, View

SQL View, to switch to a window that will show the SQL code. Copy

and paste that into your new post to the newsgroup.)

Note: There is a huge temptation to merely copy and past the SQL
code. Usually, this is completely unreadable, and whoever reads it
must re-align the code in order to make heads or tails of it (yes,
there are a few out there who can read endless unbroken streams of
code packed together, but I am not one of them). If you know how,
spend some time straightening out and aligning the SQL before
posting it.

Note: In some situations, of course, you will have no query or SQL
code at all.

-------------------------------

Current Results:

.. . . <the incorrect results the current query(s) is producing>

You may not have any current results if your query is not working or
if you have not been able to create a query so far.

(Same chart style as found in the Sample Data section.)

-------------------------------

Lots Of Work:

Does all this sound like a lot of work?

Remember, whatever work you haven't done will have to be done by
whoever tries to answer your question.

Any information that is not included may have to be asked for,
necessitating additional posts (sometimes many) before someone can
begin answering your question.

Time spent doing these things is time spent not answering your
question.

-------------------------------

I hope that the above can be of assistance in helping you receive an
answer to your various MS Access questions.


Sincerely,

Chris O.

 

[Chris2]

Chris,

Thanks for your reply. Your helped me along until I got it but one thing as
I thought would happen. Because I need a total count for each plan it would
only work in a report. When you have multiple of anything you will get that
in your results as you already know. Anyway here is what I came up with that
works almost what I needed but still not exactly..

TKM,

SELECT CMPSOWNER_FUNDDESC.FD_DESC_CD
,Count(CMPSOWNER_UNITS_BUY_SELL.BUY_SELL_UNIT_CT) AS [TOTAL
FUNDS]
FROM CMPSOWNER_FUNDDESC
INNER JOIN
CMPSOWNER_UNITS_BUY_SELL
ON CMPSOWNER_FUNDDESC.FD_DESC_CD =
CMPSOWNER_UNITS_BUY_SELL.FD_DESC_CD
GROUP BY CMPSOWNER_FUNDDESC.FD_DESC_CD;

Ok, I now have your current SQL attempt (as discussed in the post I
made a few minutes ago on another branch of this thread).

I still don't know what is supposed to generate (with certainty),
and what it is generating right now that does not match (and
therefore cannot offer a diagnosis that is anything other than
another "shot in the dark").

A few simple charts of actual data (source, desired results, and
current incorrect results) are worth a thousand words.


Sincerely,

Chris O.

 

[Guest]

Nope you did just find. With your help it got me sarted and running like the
wind. Thanks again

Chris,

Thanks for your reply. Your helped me along until I got it but one thing as
I thought would happen. Because I need a total count for each plan it would
only work in a report. When you have multiple of anything you will get that
in your results as you already know. Anyway here is what I came up with that
works almost what I needed but still not exactly..

TKM,

SELECT CMPSOWNER_FUNDDESC.FD_DESC_CD
,Count(CMPSOWNER_UNITS_BUY_SELL.BUY_SELL_UNIT_CT) AS [TOTAL
FUNDS]
FROM CMPSOWNER_FUNDDESC
INNER JOIN
CMPSOWNER_UNITS_BUY_SELL
ON CMPSOWNER_FUNDDESC.FD_DESC_CD =
CMPSOWNER_UNITS_BUY_SELL.FD_DESC_CD
GROUP BY CMPSOWNER_FUNDDESC.FD_DESC_CD;

Ok, I now have your current SQL attempt (as discussed in the post I
made a few minutes ago on another branch of this thread).

I still don't know what is supposed to generate (with certainty),
and what it is generating right now that does not match (and
therefore cannot offer a diagnosis that is anything other than
another "shot in the dark").

A few simple charts of actual data (source, desired results, and
current incorrect results) are worth a thousand words.


Sincerely,

Chris O.

 

[Chris2]

Nope you did just find. With your help it got me sarted and running like the
wind. Thanks again

TKM,

Excellent!

I feel like I didn't do a whole lot, but you are definitely welcome.


Sincerely,

Chris O.

 

[John Spencer]

Select View: Totals from the menu when you have the query open in design
view.