Can Access create new headings for me for all like data?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ManhattanRebel - 18 May 2008 00:58 GMT

How can I direct Access to create a new heading for all like data that is
currently in the same field?

For example, my data looks like this now:

A B C D E - This is the headings row.
a b c x z
a b c x y
a b c z x
a b c y z
a b c d e

I need it to look like this without having to sort, cut and paste. There is
too much data to sort, cut and paste. Note: in the sample below the x,y, and
z now have their own fields

A B C D E X Y Z -----This is
new heading row
a b c x z
a b c x y
a b c x z
a b c y z
a b c d e

Reply to this Message

Douglas J. Steele - 18 May 2008 01:27 GMT

Yes, Access can do that. Since your data is obviously fictitious, it's
pretty hard to give instructions on how to do it though.

You need to create a query and put calculated fields in it. To get
essentially what you've asked for, your query would look something like:

SELECT A, B, C, IIf([D] <> "d", Null, "d") AS NewD,
IIf([E] <> "e", Null, "e") AS NewE,
IIf([D] = "x" OR [E] = "x", "x", Null) AS X,
IIf([D] = "y" OR [E] = "y", "y", Null) AS Y,
IIf([D] = "z" OR [E] = "z", "z", Null) AS Z
FROM MyTable

The "essentially" part is due to the fact that you need to rename columns D
and E: leaving their names as the same as the columns would lead to a
circular reference, so isn't possible.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> How can I direct Access to create a new heading for all like data that is
> currently in the same field?
[quoted text clipped - 21 lines]
> a b c y z
> a b c d e

Reply to this Message

luu - 20 May 2008 08:53 GMT

> How can I direct Access to create a new heading for all like data that is
> currently in the same field?
[quoted text clipped - 21 lines]
> a b c y z
> a b c d e

Reply to this Message