Query to copy data from query field to table

[Slade2000]

Howdi.

What i have is a is a table with 2 fields. field 1 contains numbers field 2 empty. Then i have a query that extract certain data from field 1 and put it in field 2 and it displays correctly in the query but now i want to take that data in field 2 in the query and copy it to the table. I did a append query wat it just adds the data and not putting it next to the field 1 numbers in field 2.

Tnx allot

 

[Access Hero]

Look at using an "update" query.

 

[Slade2000]

I did try the update query but it gives me an error for the mid function that i use to extract the certain data.

 

[boblarson]

I'm going to say this again. If the data in field 1 is going to stay there. You should NOT, I repeat, should NOT be storing partial data of field 1 in field 2. That flies in the face of normalized data. DO NOT DO IT. Use a QUERY to give you that when you need it (for a form, for a report, for an export). Just do not put it in that field. NO, No, Do not.

Did my message get across this time?

 

[Access Hero]

What were you trying to say again, Bob?

Slade, what Bob says is quite true. If you're not doing a multi-step data conversion from one system to another, there is no reason to do what you're doing. Stated another way, if this is a transactional system (OLTP) there is absolutely no reason for what you're doing.

That being said, if you are doing that and you had a problem using an update statement because it fails on the mid() function, the problem is not the update statement, which does what you said you wanted to do, but with the mid() function, which is potentially being used incorrectly.

What is the business problem you are trying to solve?

 

[boblarson]

What is the business problem you are trying to solve?

See the original thread where this came up.

 

[Slade2000]

Ok why i want to do this is because i have a main table with all my data with 3 fields. field 1 contains the original barrcode and in field 2 and 3 partial numbers from field 1. Field 2 will have then the first 4 numbers of field 1 and field 3 the last 9 numbers. field 1 consists of 13 numbers. So i get a text file from a client and then import it into a new table, table 2. this table only contains 1 field, this has the 13digit number then i copy this field into field 1 of table 1 to update the database. but then i need to take the new barrcodes and split it in table 1 to the 3 fields. What other way are there?

 

[Access Hero]

Ooooo. I saw that thread and decided not to respond. I'm just not smart enough. I'll let you guys handle it.

 

[boblarson]

What other way are there?

Just create a query from table1 with your Mid function and use THAT in place of table1 where you would use table1. You can use queries, in case you were not aware, in basically every place you would use a table. In fact a table (as you view it) is really only a query itself. So there is no reason to put this data into another field. You just use a query to have the new field (by use of the MID function) and then join that query with table2.

 

[boblarson]

See the attached sample which should show you what I mean. Look at the various parts to see how Table1 has a longer value than Table2 but then I use a query to shorten the Table1 field and then use THAT inside the query named QueryThatShowsAllInTable2ThatMatchesTable1.

 

[Slade2000]

ok grate i understand now. It works but now i need to have both the longtext and short text in 1 table for example table 1 must then have tables TestID, LongText,ShortText.

Tnx allot for the sample helped allot.

 

[boblarson]

ok grate i understand now. It works but now i need to have both the longtext and short text in 1 table for example table 1 must then have tables TestID, LongText,ShortText.

Tnx allot for the sample helped allot.

It obviously did not help enough. Again I say to you YOU DO NOT NEED SHORT TEXT IN TABLE ONE; PERIOD. Why do you keep saying that. You use A QUERY when you need short text. My sample showed you how you use it.

Perhaps if I say it enough:

Your table one does NOT need both long and short text. Your table one does NOT need both long and short text.
Your table one does NOT need both long and short text.
Your table one does NOT need both long and short text.
Your table one does NOT need both long and short text.
Your table one does NOT need both long and short text.
Your table one does NOT need both long and short text.
Your table one does NOT need both long and short text.
Your table one does NOT need both long and short text.
Your table one does NOT need both long and short text.
Your table one does NOT need both long and short text.
Your table one does NOT need both long and short text.
Your table one does NOT need both long and short text.
Your table one does NOT need both long and short text.
Your table one does NOT need both long and short text.
Your table one does NOT need both long and short text.
Your table one does NOT need both long and short text.
Your table one does NOT need both long and short text.

 

[Slade2000]

okay so then lets say i want to recall the data in table 1 with the long text that i want and then it returns it in a form in 2 text boxes one longtext and the other short text so split it only when i want to query it in a form or report?

 

[boblarson]

okay so then lets say i want to recall the data in table 1 with the long text that i want and then it returns it in a form in 2 text boxes one longtext and the other short text so split it only when i want to query it in a form or report?

Set up a query like I showed in my example (only for you, you probably have more than two fields in your table(s)). So you have all fields included in that query AND your "manufactured field" using the Mid function. Now, wherever you need table1, just use that base query instead.

 

[Slade2000]

Grate tnx allot

 

[boblarson]

Grate tnx allot

Glad we could help and get you on the path from the "Dark Side" to the "Light"