Total: Max and Min (1 Viewer)

[Christopherusly]

I have a table - which lists transactions for jobs, i.e. each entry consists of a member of staff, their grade, hours they have worked, cost and charge out rates etc.

From this i have a query - which runs max and min totals on the Fiscal Week Year column, now for the min total - it quite happily gives me 01.2009 - so january 2009, however for the max total value is gives me 53.2009 even tho there is a week 18.2010 record, how do i get access to display the actual maximum total week value rather than the one it thinks is the greatest value.

I have searched this forum and others from high to low to no avail, any suggestions are most welcome, i suspect i am likely to want to kick myself for not realising how to do it ...

Regards

Christophe

 

[boblarson]

You have the fiscal week set up wrong. It should be year first and THEN the week.

So, the way you have it 53 comes before 18.

 

[Christopherusly]

yet another annoyance with sap, my database is based on a SAP BI report which is exported into excel, then imported into my database, sadly the date format is MM/YYYY is there no other way to get round this ?

 

[boblarson]

How are you getting the weeks then?

 

[Christopherusly]

Week Ending BI Week
01/01-2010 01.2010
08/01-2010 02.2010
15/01-2010 03.2010
22/01-2010 04.2010
29/01-2010 05.2010
05/02-2010 06.2010
12/02-2010 07.2010
19/02-2010 08.2010
26/02-2010 09.2010
05/03-2010 010.2010
12/03-2010 011.2010
19/03-2010 012.2010
26/03-2010 013.2010
02/04-2010 014.2010
09/04-2010 015.2010
16/04-2010 016.2010
23/04-2010 017.2010
30/04-2010 018.2010
07/05-2010 019.2010
14/05-2010 020.2010
21/05-2010 021.2010
28/05-2010 022.2010
04/06-2010 023.2010
11/06-2010 024.2010
18/06-2010 025.2010
25/06-2010 026.2010
02/07-2010 027.2010
09/07-2010 028.2010
16/07-2010 029.2010
23/07-2010 030.2010
30/07-2010 031.2010
06/08-2010 032.2010
13/08-2010 033.2010
20/08-2010 034.2010
27/08-2010 035.2010
03/09-2010 036.2010
10/09-2010 037.2010
17/09-2010 038.2010
24/09-2010 039.2010
01/10-2010 040.2010
08/10-2010 041.2010
15/10-2010 042.2010
22/10-2010 043.2010
29/10-2010 044.2010
05/11-2010 045.2010
12/11-2010 046.2010
19/11-2010 047.2010
26/11-2010 048.2010
03/12-2010 049.2010
10/12-2010 050.2010
17/12-2010 051.2010
24/12-2010 052.2010
31/12-2010 053.2010

is the format that the date structure is in, sadly, the date reference in the report only uses its only week numbering system i.e. 053.2010 rather than a week ending date which would make life so much easier. (i hope this answers your question)

 

[Brianwarnock]

You could create an alias to work with.

let wy be your current field
yearweek: Val(Right([wy],4) & (Left([wy],InStr([wy],".")-1)))

Brian