Summing Values Based On Other Field

[JeffGeorge]

This is my query:

SELECT SMCITYP.CI_GROUP, dbo_MSSHIPD.YARD, dbo_MSSHIPD.COMMODITY, dbo_MSSHIPD.DESCRIPTION, Sum(dbo_MSSHIPD.SHIP_NET) AS SumOfSHIP_NET, dbo_MSSHIPH.SHIP_DATE, dbo_MSSHIPH.CONTROL, dbo_CIMASTER.COMM_TYPE
FROM ((dbo_MSSHIPD INNER JOIN dbo_MSSHIPH ON dbo_MSSHIPD.CONTROL = dbo_MSSHIPH.CONTROL) INNER JOIN dbo_CIMASTER ON (dbo_MSSHIPD.YARD = dbo_CIMASTER.YARD) AND (dbo_MSSHIPD.COMMODITY = dbo_CIMASTER.COMMODITY)) INNER JOIN SMCITYP ON dbo_CIMASTER.COMM_TYPE = SMCITYP.CODE
GROUP BY SMCITYP.CI_GROUP, dbo_MSSHIPD.YARD, dbo_MSSHIPD.COMMODITY, dbo_MSSHIPD.DESCRIPTION, dbo_MSSHIPH.SHIP_DATE, dbo_MSSHIPH.CONTROL, dbo_CIMASTER.COMM_TYPE
HAVING (((SMCITYP.CI_GROUP)="FER" Or (SMCITYP.CI_GROUP)="NF") AND ((dbo_MSSHIPH.SHIP_DATE)=#8/7/2013#))
ORDER BY SMCITYP.CI_GROUP, dbo_MSSHIPD.YARD, dbo_MSSHIPD.COMMODITY;

My goal is to, within my groupings, sum the value of that field based on the COMMODITY field, i.e. I only want to see one entry for COMMODITY with SHIP_NET showing the sum of all values for that value of COMMODITY. However, this query obviously isn't correct because I am still seeing multiple entries for the same COMMODITY value. I'm sure it's something simple that I'm missing but I can't figure out what it is. Any ideas?

The basic structure for the query should be master grouping by CI_GROUP. Within CI_GROUP there should be a sort by YARD. Within YARD there should be a single COMMODITY record with all SHIP_NET for that COMMODITY summed.

[June7]

You have fields other than Commodity in the grouping. So the aggregation is applied to the combined fields. If a commodity is associated with multiple CI_Group and/or Yard values, there will be a summation for each combination, therefore the same commodity will list multiple times. The more fields in the grouping, the more unique the records and the more repetition of values.

Consider building a report using Grouping & Sorting with aggregate calcs in group footers. Report allows display of detail records as well as summary calcs.

[JeffGeorge]

The strange thing is that I created the same query in MSACCESS and it did exactly what I wanted it to. It must be some sort of difference in the engines or something. I guess a report is the way to go but my reports usually don't turn out very pretty!

[June7]

I am confused. The query is in MS Access, you posted the file. Where else are you building query?

[JeffGeorge]

Yes, it is in Access, using ODBC connections to MS-SQL tables. When I couldn't get the Access query to give me the output I wanted I tried writing the SQL version of the same query. In SQL the field summed as I desired even though both queries were the same using their respective syntax. Where I ended up with 60 records using Access I only ended up with 21 in SQL.

[June7]

No idea, never used MS-SQL. I would have expected the same results from each.

[JeffGeorge]

I would have expected the same results, as well. That's what made me think I was doing something wrong in the Access query.

[JeffGeorge]

You have fields other than Commodity in the grouping. So the aggregation is applied to the combined fields. If a commodity is associated with multiple CI_Group and/or Yard values, there will be a summation for each combination, therefore the same commodity will list multiple times. The more fields in the grouping, the more unique the records and the more repetition of values.

The following is some output with a slight variation in the summing attempt. Note how, despite the fact that the CI_GROUP and YARD are the same for these records, Access still refuses to sum the Ship_Net field. Is there a special trick in getting a that to sum?

CI_GROUP	YARD	COMMODITY	DESCRIPTION	Ship_Net	SHIP_DATE	CONTROL	COMM_TYPE
FER	01	650	#1 STEEL	0	08/12/13	109309	HMG
FER	01	650	#1 STEEL	0	08/12/13	109310	HMG
FER	01	650	#1 STEEL	41000	08/12/13	109308	HMG
FER	01	650	#1 STEEL	42900	08/12/13	109307	HMG
FER	01	650	#1 STEEL	41940	08/12/13	109306	HMG
FER	01	650	#1 STEEL	43380	08/12/13	109304	HMG
FER	01	650	#1 STEEL	43400	08/12/13	109252	HMG
FER	01	650	#1 STEEL	0	08/12/13	109311	HMG

[June7]

You include Control in the Group By. It is different for each record so each record is unique.

[JeffGeorge]

You include Control in the Group By. It is different for each record so each record is unique.

Ah! That fixed it! I'm not even sure how that field ended up in there as I didn't need it. And that's why my SQL query worked; I left that field out. Thanks for your help!