Updating Two Related Fields in One Query

  • Thread starter Thread starter SANJAY SHAH \(MICROBRAIN Computers Pvt. Ltd.\)
  • Start date Start date
S

SANJAY SHAH \(MICROBRAIN Computers Pvt. Ltd.\)

Dear Sir,

In My Stock Master Table I am having following fields.

StockMaster
-------------
ItemCode Quantity Rate Value
------------- ---------- -------- ---------
10001 10 10 100

In Purchase Entry I had Purchase 5 quantity & Value is 60 and I have to
update as follows.

Purchase
---------
ItemCode Quantity Rate Value
-------------- -------- --------- --------
10001 5 12 60

StockMaster.Quantity=StockMaster.Quantity+Purchase.Quantity
StockMaster.Value=StockMaster.Value+Purchase.Value
StockMaster.Rate=StockMaster.Value/StockMaster.Quantity

So After Updating Result must be as follows
StockMaster
-------------
ItemCode Quantity Rate Value
------------- ---------- -------- ---------
10001 15 10.66 160

I had given query as follows
UPDATE Purchase, StockMaster SET
StockMaster.Quantity=StockMaster.Quantity+Purchase.Quantity
StockMaster.Value=StockMaster.Value+Purchase.Value
StockMaster.Rate=StockMaster.Value/StockMaster.Quantity

But It shows following result
StockMaster
-------------
ItemCode Quantity Rate Value
------------- ---------- -------- ---------
10001 15 10 160

While updating rate it is picking old value. How to do in one query. Please
reply me.

Sanjay Shah
 
SANJAY SHAH (MICROBRAIN Computers Pvt. Ltd.) said:
Dear Sir,

In My Stock Master Table I am having following fields.

StockMaster
-------------
ItemCode Quantity Rate Value
------------- ---------- -------- ---------
10001 10 10 100

In Purchase Entry I had Purchase 5 quantity & Value is 60 and I have to
update as follows.

Purchase
---------
ItemCode Quantity Rate Value
-------------- -------- --------- --------
10001 5 12 60

StockMaster.Quantity=StockMaster.Quantity+Purchase.Quantity
StockMaster.Value=StockMaster.Value+Purchase.Value
StockMaster.Rate=StockMaster.Value/StockMaster.Quantity

So After Updating Result must be as follows
StockMaster
-------------
ItemCode Quantity Rate Value
------------- ---------- -------- ---------
10001 15 10.66 160

I had given query as follows
UPDATE Purchase, StockMaster SET
StockMaster.Quantity=StockMaster.Quantity+Purchase.Quantity
StockMaster.Value=StockMaster.Value+Purchase.Value
StockMaster.Rate=StockMaster.Value/StockMaster.Quantity

But It shows following result
StockMaster
-------------
ItemCode Quantity Rate Value
------------- ---------- -------- ---------
10001 15 10 160

While updating rate it is picking old value. How to do in one query. Please
reply me.
Click to expand...
Hi Sanjay,

Your data may be more complicated than this, but...

start with a simple SELECT query in query designer:

Click on "Create Query in Design View"

In the Show Table dialog box,
click on your StockMaster table,
click Add,
click on Purchase table,
click on Add again,
and then click Close.

You should now show each of your tables
in the query designer.

Right-mouse click on the StockMaster table
and choose Properties.
In the Alias row, type in
S
then close the Properties dialog box.

Right-mouse click on the Purchase table
and choose Properties.
In the Alias row, type in
P
then close the Properties dialog box.

I believe you want to join them
on the ItemCode fields.

Click and hold down on left table's
ItemCode field
and "drag and drop"
over on right table's ItemCode field.

You should now have a (join) line
connecting the 2 tables going
from S.ItemCode to P.ItemCode
fields.


Drag and Drop Quantity field from
S table down in to field row
of first column of grid.

Drag and Drop Rate field from
S table down in to field row
of second column of grid.

Drag and Drop Value field from
S table down in to field row
of third column in grid.

If you go to SQL View, it should look like:

SELECT
S.Quantity,
S.Rate,
S.[Value]
FROM
StockMaster As S
INNER JOIN
Purchase As P
ON
S.ItemCode = P.ItemCode;

BTW "Value" is a reserved word in
Access and you should probably change
the name of the field if you can, or at
least, always surround with brackets in
your queries. It is a "gotcha" just waiting
to happen.

In top menu, select "Query/ Update Query"
to change your query to an update query.

Your grid in Design View will change to

Field:
Table:
Update To:

Fill in the Update To rows.

