I am trying to append some data to a new table. Table A looks like this>
ID|Year|Jan|Feb|Mar..and so on
Sample data
1|2011|10|20|30

I would like the new table to look like this>
ID|2011/Jan|2011/Feb|2011/Mar

I am trying to use this iif statement in an append query
Append To: IIF([Year]=2011,[2011/Jan],IIF([Year]=2012,[2012/Jan]))

I have a simular iif statement in to choose the Field and that is working fine.

Any ideas or is this not even possible.

Joe

On Wed, 9 Nov 2011 09:32:51 -0800 (PST), (E-Mail Removed) wrote:

>I am trying to append some data to a new table. Table A looks like this>
>ID|Year|Jan|Feb|Mar..and so on
>Sample data
>1|2011|10|20|30
>
>I would like the new table to look like this>
>ID|2011/Jan|2011/Feb|2011/Mar
>
>I am trying to use this iif statement in an append query
>Append To: IIF([Year]=2011,[2011/Jan],IIF([Year]=2012,[2012/Jan]))
>
>I have a simular iif statement in to choose the Field and that is working fine.
>
>Any ideas or is this not even possible.
>
>Joe

Neither table design is correctly normalized. You should absolutely NOT be
storing data in fieldnames, particularly data like this - you'll need to
expand your table's width by twelve new fields every year as time goes on, and
redesign all your forms, reports and queries to match. Shudder!!!

Consider instead a properly normalized table with fields SampleID, SampleDate,
and SampleValule, with values like

1; #1/1/2011#; 10
1; #2/1/2011#; 20
1; #3/1/2011#; 30
....
41; #6/1/2014#; 15
....

This "tall-thin" table can be presented in a grid with ID's on the side and
months (or dates) across the top using a crosstab query.

You can populate the tallthin table with a Normalizing Union query like:

INSERT INTO TallThinTable (ID, SampleDate, SampleValue)
(SELECT ID, DateSerial([year], 1, 1), [Jan] FROM WideFlat
UNION ALL
SELECT ID, DateSerial([year], 2, 1), [Feb] FROM WideFlat
UNION ALL
SELECT ID, DateSerial([year], 3, 1), [Mar] FROM WideFlat
UNION ALL
SELECT ID, DateSerial([year],4, 1), [Apr] FROM WideFlat
UNION ALL
<etc>
SELECT ID, DateSerial([year], 12, 1), [Dec] FROM WideFlat);

Your proposed alternative wide-flat can be created if you insist, but the
fieldnames will change from year to year and there is in principle no limit
(other than the heat-death of the universe) to the number of fields you would
need to add, so it will be an UGGGLLLY query.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/For...-US/accessdev/
http://social.answers.microsoft.com/.../en-US/addbuz/
and see also http://www.utteraccess.com

thanks for your advise. can you give me an example of a crosstab using sql?

another issue is I need the user to input this information. cannot update a crosstab. that is why I started with the ID|Year|Jan|Feb|Mar... table. now the users want the input area to list Year/Month across. This is theirrequirment. I know it is a nightmare but I can not think of any other salution.

On Wed, 9 Nov 2011 14:49:34 -0800 (PST), (E-Mail Removed) wrote:

>another issue is I need the user to input this information. cannot update a crosstab. that is why I started with the ID|Year|Jan|Feb|Mar... table. now the users want the input area to list Year/Month across. This is their requirment. I know it is a nightmare but I can not think of any other salution.

You can use a temporary table or an unbound form to enter the data, with some
VBA code to populate the normalized table. You should always fit the data
entry forms to a normalized table design, rather than letting data entry
layout drive table design - the result will generally NOT be a good table!
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/For...-US/accessdev/
http://social.answers.microsoft.com/.../en-US/addbuz/
and see also http://www.utteraccess.com