Changing data in a query 1

[cppiston]

Hello all,

I have a table (Job Order) with a lot of data... Ok, I want to take a row from this table, which I am querying by (Job Number), say 15655.

Now I would like to append the original table (Job Order) with the same data EXCEPT change the (Job Number) from 15655 to say 21010.

Note: The purpose of this, the data entry person just wants to copy and paste a row, but change the (Job Number).

Right now I am trying to use 2 queries. First one queries (Make Table Query) by Job Number to get the data, second query (Append Query) to query the temp table (Job Order_Temp) then append query is to append the data with the new Job Number back into (Job Order) table.

MY PROBLEM: HOW CAN I CHANGE THAT NUMBER 15655 to 21010?? and paste it into the orig. (Job Order) table????


Thanks...

 

[mwolf00]

INSERT INTO jobOrder (jobNum, field1, field2, field3) SELECT 21010, field1, field2, field3 FROM oldTable WHERE jobNum = 15655

 

[cppiston]

I guess what I was trying to say, this is something that will happen daily. The user has a Reference job, and wishes to use that same data for a new job number.. does that make sense???

 

[mwolf00]

I would think that jobNumber is and autoNumber field... So you would only have to say:

INSERT INTO jobOrder (field1, field2, field3) SELECT field1, field2, field3 FROM jobOrder WHERE jobNum = 15655

The insert would get a new job number from the autoNumber field...

 

[cppiston]

Hello mwolf... hey, actually the jobNumber field is not set to auto number, the job number is created in our accounting software when they make a PO..etc... so the number can be changed,

What I need the user to do is be prompted for "Reference Job" and then be prompted for "New Job ID" and basically it will paste the new row into the JobOrder table with all of the same data except the "New Job ID' will be entered by the user..

does this make more sense.. it seems like it could be done with an expression or something in the first query under the jobNumber field : ie [Reference Job] ....??? [New Job ID] ...

 

[mwolf00]

I think that we must be close to what you need:

INSERT INTO jobOrder (jobNumber,field1, field2, field3) SELECT newJobID, field1, field2, field3 FROM jobOrder WHERE jobNum = referenceJob

newJobID and referenceJob would be entered by the user...

 

[cppiston]

That worked!!! That is awesome.. Cool man..

Hey by the way, I see your listed as mwolf... not a michael wolf?? I sever in the army with a michael wolf....

 

[mwolf00]

I'm glad that I could help. I've never been in the army...