Price changes in several Rows to one row.

[Rahul79]

Hi All,

In short, I am working on this complex Access DB for which the data for one of the Tables is very important.

The Data:

It consists of Rate changes to a specific type of service that is different per region and state.

Implying, the headers are: Region, State, Company, Type Code, Service Code, Rate1 (Flat), Rate 2(By units used), Effective Date, Termination Date.

If Rate1 exist, implies Rate 2 = 0 and vice versa.

What I am trying to do is to bring all rates in one row. There is a maximum of 2 rate changes that I deal with.

So I want a new sheet with the following headers:
Region, State, Company, Type Code, Service Code, Old Rate1, Old Rate2, Effective Date, Term Date, Old1 Rate 1, Old1 Rate 2, Effective Date Term Date, Current Rate 1, Current Rate 2, Effective Date Term Dat.


Is there a easy way to do it? I have already messed around with Lookup's and If statements but all yield to errors, and certain of it could be my fault as I am also creating several iif commands in Access as well.

Any help would be highly appreciated. Even a direction I need to go to, and I can manage the rest.

Thanks.