duplicate alias ??

[dickk]

i have what might be a simple problem if I knew access/sql better. I have 12 fields (field1 thru field12)which may or may not be populated. I know how to do this with other languages with if/else structures but it looks like access/sql doesn't have an ELSE structure. Any help greatly appreciated.

I have to create a new table, with a new field populated with the contents of the last populated field...

in other words... if the 12th field is not null, populate fieldnew with the contents of field12
else
if field12 is null, then if field11 is not null,populate fieldnew with the contents of field11
else
if field11 is null, then if field10 is not null,populate fieldnew with the contents of fieldl10
else
if field10 is null, then if field9 is not null,populate fieldnew with the contents of fieldl9
else
if field9 is null, then if field8 is not null,populate fieldnew with the contents of fieldl8


and so on........

[recyan]

Are these 12 fields, fields of a table ?
Have you checked out the IIf() function ?
Also could not understand the title of your post ?

Thanks

[dickk]

Hi Recyan,

thanks for the help. they are fields in the table. I did use the IIF statement, thinking I could just say, "if not null, move this value" (example below) and, in this way, I would stop after using the last populatede field. However, Access won't let me use the same receiving field name... it errors on a duplicate alias.

SELECT
(IIf([Last date served-2] Is Not Null, [Last date served-2],Null)) AS [Newdate], ELSE
(IIf([Last date served-3] Is Not Null,[Last date served-3],Null)) AS [Newdate]
FROM [Daily Client Query];

[recyan]

Was unable to understand the SQL you have posted.
Just check if below crude method gives some guidelines :

SELECT 
	myTable.TestID, 
	myTable.field1, 
	myTable.field2, 
	myTable.field3, 
	myTable.field4, 
	myTable.field5, 
	myTable.field6, 
	myTable.field7, 
	myTable.field8, 
	myTable.field9, 
	myTable.field10, 
	myTable.field11, 
	myTable.field12, 
	IIf(IsNull([field12]),"",[field12] & ",") & IIf(IsNull([field11]),"",[field11] & ",") & IIf(IsNull([field10]),"",[field10] & ",") & IIf(IsNull([field9]),"",[field9] & ",") & IIf(IsNull([field8]),"",[field8] & ",") & IIf(IsNull([field7]),"",[field7] & ",") & IIf(IsNull([field6]),"",[field6] & ",") & IIf(IsNull([field5]),"",[field5] & ",") & IIf(IsNull([field4]),"",[field4] & ",") & IIf(IsNull([field3]),"",[field3] & ",") & IIf(IsNull([field2]),"",[field2] & ",") & IIf(IsNull([field1]),"",[field1] & ",") AS AllTheNotNullFieldsSeperatedByComma, 
	InStr(1,[AllTheNotNullFieldsSeperatedByComma],",") AS TheFirstCommaPosition,
	Left([AllTheNotNullFieldsSeperatedByComma],[TheFirstCommaPosition]-1) AS TheRequiredValue
FROM 
	myTable;

Thanks

[dickk]

Hi Recyan,

Wow!! That seems to have done it! Plus, it taught me a little more about ways to use SQL. Thanks a bunch!! This is a wonderful forum and tool.

[recyan]

Glad you found it helpful.

Thanks