Field: Quantity
Table: S
Update To: S.Quantity+P.Quantity

Field: Rate
Table: S
Update To: (S.[Value]+P.[Value])/(S.Quantity+P.Quantity)


Field: [Value]
Table: S
Update To: S.[Value]+P.[Value]

if you go into SQL View, it will look like:

UPDATE StockMaster AS S
INNER JOIN Purchase AS P
ON S.ItemCode = P.ItemCode
SET S.Quantity = .[Quantity]+[P].[Quantity],
S.Rate = (.[Value]+[P].[Value])/(.[Quantity]+[P].[Quantity]),
S.[Value] = .[Value]+[P].[Value];

Please respond back if I have misunderstood
or was not clear about something.

Good luck,

Gary Walter
 
Dear Gary Walter,

I thank you very much for your reply & with your query I am getting result.

But to Update Value we have to again calculate stockquantity & stock Value.
it will increase length of query. If in one query I have to update more then
10 fields with such calculation then it will be very complicated.

Any way thanks for your reply.

Sanjay Shah

Gary Walter said:
message news:[email protected]...
Dear Sir,

In My Stock Master Table I am having following fields.

StockMaster
-------------
ItemCode Quantity Rate Value
------------- ---------- -------- ---------
10001 10 10 100

In Purchase Entry I had Purchase 5 quantity & Value is 60 and I have to
update as follows.

Purchase
---------
ItemCode Quantity Rate Value
-------------- -------- --------- --------
10001 5 12 60

StockMaster.Quantity=StockMaster.Quantity+Purchase.Quantity
StockMaster.Value=StockMaster.Value+Purchase.Value
StockMaster.Rate=StockMaster.Value/StockMaster.Quantity

So After Updating Result must be as follows
StockMaster
-------------
ItemCode Quantity Rate Value
------------- ---------- -------- ---------
10001 15 10.66 160

I had given query as follows
UPDATE Purchase, StockMaster SET
StockMaster.Quantity=StockMaster.Quantity+Purchase.Quantity
StockMaster.Value=StockMaster.Value+Purchase.Value
StockMaster.Rate=StockMaster.Value/StockMaster.Quantity

But It shows following result
StockMaster
-------------
ItemCode Quantity Rate Value
------------- ---------- -------- ---------
10001 15 10 160

While updating rate it is picking old value. How to do in one query. Please
reply me.
Click to expand...
Hi Sanjay,

Your data may be more complicated than this, but...

start with a simple SELECT query in query designer:

Click on "Create Query in Design View"

In the Show Table dialog box,
click on your StockMaster table,
click Add,
click on Purchase table,
click on Add again,
and then click Close.

You should now show each of your tables
in the query designer.

Right-mouse click on the StockMaster table
and choose Properties.
In the Alias row, type in
S
then close the Properties dialog box.

Right-mouse click on the Purchase table
and choose Properties.
In the Alias row, type in
P
then close the Properties dialog box.

I believe you want to join them
on the ItemCode fields.

Click and hold down on left table's
ItemCode field
and "drag and drop"
over on right table's ItemCode field.

You should now have a (join) line
connecting the 2 tables going
from S.ItemCode to P.ItemCode
fields.


Drag and Drop Quantity field from
S table down in to field row
of first column of grid.

Drag and Drop Rate field from
S table down in to field row
of second column of grid.

Drag and Drop Value field from
S table down in to field row
of third column in grid.

If you go to SQL View, it should look like:

SELECT
S.Quantity,
S.Rate,
S.[Value]
FROM
StockMaster As S
INNER JOIN
Purchase As P
ON
S.ItemCode = P.ItemCode;

BTW "Value" is a reserved word in
Access and you should probably change
the name of the field if you can, or at
least, always surround with brackets in
your queries. It is a "gotcha" just waiting
to happen.

In top menu, select "Query/ Update Query"
to change your query to an update query.

Your grid in Design View will change to

Field:
Table:
Update To:

Fill in the Update To rows.

Field: Quantity
Table: S
Update To: S.Quantity+P.Quantity

Field: Rate
Table: S
Update To: (S.[Value]+P.[Value])/(S.Quantity+P.Quantity)


Field: [Value]
Table: S
Update To: S.[Value]+P.[Value]

if you go into SQL View, it will look like:

UPDATE StockMaster AS S
INNER JOIN Purchase AS P
ON S.ItemCode = P.ItemCode
SET S.Quantity = .[Quantity]+[P].[Quantity],
S.Rate = (.[Value]+[P].[Value])/(.[Quantity]+[P].[Quantity]),
S.[Value] = .[Value]+[P].[Value];

