Append records with different field names or change field names (1 Viewer)

[maw230]

Using the attached image, I want to either update all of the field names on the right table to those on the left or append the records from the table on the right to the table on the left. The fields from both tables are in the same order, but have different names.

What can I do? Thanks.

 

[plog]

Using the attached image, I want to either update all of the field names on the right table to those on the left or append the records from the table on the right to the table on the left.

I would make an APPEND query (https://support.office.com/en-us/ar...nd-query-71cd4fe3-c2d7-4856-a0c8-a2638ccf4ad0) based on 1200H868IP.

 

[maw230]

I would make an APPEND query (https://support.office.com/en-us/ar...nd-query-71cd4fe3-c2d7-4856-a0c8-a2638ccf4ad0) based on 1200H868IP.

I tried, but I believe the field names need to be the same?

 

[plog]

Nope. In the 'Append To:' area of the query you get to designate where each field goes. You have to bring down all the fields you want to append--you can't just do the *.

 

[maw230]

Nope. In the 'Append To:' area of the query you get to designate where each field goes. You have to bring down all the fields you want to append--you can't just do the *.

Oh, man. One of those days. Thanks for helping me past this low point in my week :banghead:

 

[maw230]

Is there a way to match these up based on row number? This flat file contains 234 fields, so if I could say 'APPEND where record number 1 from Table A = record number 1 from Table B' it would save me a ton of time.

 

[plog]

Is there a way to match these up based on row number?

Not that I know of. Doing this the hard way should be your pennance for storing the data in an improper manner.

There's no reason for a table with 234 fields. As I look at the table where you want the data to go, I see that it should be several tables. I'm really questioning the purpose of Access in all of this. What does this database do? Why are you using Access?

 

[maw230]

Not that I know of. Doing this the hard way should be your pennance for storing the data in an improper manner.

There's no reason for a table with 234 fields. As I look at the table where you want the data to go, I see that it should be several tables. I'm really questioning the purpose of Access in all of this. What does this database do? Why are you using Access?

Simply as a means of taking a horrible flat, text file and putting it somewhere I'm familiar with. The text file was not csv or delineated in any way, so I had to use an accompanying document to manually specify field beginning positions and lengths. Then I imported it in all of its glory and needed a clever way to add the field names besides manually typing those in as well. So that's where the append came from.

Now, I can go through and delete 90% of the columns that I don't even need. At which point I will have a flat file that will be at least somewhat useful. The time I would need to spend to normalize the data would be essentially a waste for what I'm using it for.

Edit: the perpetrator should be given the punishment. I have no idea how this organization is allowed to send out their data in such a format. And on DISCS nonetheless! I don't have an optical drive!