Access2010 update query - need to insert new column (SalesPerID) matching SalesPer

[dhutton27]

Hello everyone,



Having a problem trying to build an update query to insert another column to an existing table. I forgot to add in SalesPerID column(in table OrderDetails) to match SalesPer column. The problem is there are 5,000 lines of orders and I don't want to enter this manually. I have SQL server 2012 and tried to add the column in that way - but would like to know how to do this in Access(or SQL). So I have a table with SalesPerID and would like to query that table to update or append to OrderDetails table. Here is what the table looks like:

Trying to Build relationships amongst tables so I can use JOINS in my query's - so instead of trashing everything and starting over because I forgot to add Key fields I want to insert the data I need.

Table:SalesPerson

SalesPerID already exists on this table

SalesPerID	FirstName	LastName	Branch	SalesPer
1	Bob	Smith	Houston	BS
2	Sherry	Wiley	Boston	SW

Tables: OrderDetails. SalesPerID is the Field or Column that I want to insert - it doesn't exist. Would like to do an update or append query using a JOIN on table SalesPerson. Also see attachment on Database's Relationship diagram - OrderDetails is actually called MO2012to2014 on the attachment....

OrderID	Date	Company	JobDesc	SONUM	SalesPer	SalesPerID
1	6/30/14	ACME	Drilling	12345	BS	1
2	7/1/14	Pipe Supply	Welding	12356	SW	2

[pbaldy]

To the best of my knowledge you can't do it in one step. You need to add the field first, then populate it with an update query.

[dhutton27]

Ok, changed the OrderDetails to have the SalesPerID header. Now to do this in design view what arguments are needed to say IF SalesPerson.SalesPer = OrderDetails.SalesPer Then update OrderDetails.SalesID ON Salesperson.SalesPerID.

(Actually making this a little confusing because OrderDetails table is still called MO2012to1214 and haven't changed it yet) Anyways I can't figure out the correct JOIN syntax in the design view to either use an update, append or make table procedure.

[pbaldy]

Off the top of my head:

UPDATE OrderDetails INNER JOIN SalesPerson ON OrderDetails.SalesPer = SalesPerson.SalesPer
SET OrderDetails.SalesPerID = SalesPerson.SalesPerID

[dhutton27]

[Tried an make table query and no luck..

Having a problem trying to build an update query to insert another column to an existing table. I forgot to add in SalesPerID column(in table OrderDetails) to match SalesPer column. The problem is there are 5,000 lines of orders and I don't want to enter this manually. I have SQL server 2012 and tried to add the column in that way - but would like to know how to do this in Access(or SQL). So I have a table with SalesPerID and would like to query that table to update or append to OrderDetails table. Here is what the table looks like:

Trying to Build relationships amongst tables so I can use JOINS in my query's - so instead of trashing everything and starting over because I forgot to add Key fields I want to insert the data I need.

Table:SalesPerson

SalesPerID already exists on this table

SalesPerID	FirstName	LastName	Branch	SalesPer
1	Bob	Smith	Houston	BS
2	Sherry	Wiley	Boston	SW

Tables: OrderDetails. SalesPerID is the Field or Column that I want to insert - it doesn't exist. Would like to do an update or append query using a JOIN on table SalesPerson. Also see attachment on Database's Relationship diagram - OrderDetails is actually called MO2012to2014 on the attachment....

OrderID	Date	Company	JobDesc	SONUM	SalesPer	SalesPerID
1	6/30/14	ACME	Drilling	12345	BS	1
2	7/1/14	Pipe Supply	Welding	12356	SW	2

[/QUOTE]

[pbaldy]

Is there a new question or are you just restating the original question? If so I'll restate my original answer.

[dhutton27]

I have a new question on the same problem...



Here are the two revised tables in question. Where do I insert your solution? in the field list or can I run it from SQL view and if so do I need a SELECT AND FROM statement before your code.

[pbaldy]

What I posted was the untested full SQL of an update query. It could be run from SQL view.

[dhutton27]

WOW!! It worked!! Thank You So Much!!!!

Field List Solution:




SQL View Solution:

UPDATE OrderDetailsTest1 INNER JOIN SalesPersonTest1
ON OrderDetailsTest1.SalesPer = SalesPersonTest1.SalesPer
SET OrderDetailsTest1.SalesPerID = SalesPersonTest1.SalesPerID