Not quite a crosstab

[Johnny Bright]

Hi there,

I had a table with about 50 fields and in normalizing my data I have
stripped out some of the fields that weren't immediately relevant to the
building. Part of it looked like this:

Start Date BuildingID Rent Yr1 Rent Yr2 Rent Yr3 Rent Yr4 Rent Yr 5
1/1/05 1 100 150 200 250
300
I have now converted this to a rental table:
Rent IDBuilding ID Rent StartDate
1 1 100 1/1/05
2 1 150 1/1/05
3 1 200 1/1/05
4 1 250 1/1/05
5 1 300 1/1/05

What I want is:
Building ID Rent05 Rent 06 Rent 07 Rent 08 Rent 09
1 100 150 200 250 300

I have tried doing this in a crosstab format but can't get the result I'm
looking for, plust the fact that I'm not very good at them.
Can anyone suggest a way of achieving my goal. Possibly I need to struture
my rental table differently.

Thanks,

J

 

[Duane Hookom]

Your new rental table should have something that determines the year. Your
start dates are all the same.