Updating the 1st Instance of a Record

Hello all,

I have created a database that has multiple tables. On one table I have
detailed data for items purchased (table a). On another table I have the
total amount paid for the order (table b). These 2 tables are connected via a
key. When I table a with the data from table b, the total amount is updated
for every line in table a where the key is equal. I want to know how I can
just update the first instance of the record.

sounds like your tables are not structured correctly. one clue is copying
data from one table to another table; a second clue is storing a "total"
value in a table, where presumably the individual values are already stored
in another table.

suggest you post your table structure, in the following setup:

TableA
FieldName (primary key)
NextFieldName
AndNextFieldName
(list all the fields. where a name is not clearly indicative of the kind of
data the field holds, a bit of explanation would help, as well as the
field's data type.)

TableB
FieldName (primary key)
FieldName (foreign key from TableA)
NextFieldName
AndNextFieldName
(ditto the above note.)

hth


"Frank" wrote in message
...
Hello all,

I have created a database that has multiple tables. On one table I have
detailed data for items purchased (table a). On another table I have the
total amount paid for the order (table b). These 2 tables are connected
via a
key. When I table a with the data from table b, the total amount is
updated
for every line in table a where the key is equal. I want to know how I can
just update the first instance of the record.

On Thu, 8 Jan 2009 18:12:01 -0800, Frank
wrote:

Hello all,

I have created a database that has multiple tables. On one table I have
detailed data for items purchased (table a). On another table I have the
total amount paid for the order (table b). These 2 tables are connected via a
key. When I table a with the data from table b, the total amount is updated
for every line in table a where the key is equal. I want to know how I can
just update the first instance of the record.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or in the control source of a Form or a Report
textbox.
--

John W. Vinson [MVP]

In addition to the suggestions already offered by Tina & John, I'm curious
how you think Access decides which record is "the first instance"?

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Frank" wrote in message
...
Hello all,

I have created a database that has multiple tables. On one table I have
detailed data for items purchased (table a). On another table I have the
total amount paid for the order (table b). These 2 tables are connected
via a
key. When I table a with the data from table b, the total amount is
updated
for every line in table a where the key is equal. I want to know how I can
just update the first instance of the record.

As the others have pointed out you shouldn't store the totals but compute
them at runtime, either on the fly in a query, or in a computed control in a
report. In a query you can return the total per order provided you don't
want to also return the details per order by grouping on the columns from the
Orders table (i.e. your table b), and summing the amounts from the
OrderDetails table (your table a), e.g.

SELECT Orders.OrderNumber, Orders.OrderDate, Orders.CustomerID,
SUM(OrderDetails.Amount) AS TotalAmount
FROM Orders INNER JOIN OrderDetails
ON Orders.OrderNumber = OrderDetails.OrderNumber
GROUP BY Orders.OrderNumber, Orders.OrderDate, Orders.CustomerID;

If you want to include the details of each item ordered then you need to use
a subquery to compute the total, e.g.

SELECT Orders.OrderNumber, Orders.OrderDate,
Orders.CustomerID, OrderDetails.ProductID,
(SELECT SUM(OD2.Amount)
FROM OrderDetails AS OD2
WHERE OD2.OrderNumber = OD1.OrderNumber)
AS TotalAmount
FROM Orders INNER JOIN OrderDetails AS OD1
ON Orders.OrderNumber = OD1.OrderNumber;

Note how in this case the two instances of the OrderDetails table are
distinguished by being given aliases OD1 and OD2. This enables the subquery
to be correlated with the outer query so that it returns the sum of the
amounts for the outer query's current OrderNumber in each case.

This second query will return the total amount per order in every row of its
result table, just as when you join your tables in which the total amounts
are stored. This is just how it works; you can't return the total in the
'first instance of the record' only. For one thing the concepts of 'first',
'last' etc are meaningless in the context of tables as a table is a set, and,
if you ever touched upon sets in maths at school you may recall that by
definition a set has no order, so there is no 'first' or 'last' member of a
set. A row might be first in an order applied to a set of rows of course by
virtue of having the earliest date or being the first in an alphabetical or
numerical sort order of distinct values, but that's not the same as a 'first
row' per se.

It doesn’t of course matter that the amounts are repeated because a query
like this would almost certainly be used as the basis for a report. The raw
result set of a query is only a means to an end in any database application
worth the name.

For a report, however, you don't actually need to compute the total amounts
in the underlying query. You can base the report on a simple query which
joins the tables, e.g.

SELECT Orders.OrderNumber, Orders.OrderDate,
Orders.CustomerID, OrderDetails.ProductID, OrderDetials.Amount
FROM Orders INNER JOIN OrderDetails
ON Orders.OrderNumber = OrderDetails.OrderNumber;

The above query is oversimplified of course as you'd also want to include a
Customers and Products table to bring in values such as the customer's name
and the product name etc. However, it serves to illustrate the point, which
is that in a report you'd group on OrderNumber and out the OrderNumber,
OrderDate etc in a group header, the ProductID and Amount in the detail
section, and an unbound control in the group footer with a ControlSource
property of:

=Sum([Amount])

to return the total per order.

In a form you'd adopt a different approach, basing the form on the Orders
table, and embedding a subform based on the OrderDetials table within it,
linking them on OrderNumber. You can then put a computed control in the
subform's footer, again with a ControlSource property of:

=Sum([Amount])

to return the total per order. You'll find an example of this in the sample
Northwind database which comes with Access where the Orders form includes a
subform which computes the total per order in this way. In the Northwind
example the total is hidden in the subform, however, and shown in a Subtotal
text box control in the main parent form which references the subform's
OrderSubtotal control by means of an expression as its ControlSource property
of:

=[Orders Subform].Form!OrderSubtotal

In the above its been assumed that the OrderDetails table includes an Amount
column, but this might not necessarily be the case. Its more likely that it
will contain columns such as UnitPrice and Quantity, in which case I should
not also contain an Amount column as this can be computed in an unbound
control with a ControlSource property of:

=[UnitPrice]*[Quantity]

or in a computed column in the subform's underlying query. This is how the
Northwind example does it, in an ExtendedPrice computed column, but using a
more involved expression which also takes a customer discount into account.
Doing it in the query in this case means that the subform need only refer to
the ExtendedPrice column rather than repeating the expression each time.

Ken Sheridan
Stafford, England

"Frank" wrote:

Hello all,

I have created a database that has multiple tables. On one table I have
detailed data for items purchased (table a). On another table I have the
total amount paid for the order (table b). These 2 tables are connected via a
key. When I table a with the data from table b, the total amount is updated
for every line in table a where the key is equal. I want to know how I can
just update the first instance of the record.