Grouping Problem (I think) (1 Viewer)

[sandhurstUK]

Below is the code from a qry in my database, which shows the work due per individual per month. However, what I need it to show is the totals grouped by month due.

Example:
The qry results currently display say 5 or 6 entries for Jul as the underlying data is the actual day a site is overdue but is displayed in MMM/YY format. What it needs to do is add up all the records for the months so that it then displays say Jul 50 IUs, Aug 75 IUs, Sept 150 IUs etc etc. (IU is the abbreviation for inspection unit, which is a straight number format).

SELECT [Work Due Grouped qry].[Surveyor Name], [Work Due Grouped qry].[Next Insp Date], Sum([Work Due Grouped qry].[SumOfIU's]) AS [SumOfSumOfIU's], Sum([SumOfIU's]/23) AS Expr1FROM [Work Due Grouped qry]
GROUP BY [Work Due Grouped qry].[Surveyor Name], [Work Due Grouped qry].[Next Insp Date];

All offers of assistance greatfully accepted

 

[pdx_man]

A quick and nifty way of doing this is through a report. You can use the Sorting and Grouping to group your dates by month and use the groups footer to summarize.

 

[sandhurstUK]

Thanks for that - The only problem is that the data results are to be displayed on the screen as a form, which incidentally defaults to datasheet display. The results of the qry are not required as a printable output.

Just a point - If I change all the dates within each month to fool the system into thinking all the work is due on the same day ie 01/10/02 (UK format date) then the qry results look fine. The problem is that not all the work is du on the same day as you can imagine.

Essentially I am looking for a solution which will add up all the work for each month, and display a total regardless of what the due date is.

Thanks
Ian

 

[simongallop]

The way to do it is change the date field bit being formated "mm/yy". Have called it Mnth in example

SELECT [Surveyor Name], Format([Next Insp Date],"mm/yy") AS Mnth, Sum([SumOfIU's]) AS [SumOfSumOfIU's], Sum([SumOfIU's]/23) AS Expr1 FROM [Work Due Grouped qry]
GROUP BY [Surveyor Name], Format([Next Insp Date], "mm/yy");

HTH

 

[pdx_man]

OK,
Create a field in an empty column in the query grid (QBE) that parses out the month and year.

SELECT [Work Due Grouped qry].[Surveyor Name], [Work Due Grouped qry].[Next Insp Date], Sum([Work Due Grouped qry].[SumOfIU's]) AS [SumOfSumOfIU's], Sum([SumOfIU's]/23) AS Expr1FROM [Work Due Grouped qry]
GROUP BY [Work Due Grouped qry].[Surveyor Name], DatePart('m',[Next Insp Date]) & DatePart('yyyy',[Next Insp Date]);

 

[sandhurstUK]

Excellent fellas, many thanks.

Slight tweak to Harry's code with regards to mm/yy format and all worked fine.

I was unsure of what was meant by parse in the PDX_man code and the Help file didn't help. If you can explain what you want me to do in layman terms, I will give that code a shot as well.

Thanks again guys - you have got me out of a tight spot here.

Ian

 

[pdx_man]

Glad it worked. Both of our solutions were basically the same. Parsing is breaking down a element to its smaller parts. So, let's look at a date, say 09/25/2002 (US). To parse it would be to take its month part; DatePart('m',[Next Insp Date]), then its day part; DatePart('d',[Next Insp Date]), and its year part; DatePart('yyyy',[Next Insp Date]) as three distinct pieces. To get what you were looking for, we need the month and year parts and we concatonated them together using the '&". Harry's solution does the same thing using the Format statement. I think his is a better solution as it uses one less function and that is always good! Hope that answers your question.

 

[sandhurstUK]

Crystal clear

Many thanks
Ian

 

[sandhurstUK]

FAO - Harry,

Your code works really well and gives me all the calculated data exactly right, except for when I change the Format in the SQL from "mm/yy" to "mmm/yy" to show the result as Sep/02 rather than 09/02. The results are then displayed in alphabetic order (Aug - Dec - Jul - Sept etc etc), rather than by month order, I have played around with ASC and DESC but to no avail.

Any ideas?

Ian

 

[simongallop]

Create a duplicate date field so you have Datefiel1:Format([Next Insp Date],"mmm-yy") and Datefield2: Format([Next Insp Date],"mm/yy") and sort on Datefield2. You can uncheck the datefield2 Show box so that it isn't visible but it will sort it in the required order.

HTH

 

[sandhurstUK]

Cheers Harry

Job jobbed !!

Ian