which sql for this report?

[Francesco]

Hi everybody.

I have a master table
Table: PROJECTS
ID_PROJECT
PROJECT

And two detail tables
Table: PLEDGED_FUNDS
ID_ PLEDGED _FUND
ID_PROJECT
YEAR
PLEDGED _FUND

Table: GIVEN_FUNDS
ID_ GIVEN _FUND
ID_PROJECT
YEAR
GIVEN _FUND

I need a report such as

Project1 | Total Pledge (all the years) | Total given year_2004 | Total
given year_2005 …
Project2 | Total Pledge (all the years) | Total given year_2004 | Total
given year_2005 …
Etc.

Which kind of SQL statement could I use?
Thank you for any suggestion

 

[Anthony Headley]

Hi everybody.

I have a master table
Table: PROJECTS
ID_PROJECT
PROJECT

And two detail tables
Table: PLEDGED_FUNDS
ID_ PLEDGED _FUND
ID_PROJECT
YEAR
PLEDGED _FUND

Table: GIVEN_FUNDS
ID_ GIVEN _FUND
ID_PROJECT
YEAR
GIVEN _FUND

I need a report such as

Project1 | Total Pledge (all the years) | Total given year_2004 | Total
given year_2005 …
Project2 | Total Pledge (all the years) | Total given year_2004 | Total
given year_2005 …
Etc.

Which kind of SQL statement could I use?
Thank you for any suggestion

Play with PivotTables, they are really easy to setup and they would do
everything you need on the cilent side.

 

[Sylvain Lafontaine]

This is a pivotal transformation, a big subject by itself. You can search
the internet with terms like "pivot table" and "pivot transformation". If
you are using Access with an MDB or an ACCDB database file, then there is a
special query called Pivot Transform that can solve this. There are also
the PivotTable as mentionned in the other post.

However, if you are using an ADP file (instead of a MDB/ACCDB file) against
a SQL-Server (as mentionned in the title of this newsgroup), then you must
something else than the pivot transformation of Access because this
particular query is not available with ADP and SQL-Server.