Change columns into individual records

[bigomega73]

I have a table with the following info:

Column1 Column2 Column3 Column4 Column5 Column6 Column7
Policy Date1 Amount1 Date2 Amount2 Date3 Amount3

The problem that I have is that I need to have the columns added to
individual records in a new table like this:

Column1 Column2 Column3
Policy Date1 Amount1
Policy Date2 Amount2
Policy Date3 Amount3

I was wondering if there is a way to do this without writing a huge string
of append queries (there are actually about 24 date and amount columns that I
need to seperate). I've seen some suggestions about crosstab queries, but I
can't seem to get them to work in this way. Does anyone have any suggestions?

 

[John W. Vinson]

I have a table with the following info:

Column1 Column2 Column3 Column4 Column5 Column6 Column7
Policy Date1 Amount1 Date2 Amount2 Date3 Amount3

The problem that I have is that I need to have the columns added to
individual records in a new table like this:

Column1 Column2 Column3
Policy Date1 Amount1
Policy Date2 Amount2
Policy Date3 Amount3

I was wondering if there is a way to do this without writing a huge string
of append queries (there are actually about 24 date and amount columns that I
need to seperate). I've seen some suggestions about crosstab queries, but I
can't seem to get them to work in this way. Does anyone have any suggestions?

This is the reverse of a crosstab (which takes tall-thin data and changes it
into spreadsheet form). What you need is what's called a "Normalizing Union
Query" with an append query based on it. Assuming that your fieldnames
actually are (uck!!) Column1 and so on, you could go into SQL view of a new
query (you can't do this in the grid) and edit it to:

SELECT Column1 AS Policy, Column2 AS Datefield, Column3 AS Amount
FROM yourtable
WHERE Column1 IS NOT NULL
UNION ALL
SELECT Column1, Column4, Column5
FROM yourtable
WHERE Column4 IS NOT NULL
SELECT Column1, Column6, Column7
FROM yourtable
WHERE Column6 IS NOT NULL

etc. if there are more columns.

Check the output of this query to see if it looks like what you want; if so,
save it and base a single Append query on it.