Help with sum of max values for a specific date field

P

PKT

Can anyone help with this query? I'm having trouble getting the correct sum
results in a query.
Data is coming from this source data:
DPV Var_ Van.Date Van.Volume Ord.Date Prod.Date Prod.Volume
XX test 3/31/08 284 3/24/08 5/12/08 283
XX test 3/31/08 284 3/24/08 5/13/08 1
YY test 3/31/08 733 3/24/08 5/9/08 2
YY test 3/31/08 733 3/24/08 5/12/08 730
YY test 3/31/08 733 3/24/08 5/13/08 1

What I want is the total vanning volume for 3/31/08 Van.Date which should
be=1017. But because I only have Prod.Volume on 5/9/08 for DPV YY field, the
Resulting sum row with that prod. date shows only 733 not 1017.
Below are my query results=NG
DPP LinePath Ord.Date Van.Date Van.Volume Prod.Date Prod.Volume
104U 11 080324 080331 1017 080512 1013
104U 11 080324 080331 1017 080513 2
104U 11 080324 080331 733 080509 2
Here is what I want=OK
DPP LinePath Ord.Date Van.Date Van.Volume Prod.Date Prod.Volume
104U 11 080324 080331 1017 080512 1013
104U 11 080324 080331 1017 080513 2
104U 11 080324 080331 1017 080509 2

QUERY:
SELECT DISTINCT SeriesGrouping.DPP, LinePath.LinePath,
Format(DiagramSimMonthly.v,"yymmdd") AS Ord.Date,
Format(DiagramSimMonthly.Van.Date,"yymmdd") AS Van.Date,
CStr(Sum([DiagramSimMonthly].[Van.Volume])) AS Van.Volume,
Format(DiagramSimMonthly.Prod.Date,"yymmdd") AS Prod.Date,
CStr(Sum([UsedVanningVolume])) AS Prod.Volume
FROM W_SDS_VanContentActual INNER JOIN ((DiagramSimMonthly INNER JOIN
SeriesGrouping ON (DiagramSimMonthly.Series = SeriesGrouping.Series) AND
(DiagramSimMonthly.DPP = SeriesGrouping.DPP) AND (DiagramSimMonthly.DPV =
SeriesGrouping.DPV) AND (DiagramSimMonthly.Variation =
SeriesGrouping.Variation)) INNER JOIN LinePath ON SeriesGrouping.Series =
LinePath.SeriesName) ON (W_SDS_VanContentActual.Van.Date =
DiagramSimMonthly.Van.Date) AND (W_SDS_VanContentActual.Ord.Date =
DiagramSimMonthly.Ord.Date)
GROUP BY SeriesGrouping.DPP, LinePath.LinePath,
Format(DiagramSimMonthly.Ord.Date,"yymmdd"),
Format(DiagramSimMonthly.Van.Date,"yymmdd"),
Format(DiagramSimMonthly.Prod.Date,"yymmdd"), DiagramSimMonthly.PorA,
SeriesGrouping.PatternName
HAVING (((SeriesGrouping.DPP)="104U") AND ((LinePath.LinePath)="11") AND
((Format(DiagramSimMonthly.Van.Date,"yymmdd"))=80331) AND
((DiagramSimMonthly.PorA)="P") AND ((SeriesGrouping.PatternName) Like "Group
5" Or (SeriesGrouping.PatternName) Like "Group 6" Or
(SeriesGrouping.PatternName) Like "Group 7")) OR
(((SeriesGrouping.DPP)="TMMI") AND ((DiagramSimMonthly.PorA)="P") AND
((SeriesGrouping.PatternName) Like "Group 1" Or (SeriesGrouping.PatternName)
Like "Group 2" Or (SeriesGrouping.PatternName) Like "Group 3" Or
(SeriesGrouping.PatternName) Like "Group 4")) OR
(((SeriesGrouping.DPP)="102C") AND ((DiagramSimMonthly.PorA)="P") AND
((SeriesGrouping.PatternName) Like "09 TMMC North" Or
(SeriesGrouping.PatternName) Like "10 TMMC North" Or
(SeriesGrouping.PatternName) Like "11 TMMC North" Or
(SeriesGrouping.PatternName) Like "08MY TMMC North")) OR
(((SeriesGrouping.DPP)="104P") AND ((DiagramSimMonthly.PorA)="P") AND
((SeriesGrouping.PatternName) Like "09 NUMMI Pass" Or
(SeriesGrouping.PatternName) Like "10 NUMMI Pass" Or
(SeriesGrouping.PatternName) Like "11 NUMMI Pass"))
ORDER BY LinePath.LinePath, Format(DiagramSimMonthly.Ord.Date,"yymmdd"),
Format(DiagramSimMonthly.Van.Date,"yymmdd"),
CStr(Sum([DiagramSimMonthly].[Van.Volume])),
Format(DiagramSimMonthly.Prod.Date,"yymmdd");
 
H

Hafeez Esmail

Hey PKT,

I haven't looked at your query in detail, as it is a bit difficult to read.
But looking at your data set it looks like the results you've posted are
what I would expect you to get.
The grouping is always performed on the lowest level of detail (the more
fields you include, the more groups granular your scope will be). The
aggregate functions (like SUM()) only take place after the grouping is
performed. So if you've included the Prod.Date field, that means you will be
grouping by that field as well and so the Sum(Van.Vol) will be based on
records that have unique Van.Date, Ord.Date, Prod.Date

