formatting by week number

[trevorC via AccessMonster.com]

Hi,
I'm trying to set get a report of repairs by week, I have the following SQL
code as a sample.

My problem is that when i export to excel the week number is replaced with
"ww".
Also i want to stop the output result from then dividing up the week into
seperate parts for each day of repairs - EG

Qty "ww" - should be week # repair date

9 ww LF13781 STDC 03-Mar-09
11 ww LF13782 PTDC 03-Mar-09
9 ww LF13781 STDC 04-Mar-09
15 ww LF13782 PTDC 04-Mar-09
25 ww LF13781 STDC 05-Mar-09
12 ww LF13782 PTDC 05-Mar-09
14 ww LF14410 BDC 05-Mar-09


SELECT Count(gamrep.[Serial #]) AS Quantity, gamrep.Out AS [Week Completed],
gamrep.[Part #], [Part Number Reference].Description, gamrep.Out AS [Week
Complete]
FROM gamrep LEFT JOIN [Part Number Reference] ON gamrep.[Part #] = [Part
Number Reference].[Part #]
GROUP BY gamrep.[Customer Name], gamrep.[despatch date], gamrep.Out, gamrep.
[Part #], [Part Number Reference].Description, gamrep.Out
HAVING (((gamrep.[Customer Name])="wayfarer") AND ((gamrep.[despatch date])
Is Null) AND ((gamrep.Out) Is Not Null) AND ((gamrep.Out) Is Not Null));

 

[KARL DEWEY]

Try this --
SELECT Count(gamrep.[Serial #]) AS Quantity, Format([gamrep].[Out], "ww") AS
[Week Completed], gamrep.[Part #], [Part Number Reference].Description,
gamrep.Out AS [Week Complete]

 

[trevorC via AccessMonster.com]

Thanks Karl,

That has fixed the week numbers for the excel report.
Can you help with the grouping of the same part for the week but different
date completed, as shown thier are 2 entries of each part for March 3, 4 and
5, I would like them grouped and a total shown. I can only think of using
imbeded queries but aren't sure how to do this with date formats.
Thanks for your assistance.
Trevor.

 

[KARL DEWEY]

Post example of how you want the data to look.

 

[trevorC via AccessMonster.com]

Thanks for the updart Karl,

this is the current result below


9 ww LF13781 STDC 03-Mar-09
11 ww LF13782 PTDC 03-Mar-09
9 ww LF13781 STDC 04-Mar-09
15 ww LF13782 PTDC 04-Mar-09
25 ww LF13781 STDC 05-Mar-09
12 ww LF13782 PTDC 05-Mar-09
14 ww LF14410 BDC 05-Mar-09

first col is Qty then week #, part #, description, date completed.

the crosstab seems to group records as required unless there is units
completed on different days of the week - from above the top line shows Qty 9
stdc for 3-March, and then Qty 9 stdc 4-March and Qty 25 stdc for 5-March.
What i need is these to be grouped as well

final output =

Qty 43 stdc for Week 10, and

Qty 38 ptdc for Week 10

and so on for all repairs completed on a weekly basis
It is grouping the units by week and showing them grouped by date during that
week.

hope this helps as a good descriptrion for what i require.

I appriciate your assistance in helping me to resolve this issue.
regards
Trevor

 

[trevorC via AccessMonster.com]

Hi Karl,
I found the solution to the grouping problem as shown below,

TRANSFORM Sum([Wayfarer Weekly Output].SumOfQuantity) AS SumOfSumOfQuantity
SELECT [Wayfarer Weekly Output].[Part #], [Wayfarer Weekly Output].
Description, Count([Wayfarer Weekly Output].SumOfQuantity) AS [Total Of
SumOfQuantity]
FROM [Wayfarer Weekly Output]
GROUP BY [Wayfarer Weekly Output].[Part #], [Wayfarer Weekly Output].
Description
PIVOT [Wayfarer Weekly Output].[Week Completed];

I still need to use the original query then run it through the code below,
this gives the correct results but the Week numbers are being sorted by
number (generic i think) not by week number

they come out as shown

----week numbers----
Part # Description Total 10 11 12 4 41 6 8 9
LF13781 PTDC 73 40 33
LF13782 STDC 62 40 22

I can manualy rearange the fields but if it is posible to do this with code
it will make things easier
regards
Trevor