Take two separate fields in a table and append them into one field in a separate table?

[gojets1721]

I'm wondering if an append query can take two different fields (same data type), combine them and then append them into one single field in a different table

Thanks

 

[plog]

Yes, but I fear you're missing the true meaning of APPEND.

APPEND doesn't simply add data to a field, it adds a whole new record. You have 5 records in TableA and run an APPEND on it and afterward you would have more than 5 records in TableA.

Last, one of the fundamentals of normalized database is that you store discrete pieces of data discretely. That is, you don't jam together individual pieces of data into one field--you give each its own. Another funamental is that you don't move data around. If its in one place, you don't move it to another, you simply reference that place it is in.

So, why do you need to break the fundamental way your database should operate? What will this APPEND do for you?

 

[gojets1721]

I see what you're saying. The issue is the way that the data comes into the DB. Without getting into the details, the raw data from an outside source has two fields that really should always be one and I can't change the way the data is originally captured (its captured via an online form). If there's data in one of the fields, the other one will always be blank.

So essentially this raw data is dumped into a temp table and then we append it into the main table, and through that process, combining the two fields would be needed. Once appended, the temp table is cleared. We import the data daily.

 

[plog]

Just checking--your situation is the right case for an UPDATE or APPEND query. Moving from a raw file to a properly structred table system is the correct use of them.

 

[Pat Hartman]

What are the two fields? Are you certain they should be mushed. Putting First and Last names into a single field leads to nothing but trouble down the road. It is quite likely that if the fields come to you as two, then you should keep them as two separate fields. Usually, we have the opposite problem. The data comes to us mushed and we need to separate it.

 

[gojets1721]

Just checking--your situation is the right case for an UPDATE or APPEND query. Moving from a raw file to a properly structred table system is the correct use of them.

So how do I go about using an append query to do that? Any suggestions?

 

[gojets1721]

What are the two fields? Are you certain they should be mushed. Putting First and Last names into a single field leads to nothing but trouble down the road. It is quite likely that if the fields come to you as two, then you should keep them as two separate fields. Usually, we have the opposite problem. The data comes to us mushed and we need to separate it.

Yes I'm certain. I see what you're saying about the name fields, but it isn't that. The way the data is captured is silly (not my department) and it should just be one field.

 

[Pat Hartman]

Ok don't tell us.

How are you loading the data now? I'm assuming it is with an append query. Just modify that append query to concatenate the two fields.

Open the query in Design view. Delete the "second" column and change the first one to

fld1 & fld2

And then change the append to field to whatever fieldname you want the concatenated field to be appended to.

If you want to fix data that is already there, you need to use an UPDATE query.

As someone mentioned earlier. Append queries append NEW ROWS. Update queries update EXISTING ROWS.