Please respond back if I have misunderstood
or was not clear about something.

Good luck,

Gary Walter
Click to expand...
 
Hi Sanjay,

"water from the moon"

You cannot calculate based on what it
*will be*, until it has become, or use
how it will get there.

Only other alternative is run update
on Quantity and Value, then run
another update on Rate.

That's just reality.

Gary Walter

SANJAY SHAH (MICROBRAIN Computers Pvt. Ltd.) said:
Dear Gary Walter,

I thank you very much for your reply & with your query I am getting result.

But to Update Value we have to again calculate stockquantity & stock Value.
it will increase length of query. If in one query I have to update more then
10 fields with such calculation then it will be very complicated.

Any way thanks for your reply.

Sanjay Shah

Gary Walter said:
message news:[email protected]...
Dear Sir,

In My Stock Master Table I am having following fields.

StockMaster
-------------
ItemCode Quantity Rate Value
------------- ---------- -------- ---------
10001 10 10 100

In Purchase Entry I had Purchase 5 quantity & Value is 60 and I have to
update as follows.

Purchase
---------
ItemCode Quantity Rate Value
-------------- -------- --------- --------
10001 5 12 60

StockMaster.Quantity=StockMaster.Quantity+Purchase.Quantity
StockMaster.Value=StockMaster.Value+Purchase.Value
StockMaster.Rate=StockMaster.Value/StockMaster.Quantity

So After Updating Result must be as follows
StockMaster
-------------
ItemCode Quantity Rate Value
------------- ---------- -------- ---------
10001 15 10.66 160

I had given query as follows
UPDATE Purchase, StockMaster SET
StockMaster.Quantity=StockMaster.Quantity+Purchase.Quantity
StockMaster.Value=StockMaster.Value+Purchase.Value
StockMaster.Rate=StockMaster.Value/StockMaster.Quantity

But It shows following result
StockMaster
-------------
ItemCode Quantity Rate Value
------------- ---------- -------- ---------
10001 15 10 160

While updating rate it is picking old value. How to do in one query. Please
reply me.
Click to expand...
Hi Sanjay,

Your data may be more complicated than this, but...

start with a simple SELECT query in query designer:

Click on "Create Query in Design View"

In the Show Table dialog box,
click on your StockMaster table,
click Add,
click on Purchase table,
click on Add again,
and then click Close.

You should now show each of your tables
in the query designer.

Right-mouse click on the StockMaster table
and choose Properties.
In the Alias row, type in
S
then close the Properties dialog box.

Right-mouse click on the Purchase table
and choose Properties.
In the Alias row, type in
P
then close the Properties dialog box.

I believe you want to join them
on the ItemCode fields.

Click and hold down on left table's
ItemCode field
and "drag and drop"
over on right table's ItemCode field.

You should now have a (join) line
connecting the 2 tables going
from S.ItemCode to P.ItemCode
fields.


Drag and Drop Quantity field from
S table down in to field row
of first column of grid.

Drag and Drop Rate field from
S table down in to field row
of second column of grid.

Drag and Drop Value field from
S table down in to field row
of third column in grid.

If you go to SQL View, it should look like:

SELECT
S.Quantity,
S.Rate,
S.[Value]
FROM
StockMaster As S
INNER JOIN
Purchase As P
ON
S.ItemCode = P.ItemCode;

BTW "Value" is a reserved word in
Access and you should probably change
the name of the field if you can, or at
least, always surround with brackets in
your queries. It is a "gotcha" just waiting
to happen.

In top menu, select "Query/ Update Query"
to change your query to an update query.

Your grid in Design View will change to

Field:
Table:
Update To:

Fill in the Update To rows.

Field: Quantity
Table: S
Update To: S.Quantity+P.Quantity

Field: Rate
Table: S
Update To: (S.[Value]+P.[Value])/(S.Quantity+P.Quantity)


Field: [Value]
Table: S
Update To: S.[Value]+P.[Value]

if you go into SQL View, it will look like:

UPDATE StockMaster AS S
INNER JOIN Purchase AS P
ON S.ItemCode = P.ItemCode
SET S.Quantity = .[Quantity]+[P].[Quantity],
S.Rate = (.[Value]+[P].[Value])/(.[Quantity]+[P].[Quantity]),
S.[Value] = .[Value]+[P].[Value];

Please respond back if I have misunderstood
or was not clear about something.

Good luck,

Gary Walter
Click to expand...
Click to expand...
 
Back
Top