Query with 5 tables, grouping by year

How would I group results from 4 tables, each with a year field, so the
results are one row for each year, all based on one clientid from the client
table
For instance, my tables:
Client table: fields ID, ClientName
Tables 1 through 4 all have the same fields, in addition to others: Fields
ID, ClientID (fk to Client table), ValidYear, Data ...
I want my results to be one row per year (we'll be querying only one
client), for example
Year Table1Data Table2Data Table3Data Table4Data
2001 1,000 3,300 15,000 445
2002 1,212 etc.
I've started with:
Select Table1.Data, Table1.ValidYear,
Table2.Data, Table2.ValidYear,
Table3.Data, Table3.ValidYear,
Table4.Data, Table4.ValidYear,
tblClient.ID, tblClient.ClientName
from Table1
inner join tblClient as a on a.ID = Table1.ClientID
inner join tblClient as b on b.ID = Table1.ClientID
inner join tblClient as c on c.ID = Table1.ClientID
inner join tblClient as d on d.ID = Table1.ClientID
and that's as far as I got, as soon as I enter criteria for ValidYear, I get
either too many rows of data or none at all. Not all data tables have data
for all years, by the way.
Thanks very much for your help.Hi,
would you like to check out he usage of DATEPART?
In your SELECT statement, you actually can put DatePart(year,
Table2.ValidYear) instead of Table2.ValidYear.
In the end of your SELECT statement, you just need to put GROUP BY
Table1.Data, Table2.Data etc to get a distinct value.
I hope this is what you are looking for.
Leo Leong
"et" wrote:

> How would I group results from 4 tables, each with a year field, so the
> results are one row for each year, all based on one clientid from the clie
nt
> table
> For instance, my tables:
> Client table: fields ID, ClientName
> Tables 1 through 4 all have the same fields, in addition to others: Field
s
> ID, ClientID (fk to Client table), ValidYear, Data ...
> I want my results to be one row per year (we'll be querying only one
> client), for example
> Year Table1Data Table2Data Table3Data Table4Data
> 2001 1,000 3,300 15,000 445
> 2002 1,212 etc.
> I've started with:
> Select Table1.Data, Table1.ValidYear,
> Table2.Data, Table2.ValidYear,
> Table3.Data, Table3.ValidYear,
> Table4.Data, Table4.ValidYear,
> tblClient.ID, tblClient.ClientName
> from Table1
> inner join tblClient as a on a.ID = Table1.ClientID
> inner join tblClient as b on b.ID = Table1.ClientID
> inner join tblClient as c on c.ID = Table1.ClientID
> inner join tblClient as d on d.ID = Table1.ClientID
> and that's as far as I got, as soon as I enter criteria for ValidYear, I g
et
> either too many rows of data or none at all. Not all data tables have dat
a
> for all years, by the way.
> Thanks very much for your help.
>
>