Post reply

Show all records from one table for EACH record in another table?

  • April 16, 2009 at 5:50 am

    #384375

    Hi,

    I have a funding table which has records listed per year for a business case. BUsiness case is the parent table & the funding table is the child table with a one-to-many relationship.

    Each business case can therefore have one funding record listed which shows 1 year or it can have many funding records for a number of years. The years will fall into 2007-2012 as this is the length of business case timeline.

    EG, Business case 1 has funding record £5000 for 2007.

    Business case 2 has funding records £3000 for 2009, £5000 for 2010, £7000 for 2011.

    For a main report that i have, i have to list the year data grouped by the year so i have totals for each year BUT i'm struggling to figure out how to do this because i need to list all possible years in a matrix. Further more, this needs to be grouped by the project lead field so it lists all possible years of funding per person (each project lead can be a lead for 1 or more cases).

    EG,

    Joe Bloggs (project lead) (lead of 3 business cases grouped in the following totals)

    year - total (£)

    2007 - £10000

    2008 - NULL

    2009 - £20000

    2010 - £15000

    2011 - NULL

    2012 - £4000

    Jim Bob (project lead) (lead of 1 business case grouped in the following totals)

    year - total (£)

    2007 - NULL

    2008 - NULL

    2009 - £200

    2010 - NULL

    2011 - NULL

    2012 - £3500

    I created a static table to hold the years as records in the table. I then tried to link this table with my funding table as a right outer join to show all records from the year table but this shows the related years and then the additional years as single entiries. I need to seperate the records so ALL years are listed for each project lead & the amounts are summed by year.

    I know this is a bit confusing so i hope i have explained this well. Can anyone help with this one?

    Regards,

    Shuja

  • April 16, 2009 at 10:10 am

    #979000

    I've managed to find a possible solution using cross joins. See the following thread for more detail:

    http://www.dbforums.com/microsoft-sql-server/1641161-show-all-records-one-table-each-record-another-table.html

    thanks for your help.

    Shuja

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply