Re: Update and Append Query
- From: "Keith Meier, MCSA" <KeithMeierMCSA@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 14 Apr 2005 13:59:02 -0700
I have a similar update, however I want to limit the updated fields to one,
and want to make sure I've got my SQL/Jet understanding correct.
My tables have 3 keys: TransID, Month, Year (both tables set as the same
format) and when all 3 keys are the same, update the name & DOB.
UPDATE Master RIGHT JOIN [Transaction]
ON Master.[Trans ID] = Transaction.[Trans ID]
AND Master.[Month] = Transaction.[Month]
AND Master.[Year] = Transaction.[Year]
SET Master.[Trans ID] = [Transaction].[Trans ID], Master.Name =
[Transaction].[Name], Master.DOB = [Transaction].[DOB];
Is my new statement correct?
Thank you,
"Tom Lake" wrote:
> >> > 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
>
>
>
.
- Follow-Ups:
- Re: Update and Append Query
- From: Van T. Dinh
- Re: Update and Append Query
- Prev by Date: Re: Final Plea for Help
- Next by Date: Counting Updated Records
- Previous by thread: howto convert 123 minutes to 2:03
- Next by thread: Re: Update and Append Query
- Index(es):
Relevant Pages
|
