Ok...One More Try...Update Query

[Skins64]

I have two access tables both contain a field that contains a Work Order #....tblconsulting contains work order numbers that may or may not be in tblsalescredit ....I need to update a field called SalesCredit in tblconsulting with the data in a field called credit in tblsalescredit according to the matching Work Order # .....please help.

THANKS!

 

[PHV]

UPDATE tblconsulting INNER JOIN tblsalescredit
ON tblconsulting.[Work Order #] = tblsalescredit.[Work Order #]
SET tblconsulting.SalesCredit = tblsalescredit.credit

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Skins64]

Ok....I am a newbie, I enter the above into SQL Data Definition just as written?

 

[PHV]

This is SQL code to enter in the SQL view pane of the query window.
You have to put the real field and table names.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Skins64]

Ok I entered this, just as shown:

UPDATE tblconsulting INNER JOIN tblsalescredit ON tblconsulting.[WorkOrder] = tblsalescredit.[WO#] SET tblconsulting.SlsCrRev = tblsalescredit.credit

I get the Enter Parameter form for "tblsalescredit.credit"

if I just click ok....and continue it says I am about to modify 453 rows.....(only 303 rows in table)....I run it and nothing happens in my tables.

Thanks!

 

[PHV]

Does the table tblsalescredit have a field named credit ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Skins64]

Yes

 

[Skins64]

Anybody?????...help....tryin to update a blank field in one table with the data in another...tables have matching Work Orders, Company IDs.

 

[PHV]

tables have matching Work Orders, Company IDs
Ah, something new that explain the 453 rows ...
UPDATE tblconsulting INNER JOIN tblsalescredit
ON tblconsulting.WorkOrder = tblsalescredit.[WO#] AND tblconsulting.CompanyID = tblsalescredit.CompanyID
SET tblconsulting.SlsCrRev = [tblsalescredit].[credit]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Skins64]

This updates 0 rows and I still get the parameter pop up form.

 

[Skins64]

Anybody???????

 

[lespaul]

you should only get the parameter pop up form when Access doesn't recognize the field name in the query. That would lead us to believe that you've got some spelling problem or something. I don't believe there's any other reason for you to get the parameter prompt.

Have you tried just running it as a select query:

SELECT * FROM tblconsulting INNER JOIN tblsalescredit
ON tblconsulting.WorkOrder = tblsalescredit.[WO#] AND tblconsulting.CompanyID = tblsalescredit.CompanyID

does that work? Do you still get the parameter prompt? Does your field CREDIT show up in the results?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design