Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004

    Unanswered: Advice on reporting multiple data types from one table

    Hi Guys

    I know this is a bread and butter question to many but I'm getting myself confused (like thats hard - not)

    I have a table Period_of_training. In here is the start and end dates of multiple training programmes set against studants ID (easy so far). Each start and end date for their period of training needs to be reported by period - These are 12 periods a year 1 - 12 . Ive solved this area of the problem. I can account for all programme starts OR all programme leavers but on seperate reports. I know need to pull the reports together so that I have

    Programme 1 2 3 4 5 6 7 8 9 etc

    Prog 1start 10 12 2 5 7 9 13 18 11
    Prog Leaver1 7 3 12 17 14 0 4 9 19

    This would continue down the report so that it account for all starts and leaves by each of any number of training programmes

    The idea of this is to see trends in then starts and leavers and account for these later as retention to programme efficiency.

    Its simple as a spreadsheet because we just type it in from the 2 reports - now I have to knock out this secondary data punching

    any Ideas of what stupid theing I'm not thinking about

    Cheers guys


  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    if you have a calendar table then you can extract the period based on the date
    often a calendar is something like
    Year ' identifies the financial / reporting year which need not be the same as the calendar year (examples include tax year)
    PeriodNo ' identifies the period number usually in ranage 1..12 (or occasionally 1..13)
    PeriodStartDate 'does what it says on the tin (perhaps more useally PeriodEndDate..

    you can then extract the period the training fell in using a bit of SQL.. could be a join to the training table, could be a where clause
    eg where trainingtable.endate <= calendar.enddate

    your next issue is doing the aggregation of the number of people starting and leaving.. thats down to your data design

    you could then create a query which stitches everything together and use a cross tab query to get the results as requested. one thing to bear in mind is that you need to make sure you only get results for the years / periods you want. this approach may also allow the use of comparatives (eg looking at what happened this/last or previous years
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2004
    Thanks healdem

    I can get the data in the period format that was easy. My main issue is forcing a report via query that will show

    starts 1 3 4 5 6 76 7 8 99

    Leavers 2 3 4 1 7 34 2 7 66

    That way I can analyse the % of starts against leavers
    so far I can onlt do this in 2 differing crosstabs then export each to a spreadsheet. Id like to do it all in one move

    does that clear up my earlier blurb?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts