Counting Unique Data 2

[TeeSee]

I just happened to read the last post with the same name and learned
about the "GROUP BY" query. I am enclosing my SQL and have the
following questions. This is a trial DB with four tblSapDown2006
records and two unique tblMaterialMaster.LocalGroup categories.

SELECT tblMaterialMaster.SISItemCode AS tblMaterialMaster_SISItemCode,
tblMaterialMaster.LocalGroup, tblMaterialMaster.[Material description],
tblSapDown2006.SISItemCode AS tblSapDown2006_SISItemCode,
Sum(tblSapDown2006.LineDollars) AS SumOfLineDollars
FROM tblMaterialMaster INNER JOIN tblSapDown2006 ON
tblMaterialMaster.SISItemCode = tblSapDown2006.SISItemCode
GROUP BY tblMaterialMaster.SISItemCode, tblMaterialMaster.LocalGroup,
tblMaterialMaster.[Material description], tblSapDown2006.SISItemCode;

When I create the "group by" query GROUP BY is initially in each field
of the total line in the grid. I obviously changed that to "sum" in the
LineDollars field. If I lremove "group by" from any of the fields I get
an error. So I think I have three questions.......
1) What is actually happening when I run the query? Is it grouping by
the left most field and working to the right. I don't want grouping in
all fields.
2) How do I change the SQL in oder to group by "LocalGroup"?
3) Why does "material description" have [] brackets when other fields
from same table do not?

Looking forward to replies. Best regards.

 

[tina]

3) Why does "material description" have [] brackets when other fields

from same table do not?

because there is a space in the field name. recommend you avoid spaces and
all special characters in anything *you* name in a database, except for
using the underscore character ( _ )where you feel it's needed.

2) How do I change the SQL in oder to group by "LocalGroup"?

move the LocalGroup field to the left-most position in the query design
grid.

1) What is actually happening when I run the query? Is it grouping by
the left most field and working to the right.

yes, the query acts on the left-most field first, then the next field, etc.

I don't want grouping in
all fields.

AFAIK, there's no way to group, or sum (or any of the other aggregating
actions) on some fields in a Totals query, and not others. which, if you
think about it, makes sense; how can you aggregate the data in some fields
in a group of records, but *not* aggregate other fields in those same
records?

if you need to see detail data of individual records, but want the records
grouped into LocalGroup categories and a sum of LineDollars for each
LocalGroup, then suggest you change the query back to an ordinary SELECT
query, and use the Grouping & Sorting dialog in *report* design to group the
records by LocalGroup and put a calculated control in the group footer
section to sum the LineDollars.

hth

I just happened to read the last post with the same name and learned
about the "GROUP BY" query. I am enclosing my SQL and have the
following questions. This is a trial DB with four tblSapDown2006
records and two unique tblMaterialMaster.LocalGroup categories.

SELECT tblMaterialMaster.SISItemCode AS tblMaterialMaster_SISItemCode,
tblMaterialMaster.LocalGroup, tblMaterialMaster.[Material description],
tblSapDown2006.SISItemCode AS tblSapDown2006_SISItemCode,
Sum(tblSapDown2006.LineDollars) AS SumOfLineDollars
FROM tblMaterialMaster INNER JOIN tblSapDown2006 ON
tblMaterialMaster.SISItemCode = tblSapDown2006.SISItemCode
GROUP BY tblMaterialMaster.SISItemCode, tblMaterialMaster.LocalGroup,
tblMaterialMaster.[Material description], tblSapDown2006.SISItemCode;

When I create the "group by" query GROUP BY is initially in each field
of the total line in the grid. I obviously changed that to "sum" in the
LineDollars field. If I lremove "group by" from any of the fields I get
an error. So I think I have three questions.......
1) What is actually happening when I run the query? Is it grouping by
the left most field and working to the right. I don't want grouping in
all fields.
2) How do I change the SQL in oder to group by "LocalGroup"?
3) Why does "material description" have [] brackets when other fields
from same table do not?

Looking forward to replies. Best regards.

 

[John Spencer]

1. Every field in the SELECT clause of an aggregate query must either use one of
the aggregate functions (Min, Max, Sum, Count, First, Last, etc) or must use
Group by. So you can either drop fields you don't want to group by or use one
of the aggregate functions.

2. If all you want is the sum by Local group then you would do
SELECT tblMaterialMaster.LocalGroup
, Sum(tblSapDown2006.LineDollars) AS SumOfLineDollars
FROM tblMaterialMaster INNER JOIN tblSapDown2006 ON
tblMaterialMaster.SISItemCode = tblSapDown2006.SISItemCode
GROUP BY tblMaterialMaster.LocalGroup;

3. Because Material Description has a space in it. And SQL uses spaces as
separators. In order to make SQL understand that this is one object you must
wrap the field name in [] brackets. The same rule applies for fields that
contain other non-alphanumeric characters (other than the underscore). For
instance, if the name was Material-Description the problem would be that the SQL
interpreter would decide that your wanted to subtract Description from
Material. To keep that from happening you would need to identify the field as [Material-Description]

 

[David F Cox]

Exactly which records to you want to see the sum(s) for?