Access Question

[Roy Hinkley]

I also posted this under General Actuarial, but I'm afraid not everyone reads those threads!

I have an Access table in which I’d like to do a little transposing of data. For example, assume I have the following fields: Region, Branch, 1999IBNR, 2000IBNR, 2001IBNR; I’d like to map that to a table with the fields Region, Branch, Year, IBNR. Is there a way to do this? Any help would be appreciated.

[Toll Free]

Here's how I'd do it:

Step 1: Run a "make table" query mapping "Region" to new field "Region", "Branch" to new field "Branch", "1999" to new field "Year", and "1999IBNR" to new field "IBNR"
Step 2: Run an append query on the table from Step 1, mapping "2000" to "Year" and "2000IBNR" to "IBNR"
Step 3: Same as Step 2, for 2001

It's not pretty, but it will work. Hopefully, you weren't simplifying for 15 years and 8 fields.

[Wag, the Dog]

On 2002-01-29 11:24, Roy Hinkley wrote:
I also posted this under General Actuarial, but I'm afraid not everyone reads those threads!

I have an Access table in which I’d like to do a little transposing of data. For example, assume I have the following fields: Region, Branch, 1999IBNR, 2000IBNR, 2001IBNR; I’d like to map that to a table with the fields Region, Branch, Year, IBNR. Is there a way to do this? Any help would be appreciated.

Do you see the forum section entitled:

Software - Technology

?

A moderator set it up just for you. It would be rude for you not to use it, wouldn't it?

[switched]

tf:

at least our answers matched! (I posted in the other thread)

[Roy Hinkley]

Toll Free, Switched, thanks for taking the time to answer my question. An alternative I came up with was to create a dummy table with all the years and map them to the regions and branches, then use a nested iif statement to attach the IBNR [e.g. iif(year=1997,1997ibnr,etc.)]. It worked pretty cleanly.

[Actuary321]

Another option would be to export to an Excel spreadsheet do the transposition and then import it back to access.