Update Query

[Pwyd]

I have the following:

UPDATE VendorRecordsTable SET VendorRecordTable.V_City = [1042-S Summary].City
WHERE VendorRecordsTable.V_AccountNumber=[1042-S Summary].[Vendor Number];



where i'm trying to update the current record in the vendorrecordstable with
data from the 1042-S Summary table, where the two have a matching vendor
number (named v_account number in the first table, and vendor number in the
second table).

whenver i run this query, it puts up a paramter request for 1042-S
Summary.City.
The current query only shows the current vendorrecordstable. there is no
relationship between the vendorrecordstable and the 1042-S summary table,
however, even if i add the 1042-S summary table and put a relationship
between their related fields, it gives the same parameter request. What am i
doing wrong?

 

[KARL DEWEY]

You need a FROM statement with [1042-S Summary] as to where you are sourcing
the data.

 

[Pwyd]

Thanks karl, could you help me with the placement? i'm quite new at this.
The most sequel i've used is to write up basic criteria statements, not
constructing entire queries by hand -- Access seems to really suck at
building them.
I hope the explanation i provided earlier would point to where the data
comes FROM literally?

You need a FROM statement with [1042-S Summary] as to where you are sourcing
the data.
--
Build a little, test a little.

I have the following:

UPDATE VendorRecordsTable SET VendorRecordTable.V_City = [1042-S Summary].City
WHERE VendorRecordsTable.V_AccountNumber=[1042-S Summary].[Vendor Number];



where i'm trying to update the current record in the vendorrecordstable with
data from the 1042-S Summary table, where the two have a matching vendor
number (named v_account number in the first table, and vendor number in the
second table).

whenver i run this query, it puts up a paramter request for 1042-S
Summary.City.
The current query only shows the current vendorrecordstable. there is no
relationship between the vendorrecordstable and the 1042-S summary table,
however, even if i add the 1042-S summary table and put a relationship
between their related fields, it gives the same parameter request. What am i
doing wrong?

 

[John Spencer]

UPDATE VendorRecordsTable INNER JOIN [1042-S Summary]
ON VendorRecordsTable.V_AccountNumber=[1042-S Summary].[Vendor Number]
SET VendorRecordTable.V_City = [1042-S Summary].City

Then you need a where clause that identifies which record in
VendorRecordsTable you want to update.

If you were doing this from a form then the Where clause of the SQL statement
might look like:
WHERE VendorRecordsTable.V_AccountNumber=
Forms![Name of The Form]![V_AccountNumber]


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[KARL DEWEY]

John has provided the solution for you.

There are many books to learn Access but another way is to create the
queries in design view then switch to SQL view and study how the statement is
assembled.

--
Build a little, test a little.

Thanks karl, could you help me with the placement? i'm quite new at this.
The most sequel i've used is to write up basic criteria statements, not
constructing entire queries by hand -- Access seems to really suck at
building them.
I hope the explanation i provided earlier would point to where the data
comes FROM literally?

You need a FROM statement with [1042-S Summary] as to where you are sourcing
the data.
--
Build a little, test a little.

I have the following:

UPDATE VendorRecordsTable SET VendorRecordTable.V_City = [1042-S Summary].City
WHERE VendorRecordsTable.V_AccountNumber=[1042-S Summary].[Vendor Number];



where i'm trying to update the current record in the vendorrecordstable with
data from the 1042-S Summary table, where the two have a matching vendor
number (named v_account number in the first table, and vendor number in the
second table).

whenver i run this query, it puts up a paramter request for 1042-S
Summary.City.
The current query only shows the current vendorrecordstable. there is no
relationship between the vendorrecordstable and the 1042-S summary table,
however, even if i add the 1042-S summary table and put a relationship
between their related fields, it gives the same parameter request. What am i
doing wrong?