If you don't need to show the Prod.Date (which doesn't look like the case
based on what you posted you wanted), then I think what you're looking for
something like....

SELECT Order.Date, Van.Date, SUM(Van.Vol), SUM(Prod.Vol)
FROM source_data
WHERE Van.Date = #3/31/08#
GROUP BY Order.Date, Van.Date

HTH
Hafeez Esmail

PKT said:
Can anyone help with this query? I'm having trouble getting the correct sum
results in a query.
Data is coming from this source data:
DPV Var_ Van.Date Van.Volume Ord.Date Prod.Date Prod.Volume
XX test 3/31/08 284 3/24/08 5/12/08 283
XX test 3/31/08 284 3/24/08 5/13/08 1
YY test 3/31/08 733 3/24/08 5/9/08 2
YY test 3/31/08 733 3/24/08 5/12/08 730
YY test 3/31/08 733 3/24/08 5/13/08 1

What I want is the total vanning volume for 3/31/08 Van.Date which should
be=1017. But because I only have Prod.Volume on 5/9/08 for DPV YY field, the
Resulting sum row with that prod. date shows only 733 not 1017.
Below are my query results=NG
DPP LinePath Ord.Date Van.Date Van.Volume Prod.Date Prod.Volume
104U 11 080324 080331 1017 080512 1013
104U 11 080324 080331 1017 080513 2
104U 11 080324 080331 733 080509 2
Here is what I want=OK
DPP LinePath Ord.Date Van.Date Van.Volume Prod.Date Prod.Volume
104U 11 080324 080331 1017 080512 1013
104U 11 080324 080331 1017 080513 2
104U 11 080324 080331 1017 080509 2

QUERY:
SELECT DISTINCT SeriesGrouping.DPP, LinePath.LinePath,
Format(DiagramSimMonthly.v,"yymmdd") AS Ord.Date,
Format(DiagramSimMonthly.Van.Date,"yymmdd") AS Van.Date,
CStr(Sum([DiagramSimMonthly].[Van.Volume])) AS Van.Volume,
Format(DiagramSimMonthly.Prod.Date,"yymmdd") AS Prod.Date,
CStr(Sum([UsedVanningVolume])) AS Prod.Volume
FROM W_SDS_VanContentActual INNER JOIN ((DiagramSimMonthly INNER JOIN
SeriesGrouping ON (DiagramSimMonthly.Series = SeriesGrouping.Series) AND
(DiagramSimMonthly.DPP = SeriesGrouping.DPP) AND (DiagramSimMonthly.DPV =
SeriesGrouping.DPV) AND (DiagramSimMonthly.Variation =
SeriesGrouping.Variation)) INNER JOIN LinePath ON SeriesGrouping.Series =
LinePath.SeriesName) ON (W_SDS_VanContentActual.Van.Date =
DiagramSimMonthly.Van.Date) AND (W_SDS_VanContentActual.Ord.Date =
DiagramSimMonthly.Ord.Date)
GROUP BY SeriesGrouping.DPP, LinePath.LinePath,
Format(DiagramSimMonthly.Ord.Date,"yymmdd"),
Format(DiagramSimMonthly.Van.Date,"yymmdd"),
Format(DiagramSimMonthly.Prod.Date,"yymmdd"), DiagramSimMonthly.PorA,
SeriesGrouping.PatternName
HAVING (((SeriesGrouping.DPP)="104U") AND ((LinePath.LinePath)="11") AND
((Format(DiagramSimMonthly.Van.Date,"yymmdd"))=80331) AND
((DiagramSimMonthly.PorA)="P") AND ((SeriesGrouping.PatternName) Like "Group
5" Or (SeriesGrouping.PatternName) Like "Group 6" Or
(SeriesGrouping.PatternName) Like "Group 7")) OR
(((SeriesGrouping.DPP)="TMMI") AND ((DiagramSimMonthly.PorA)="P") AND
((SeriesGrouping.PatternName) Like "Group 1" Or (SeriesGrouping.PatternName)
Like "Group 2" Or (SeriesGrouping.PatternName) Like "Group 3" Or
(SeriesGrouping.PatternName) Like "Group 4")) OR
(((SeriesGrouping.DPP)="102C") AND ((DiagramSimMonthly.PorA)="P") AND
((SeriesGrouping.PatternName) Like "09 TMMC North" Or
(SeriesGrouping.PatternName) Like "10 TMMC North" Or
(SeriesGrouping.PatternName) Like "11 TMMC North" Or
(SeriesGrouping.PatternName) Like "08MY TMMC North")) OR
(((SeriesGrouping.DPP)="104P") AND ((DiagramSimMonthly.PorA)="P") AND
((SeriesGrouping.PatternName) Like "09 NUMMI Pass" Or
(SeriesGrouping.PatternName) Like "10 NUMMI Pass" Or
(SeriesGrouping.PatternName) Like "11 NUMMI Pass"))
ORDER BY LinePath.LinePath, Format(DiagramSimMonthly.Ord.Date,"yymmdd"),
Format(DiagramSimMonthly.Van.Date,"yymmdd"),
CStr(Sum([DiagramSimMonthly].[Van.Volume])),
Format(DiagramSimMonthly.Prod.Date,"yymmdd");
Click to expand...
 
Top