Merge week numbers is this possible?

[sdel_nevo]

Hi Guys

i have a queury that i am having troble with, the sgl for the query is this

SELECT DatePart("ww",[unexdata.DueDate]) AS [DATE], UNEXProcess.Process, UNEXAreaCapacity.KGPW, Sum(UNEXProcess.QTYOUTST) AS SumOfQTYOUTST, UNEXData.Completed, UnexPrePlannedCapacityQuery.PreplannedCapacity, UnexPrePlannedCapacityQuery.[Planned Week Number]
FROM UnexPrePlannedCapacityQuery INNER JOIN ((UNEXProcess INNER JOIN UNEXAreaCapacity ON UNEXProcess.Process = UNEXAreaCapacity.Area) INNER JOIN UNEXData ON UNEXProcess.ID = UNEXData.ID) ON UnexPrePlannedCapacityQuery.Area = UNEXProcess.Process
GROUP BY DatePart("ww",[unexdata.DueDate]), UNEXProcess.Process, UNEXAreaCapacity.KGPW, UNEXData.Completed, UnexPrePlannedCapacityQuery.PreplannedCapacity, UnexPrePlannedCapacityQuery.[Planned Week Number]
HAVING (((UNEXProcess.Process)=[forms]![UnexAreas]![location]) AND ((UNEXData.Completed)=False))
ORDER BY DatePart("ww",[unexdata.DueDate]);

this returns the sum of all "qty outstanding" were the machine name = "Fully Factored" and also returns the value of the planned capacity by week number for the same machine

this is what the query returns



What i can't seem to do and to be honest im not sure how to do it is the following.

i would like to "Planned Week Number" in this case 39 to only show once, so in this case it would appear between date "36" and "40" and the "PlannedCapacity" row would only show "1000" for this entry

is this even possible?

I hope this makes sence
Kind regards

Steve

[CJ_London]

remove the DATE column from your query - Also be aware that DATE is a reserved word (it is a function that returns today) and using it can create unexpected errors

[sdel_nevo]

Hi Ajax

Many thanks for the heads up

Kind regards
Steve