fill in the blanks

[spinkung]

Hi All,

I'm having a problem while i'm trying to create a data set to generate a report.

So, the challenge....
- I am tracking the job related skills score of a person by Year, Month, Week
- The report is a line chart showing the improvement over last 52 weeks

What i have so far...
- A query that has the year, month and week for the last 52 weeks
- A query of employee, year, month, week and total score

The problem
- I join the emp_score query to the last_52wk query on year, month, week but i only get matches on the dates where skills were update so i'm left with

Year......Month......Week......Emp......Score
2010......1............1............Me........25
2010......1............2...........................
2010......1............3...........................
2010......1............4...........................
2010......2............1............Me........32
2010......2............2...........................
2010......2............3...........................

..so when i try to plot a line graph i get only 2 point. what i need is to get the blanks to fill up with the last score, i.e.

Year......Month......Week......Emp......Score
2010......1............1............Me........25
2010......1............2............Me........25
2010......1............3............Me........25
2010......1............4............Me........25
2010......2............1............Me........32
2010......2............2............Me........32
2010......2............3............Me........32

..this allows for the line graph to show a progressive line.

Anyone got any ideas for getting this done in a query??

Thanks,
Spin.

 

[namliam]

what does your current source data look like?
What query do you have currently?
How are you populating Year/Month/Week?

This is doable though not pretty in Access... Oracle has some nifty tooling for it though (Partition by)

In access the 'basic' idea is to make a 'junction' query to find the most recent score to be able to propegate that into blanks. Which will require a partial outer join at the very least.

This partial outer join you make with the query and the scores table.
Having the split between year/month/week is actually a problem here as you cannot do 'proper' comparing on them...
To solve it change it into a number
2010011
2010012

Year * 1000 + Month * 10 + Week
Does that for you... this way 2009124 is less than 2010011 without any problems.

Some air code

Select w.year, w.month, w.week
, Int(max(e.Year * 1000 + e.Month * 10 + e.week)/1000) ScoreYear
, Int(Mid(max(e.Year * 1000 + e.Month * 10 + e.week),5,2) ScoreMonth
, Int(Right(max(e.Year * 1000 + e.Month * 10 + e.week),1) ScoreWeek
From  WeekQeury w, EmployeeQuery e
where e.Year * 1000 + e.Month * 10 + e.Week <= w.Year * 1000 + w.Month * 10 + w.Week
Group by w.year, w.month, w.week

Now note, something like this might work, but it is NOT a great solution and DOES NOT scale well i.e. As you get more and more data, you will get slower and much slower response.

 

[spinkung]

Thanks

My date table looks like

Year......Month......Week......NewDate(your suggestion)
2010......1............1............2010011
2010......1............2............2010012
2010......1............3............2010013
2010......1............4............2010014
2010......1............5............2010015
2010......1............5............2010016
...

My skills table looks like

Year......Month......Week......NewDate......Name......Score
2010......1............1............2010012......Me..........150
2010......1............3............2010015......Me..........160

but i still can't get the result to do what i'm after??

 

[namliam]

No need to actually make the NewDate column.... it is "created" by the query (air code) that I gave you in my previous post...

Now that you have created it ... it can make the SQL look a bit easier:

Select w.NewDate
, max(e.newdate) ScoreNewDate
From  WeekQeury w, EmployeeQuery e
where e.newdate <= w.NewDate
Group by w.year, w.month, w.week

Use this query as a junction table between your weeks and your scores... Joining ScoreNewDate to Scores table and NewDate back into your weeks query.