Transferring data between tables with a query

[creativeimages]

My thinking does not match that of the designers Access. I am trying the use a query to modify some data and append the modified data to an existing table. The source table has forty some field. The destination table has seven fields.

I keep getting the “INSERT INTO statement contains the following field name: ‘Owner`”. Make sure you have typed the name correctly, and try the operation again.”

Do the two tables have to have the exact same fields to move data? What am I over looking?

 

[TheAceMan1]

[blue]My thinking does not match that of the designers Access.[/blue]

Forgive me . . . . but [blue]this is academic[/blue], since you have something you want to accomplish no matter what!

Without knowing table structure, relationships an such, consider two queries . . . [blue]one to modify[/blue] . . . [blue]one to append![/blue]

A query of this type can be done, but the complexity will outweigh breaking it up into two!

Can you postthe query your currently using?

See Ya! . . . . . .

 

[creativeimages]

You might be right - it might be academic, but the specifics are very cumbersome. I do not understand how the specific will explain why 50 fields per record in table A causes a problem that prevents the data from being transferred to table B.

Alpha: IIf(Asc(Right(Trim(Mid([raw1],13,7)),1))<=64,"",Right(Trim(Mid([raw1],13,7)),1))
This expression removes the alpha part of the street number from a line of 132 characters (this single field in a single record in the soft copy of the report from the county; this field is known as Raw1). This is done for four lines (Raw1, Raw2, Raw3, Raw4) of 132 characters in a “record” from a county government, and leads to about 45 fields per a “record” from the county.

Right now there are no relationships.

The bigger problem for me is that there several types of queries, and I am using a quiery to append some data to a table that is named Streets; and I am getting an error message that implies the the fields in one table must be the same as the table appending to. This seems wrong to me. I feel that I am missing an important bit of information. I was hoping to have some light shed on the issue. An explaination of what I might be doing wrong. If I am nottt doing anything wrong, what is a work around?

 

[TheAceMan1]

creativeimages . . . .

Sorry to get back so late.

[blue]Do the two tables have to have the exact same fields to move data? What am I over looking?[/blue]

I should've answered this in the beginning. The answer is [blue]No! But the fields involved in the update/append do have to have the [purple]same DataType![/purple][/blue]

Another issue, [blue]the fields returned from the source query have to exist in the destination table[/blue] or you'll get the kind of error you received. A common error here is using fields for criteria in the source query that [blue]do not exist in the destination table[/blue] and not hiding them (there included in the returned recordset).

[blue][Alpha: IIf(Asc(Right(Trim(Mid([raw1],13,7)),1))<=64,"",Right(Trim(Mid([raw1],13,7)),1))
This expression [purple]removes the alpha part of the street number[/purple] from a line of 132 characters[/blue]

I'm confused here as your IIf statement returns an empty string or a single character . . .

Anyway . . . check the query conditions above and let me know . . .

See Ya! . . . . . .