Help with an Update Query

[thedrumdoctor]

I’ll try and simplify this by only including fields from the tables in
question which are relevant to the query I want to perform. First of all,
I’ll start with Table A:

Table A

StockNumber (PK, auto number)
UnitCost (Currency)

Table B

StockNumber (Number)
UnitCost (Currency)

[Table A.StockNumber] has a one-to-many relationship with [Table
B.StockNumber]

At the moment, [Table B.UnitCost] contains currency values that I want to
transfer to [Table A.UnitCost].

The [StockNumber] fields on both tables obviously link the two tables
together so I want to create a query along the lines of:

Where the number value in [Table A.StockNumber] is equal to the number value
in [Table B.StockNumber], update the [Table A.UnitCost] currency value to the
currency value held in [Table B.UnitCost].

I hope this makes sense!

Unfortunately, I don’t know what the syntax for this would be in Design View
or how I would go about doing it in SQL view, if indeed that would turn out
to be the best solution.

 

[KARL DEWEY]

[Table A.StockNumber] has a one-to-many relationship with [Table
B.StockNumber]
There is a problem in trying to do what you want. The will be some
StockNumbers that have multiple UnitCost in Table B. Which UnitCost is to be
used? Do you have dates associated with the records in Table B?

This would be the SQL if you did not have the problem ---
UPDATE [Table A] INNER JOIN [Table B] ON [Table A].[StockNumber] = [Table
B].[StockNumber] SET [Table A].[UnitCost] = [Table B].[UnitCost];

 

[thedrumdoctor]

Thanks very much for the reply, it is appreciated.

In answer to your questions; The UnitCost values I want to use are all in
Table B and all the records in Table B have dates associated with them. I'm
guessing that the inclusion of a date field will help eliminate the problem
of entering multiple UnitCost values in some way?

[Table A.StockNumber] has a one-to-many relationship with [Table

B.StockNumber]
There is a problem in trying to do what you want. The will be some
StockNumbers that have multiple UnitCost in Table B. Which UnitCost is to be
used? Do you have dates associated with the records in Table B?

This would be the SQL if you did not have the problem ---
UPDATE [Table A] INNER JOIN [Table B] ON [Table A].[StockNumber] = [Table
B].[StockNumber] SET [Table A].[UnitCost] = [Table B].[UnitCost];

--
KARL DEWEY
Build a little - Test a little

I’ll try and simplify this by only including fields from the tables in
question which are relevant to the query I want to perform. First of all,
I’ll start with Table A:

Table A

StockNumber (PK, auto number)
UnitCost (Currency)

Table B

StockNumber (Number)
UnitCost (Currency)

[Table A.StockNumber] has a one-to-many relationship with [Table
B.StockNumber]

At the moment, [Table B.UnitCost] contains currency values that I want to
transfer to [Table A.UnitCost].

The [StockNumber] fields on both tables obviously link the two tables
together so I want to create a query along the lines of:

Where the number value in [Table A.StockNumber] is equal to the number value
in [Table B.StockNumber], update the [Table A.UnitCost] currency value to the
currency value held in [Table B.UnitCost].

I hope this makes sense!

Unfortunately, I don’t know what the syntax for this would be in Design View
or how I would go about doing it in SQL view, if indeed that would turn out
to be the best solution.

 

[KARL DEWEY]

I assume you want to use the latest UnitCost so what you have to do is create
a totals query for Table B with [Table B].[StockNumber], [Table
B].[UnitCost], and Max([Table B].[UnitCost]) AS MaxOfUnitCost.

Then use the query instead of Table B in the update query.

--
KARL DEWEY
Build a little - Test a little

Thanks very much for the reply, it is appreciated.

In answer to your questions; The UnitCost values I want to use are all in
Table B and all the records in Table B have dates associated with them. I'm
guessing that the inclusion of a date field will help eliminate the problem
of entering multiple UnitCost values in some way?

[Table A.StockNumber] has a one-to-many relationship with [Table

B.StockNumber]
There is a problem in trying to do what you want. The will be some
StockNumbers that have multiple UnitCost in Table B. Which UnitCost is to be
used? Do you have dates associated with the records in Table B?

This would be the SQL if you did not have the problem ---
UPDATE [Table A] INNER JOIN [Table B] ON [Table A].[StockNumber] = [Table
B].[StockNumber] SET [Table A].[UnitCost] = [Table B].[UnitCost];

--
KARL DEWEY
Build a little - Test a little

I’ll try and simplify this by only including fields from the tables in
question which are relevant to the query I want to perform. First of all,
I’ll start with Table A:

Table A

StockNumber (PK, auto number)
UnitCost (Currency)

Table B

StockNumber (Number)
UnitCost (Currency)

[Table A.StockNumber] has a one-to-many relationship with [Table
B.StockNumber]

At the moment, [Table B.UnitCost] contains currency values that I want to
transfer to [Table A.UnitCost].

The [StockNumber] fields on both tables obviously link the two tables
together so I want to create a query along the lines of:

Where the number value in [Table A.StockNumber] is equal to the number value
in [Table B.StockNumber], update the [Table A.UnitCost] currency value to the
currency value held in [Table B.UnitCost].

I hope this makes sense!

Unfortunately, I don’t know what the syntax for this would be in Design View
or how I would go about doing it in SQL view, if indeed that would turn out
to be the best solution.

 

[thedrumdoctor]

Many thanks, I'll give that a go!

I assume you want to use the latest UnitCost so what you have to do is create
a totals query for Table B with [Table B].[StockNumber], [Table
B].[UnitCost], and Max([Table B].[UnitCost]) AS MaxOfUnitCost.

Then use the query instead of Table B in the update query.

--
KARL DEWEY
Build a little - Test a little

Thanks very much for the reply, it is appreciated.

In answer to your questions; The UnitCost values I want to use are all in
Table B and all the records in Table B have dates associated with them. I'm
guessing that the inclusion of a date field will help eliminate the problem
of entering multiple UnitCost values in some way?

[Table A.StockNumber] has a one-to-many relationship with [Table
B.StockNumber]
There is a problem in trying to do what you want. The will be some
StockNumbers that have multiple UnitCost in Table B. Which UnitCost is to be
used? Do you have dates associated with the records in Table B?

This would be the SQL if you did not have the problem ---
UPDATE [Table A] INNER JOIN [Table B] ON [Table A].[StockNumber] = [Table
B].[StockNumber] SET [Table A].[UnitCost] = [Table B].[UnitCost];

--
KARL DEWEY
Build a little - Test a little


:

I’ll try and simplify this by only including fields from the tables in
question which are relevant to the query I want to perform. First of all,
I’ll start with Table A:

Table A

StockNumber (PK, auto number)
UnitCost (Currency)

Table B

StockNumber (Number)
UnitCost (Currency)

[Table A.StockNumber] has a one-to-many relationship with [Table
B.StockNumber]

At the moment, [Table B.UnitCost] contains currency values that I want to
transfer to [Table A.UnitCost].

The [StockNumber] fields on both tables obviously link the two tables
together so I want to create a query along the lines of:

Where the number value in [Table A.StockNumber] is equal to the number value
in [Table B.StockNumber], update the [Table A.UnitCost] currency value to the
currency value held in [Table B.UnitCost].

I hope this makes sense!

Unfortunately, I don’t know what the syntax for this would be in Design View
or how I would go about doing it in SQL view, if indeed that would turn out
to be the best solution.