last date

[shart00]

Starting with a crosstab query.

row is set to [zone] and group by
column is set to [date] and group by
value is set to [overtime] and sum

additional row is set to [roll] and *Here is the problem*

Before any adjustments made... the results would look like

Zone Monday Tuesday .......
1 10 5
2 15 6
3 5

The problem is that if the zone did not have overtime on the day the report ran no roll would show on the report.
Therefore, I set another row to [roll], but the problem is that the roll may change from day to day, therefore:
Zone Monday Tuesday... Roll
1 10 4
1 5 5
2 15 6 10
3 5 1

I have tried to use row set to [roll] using first or last but it is simply picking the highest (last) and lowest (first) number. Is there away to pick the roll number associated with the date last date that has a value, like for zone one use 5 as roll since that is the roll on Tuesday.

 

[JonFer]

Can you post your SQL and show some sample records from your table with the "roll" values?

John

http://www.interautomateconsulting.com

 

[shart00]

Give it a try...

SQL from CrossTab Query (Using FirstofRoll):

TRANSFORM Sum(tblOvertime.Overtime) AS [The Value]
SELECT tblOvertime.[Cost Center], First(tblOvertime.ROLL) AS FirstOfROLL
FROM DSRDATES, tblOvertime
WHERE (((tblOvertime.[EFF-DTE])=[Monday] Or (tblOvertime.[EFF-DTE])=[Tuesday] Or (tblOvertime.[EFF-DTE])=[Wednesday] Or (tblOvertime.[EFF-DTE])=[Thursday] Or (tblOvertime.[EFF-DTE])=[Friday] Or (tblOvertime.[EFF-DTE])=[Saturday] Or (tblOvertime.[EFF-DTE])=[Sunday]))
GROUP BY tblOvertime.[Cost Center]
PIVOT tblOvertime.Date;

SQL using Group by for Roll:

TRANSFORM Sum(tblOvertime.Overtime) AS [The Value]
SELECT tblOvertime.[Cost Center], tblOvertime.ROLL
FROM DSRDATES, tblOvertime
WHERE (((tblOvertime.[EFF-DTE])=[Monday] Or (tblOvertime.[EFF-DTE])=[Tuesday] Or (tblOvertime.[EFF-DTE])=[Wednesday] Or (tblOvertime.[EFF-DTE])=[Thursday] Or (tblOvertime.[EFF-DTE])=[Friday] Or (tblOvertime.[EFF-DTE])=[Saturday] Or (tblOvertime.[EFF-DTE])=[Sunday]))
GROUP BY tblOvertime.[Cost Center], tblOvertime.ROLL
PIVOT tblOvertime.Date;

As for showing sample records, Not to sure of how? I do not see a place to insert a picture or anything in the post.