Re: Update and Append Query

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Tom Lake (tlake_at_twcny.rr.com)
Date: 11/10/04 

Date: Tue, 9 Nov 2004 19:01:46 -0500

>> > You need two separate queries, an Update and an Append one. The trick
>> > is
>> > to
>> > use the right filter; the easiest way is to use a subquery in the
>> > criterion
>> > on the F6 field.
>>
>> No you don't. One query will do both.
>>
>> Tom Lake
>
> How would you do that then Tom? I have to confess I thought he would have
> to use two aswell.
> If it is possible with one query it would make some of my databases far
> tidier.

OK, lets say you have a Master and a Transaction table with Trans ID being
the PK in both. You want to add the day's transactions to the Master table
and modify any records that have changed throughout the day. You add the
Master table to an Update query, then add the Transaction table. Now drag a
line from the Trans ID in the Transaction table to the Trans ID in the
Master table. Make the join select all records from the Transaction table
so the arrowhead points to the Master table. This is called a right join
although the arrow will be pointing left if you added the tables to the
query in the order I specified. Now drag down all (or as many as you want
updated) the fields from the Master table to the grid. In the "Update To"
row add fields from the Transaction table. When you run this query, new
records will be added and existing records will be changed. It's that
simple. Here's my code:

UPDATE Master RIGHT JOIN [Transaction] ON Master.[Trans ID] =
Transaction.[Trans ID] SET Master.[Trans ID] = [Transaction].[Trans ID],
Master.Name = [Transaction].[Name], Master.DOB = [Transaction].[DOB];

Tom Lake

Relevant Pages

  • Re: Update and Append Query
    ... UPDATE Master RIGHT JOIN [Transaction] ... "Tom Lake" wrote: ... One query will do both. ...
    (microsoft.public.access.queries)
  • Re: Simply adding two fields
    ... I'm reading a transaction file of widgets delivered and I want to update the ... quantity in the master record, so I do a query joining the master and ... Just redo the calculation whenever you need it, ...
    (microsoft.public.access.queries)
  • Re: Simply adding two fields
    ... In an update query you update the field to the calculation. ... field before you add it to the master field you will have a problem. ... I'm reading a transaction file of widgets delivered and I want to update ...
    (microsoft.public.access.queries)
  • Re: Simple(?) sql question...
    ... I have what I hope is a simple sql question. ... One is a transaction file with two fields, ... and in fact it will make your query perform worse. ... from master a join detail b ...
    (comp.sys.ibm.as400.misc)
  • Re: Subreports not printing in the right order (more details)
    ... problem with my Master Query and the master/child links. ... Query1 from table1 ... My master report was sourced from Query1 ... Update the master/child links in the subreports to be: ...
    (microsoft.public.access.reports)