Choose append field based on criteria

[jrizzo77]

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

 

[John W. Vinson]

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/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[jrizzo77]

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

 

[jrizzo77]

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.

 

[John W. Vinson]

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/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com