which sql for this report?

  • Thread starter Thread starter Francesco
  • Start date Start date
F

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
 
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
Click to expand...

Play with PivotTables, they are really easy to setup and they would do
everything you need on the cilent side.
 
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.
 
Back
Top