Update Queries:

For this example, I am using the InvenTre database that is available on CIS54U. To design an update query, first go to the query tab and then select New followed by Design View. Select the tables that are involved in the query and then go to Query and select Update Query. In this example I am showing one that was already developed so I got to it through Design.

The information here is that I want to update the OnHand field for all records where the InvCode on the SaleRecpt table contains an S. The updating that I want to do is to subtract the InvAmt on the SaleRecpt table from the OnHand amount on the Tree table.

The code is: [Tree]![OnHand]-[SaleRecpt]![InvAmt]

When this query is run by selecting OPEN, it will bring up a warning that this will effect data and then execute. The update will be done.

The example above is another example of an update query. In this example the InvAmt on the SalesRecpt table is being added to OnHand on the Tree table and subtracted from OnOrder on the Tree table if the InvCode on the SaleRecpt table is an R.

The code is:

[Tree]![OnHand]+[SaleRecpt]![InvAmt]

[Tree]![OnOrder]-[SaleRecpt]![InvAmt]

Now, lets look at the data and the impact of the update. The first table to look at is the SaleRecpt table:

 

ItemNo

InvCode

InvAmt

1212

S

10

1234

R

10

1234

S

5

1245

S

5

2012

R

10

2012

S

2

Now we will look at the part of the Tree table that contains OnHand and OnOrder before the update is executed.

ItemNo

ItemName

OnHand

OnOrder

ReorderPt

1111

Oak

5

10

50

1212

Red Maple

26

22

25

1234

Sugar Maple

35

25

50

1245

Hemlock

10

17

50

1256

Blue Spruce

10

29

25

1267

White Pine

25

50

50

1275

Black Pine

7

12

25

1290

Birch

11

0

25

2000

Dogwood

12

20

45

2012

Cherry

25

20

25

2036

Elm

50

25

25

Now I will run the Receipt Update (UpdateRecpt) query.

ItemNo

ItemName

OnHand

OnOrder

ReorderPt

1111

Oak

5

10

50

1212

Red Maple

26

22

25

1234

Sugar Maple

45

15

50

1245

Hemlock

10

17

50

1256

Blue Spruce

10

29

25

1267

White Pine

25

50

50

1275

Black Pine

7

12

25

1290

Birch

11

0

25

2000

Dogwood

12

20

45

2012

Cherry

35

10

25

2036

Elm

50

25

25

As you can see above, first a message comes up warning you that two records will be effected. If you continue with the update the changes will be made in the Tree table. The two records that were effected were 1234 and 2012 because these are the two records that had an R in the InvCode. Compare the current numbers in OnHand and OnOrder to the numbers before the update was executed.

This example uses the Dairy database. In the example, I am taking transaction information to update the master and grabbing pricing information from inventory. This will only be done for transactions with today's date. The formula is:

[BillingDetail]![NumDelivered]*[Inventory]![Price] and the criteria is: Date()

CustIdNo

DeliveryDay

ItemNo

DateDelivery

NumDelivered

12456

3

11117

3/30/97

4

12456

3

22221

4/9/00

3

Notice that I have added a new transaction on 4/9/00 for customer 12456. Currently customer 12456 has $.00 as the current amount. Looking above at the query design, I can see that that is the field on the customer table that is being updated. When I execute the query, it warns me that one record will be updated. Since there is only one record for this date, that makes sense. After the update customer 12456 has $2.67 as the current amount. This is because 3 things were ordered and a check of the inventory file shows me that item 22221 sells for $0.89.