I need a little help with this I have two database I need to take data from 4 columns in one database and then insert that into 1 column in another database?

Does anyone have any idea about how I could do this?

Are the 'other 4 columns' in the same table?

it would be something like this lexicon, with the target database in use...
Note that the concat() above has no separators, which will jam the fields together, so you would need to add spaces, or comma's into the concat where needed.

If the four fields are from multiple tables, then the FROM clause would need:
<otherDB>.<table1>, <otherDB>.<table2>, etc. to resolve the various fields.

Let us know how it goes!

Thank you but I need to ask something else, This is an ASP script with MS Access as its database I was told that SQL would be the way to do this (Why I posted here. ) But I need find out if may this is the best way.

What I have is I have a database Netview data look like this in the table:

col_store_number col_device col_ticket col_assigned_to col_resolved
002 ISP 123456 Help Desk No
003 LMS 123456 Telcom Yes
004 Router 123456 Help Desk No
005 Store 123456 Telcom No
006 CC 123456 Help Desk Yes

Now for all of the stores that the col_resolved = No I need to store the data something like this

Store: 002
Device: ISP
Ticket: 123456
Assigned To: Help Desk

I need it to format this data like this of each row that col_resolved = No
Then I need to insert it into a column in and already existing record a another database

The Turnover database looks like this:

col_id col_main col_dist col_netw col_misc
12 Mainframe problems Dist Problems Network Problems Misc Problems

There will only been one record in this database at a time I need to take the 4 columns in the netview database adn then put them in the Turnover database in the col_netw

Yes all of the 4 columns are in the same table.

I hope this makes since.

Thanks Again

Are you able to link the required table in the Netview database to the Turnover database?

If so, I came up with this from your sample data:
(this may not be the most elegant solution, but it works (I have Access 2k). I tried using a similar query like post #2 (assuming MySQL, based on this forum), but Access kept complaining about needing 'EXISTS' in the FROM clause. I tried a few different ways, but I could not get around the error(s), even after following some of their examples )
You would have to modify it, though, to be able to pass in a variable store number (via a loop or some other function call). Plus, there are no newlines added between the entries.

Oops, I will need to rework that a little, I missed the part about 'col_resolved = No'. I was driving off the store number. The change might actually be more significant, and possibly more elegant.

Ok, after hearing the "loud-pop"...

That turned out to be a simple append query, but still requires being able to link Netview's specific table (that has the 4 fields) into Turnover database for this example.

The query would then look like this:
(in Access, open a blank query, switch to SQL view, paste the following SQL)


After pasting the SQL, switch the view back to design mode to see the graphical layout. Let us know how it goes!

-- Oh, before I forget, the information in the example is separated by spaces when it is inserted into the 'col_netw' field.

That grab what I needed and got it formatted the way I need. Now I have one more question would it be possiable to have it up date a record that was already there?

Thank you again for all the help.

Yes, but how will be updated? [based on what criteria]

You mentioned earlier (in post #3) that there is only one record at a time? (I assume this means for each store) -- so does this mean that the update is a delete?

Ok sorry I meant rather than Inserting into the turnover col (col_netw) could I set this to update a record that is already there. in post 3 I meant the turnover database has only one record in it at one time the netview database can have multi records in it at one time.

so like when I run the query that you gave it works but if the netview database have 3 record in it it will insert 3 new records in the turnover database could this be fixed to just update the record that is already in the turnover database?

I understand about updating a turnover record already there, (after a prior insert), but what will be the update criteria? Here's more info...

The previous query will add (three records based on the previous sample), and it will always add (append/[insert]) as it stands...

Now, for the update part, in the turnover table, say you have previously already 'inserted' the three stores. How (or what) will be updated in the turnover table? The same field from the insert query? Delete a store whose 'resolved' has changed to 'Yes'? That's all I'm trying to understand. Then we can work the update part.