Appending records from an imported table

[bino]

Hi all



I have a table that has imported data. Here is what I want to do:

I have

Field a 'Home'
Field b ' '
Field c ' '

I only need the field that has a content to be appended to a unique field G

the programming would be

If field a is not blank, G = field a, else if field b is not blank, G = field b, else if field c is not blank, G = field c. In this case field G would be 'Home'

Trying to figure out how to do that using query option.

thanks

bino

[CJ_London]

G=nz(a,nz(b,c)

assumes by ‘blank’ you mean null and not a zls or space

[bino]

G=nz(a,nz(b,c)

assumes by ‘blank’ you mean null and not a zls or space

Thanks CJ

I fail to see where that would be inserted in the query screen.

Thanks

Bino

[CJ_London]

you haven't provided table names or proper field names but in the query grid you would put the calculation in the field row and 'G' in the append to row

And you haven't clarified whether fields a, b and c could be null, zls or a space since 'blank' is an excel term completely irrelevant to a database such as access. Unlike excel, the data type and value matter as to how the calculation is constructed

[jojowhite]

you write an Update query and update your table after appending the data:

Update YourTableName Set [G] = Switch(Len([A] & "") > 0, [A], Len([B] & "") > 0, [B], Len([C] & "") > 0, [C], True, "Home")

[bino]

you haven't provided table names or proper field names but in the query grid you would put the calculation in the field row and 'G' in the append to row

And you haven't clarified whether fields a, b and c could be null, zls or a space since 'blank' is an excel term completely irrelevant to a database such as access. Unlike excel, the data type and value matter as to how the calculation is constructed

Hi CJ

Trying to add print screens of the query, but forum not accepting it. How can I send you the print screens?

[CJ_London]

How can I send you the print screens?

To do so, click on the Go Advanced tab, scroll down and click on the Manage Attachments button>Add files>choose, then upload

[bino]

Here it is

[CJ_London]

So what don’t you understand? From the code provided replace a,b, c and G with your field names

Be aware that having spaces and none alpha numeric characters in field names can cause issues

[Welshgasman]

Show the SQL please.