Updating all fields in a table

  • Thread starter Thread starter Robin Chapple
  • Start date Start date
R

Robin Chapple

I have a membership table [tblMembers2004] and another table
[tblMembersNov2004] that has the current information for many of the
members in [tblMembers2004]

I need to superimpose, ( or replace the complete record), of the
[tblMembersNov2004] on the [tblMembers2004]
linked by the ID field.

What is the best way to do that?
 
Robin

I can't tell if you wish to replace everything in tblMembership2004 with
your "november" table's contents, or update your "membership" with your
"november" data.

The larger issue I see is that you've embedded data in your table NAMES,
rather than in your tables. A table named [tblMembersNov2004] implies that
you could have 12 tables to handle the 12 months of 2004. A table named
[tblMembers2004] implies that you have one table per year.

If these are accurate assumptions on my part, consider further normalizing
of your data structure, to avoid all the spreadsheetly issues you'll have to
come up with work arounds for with your current design (and your post seems
to portray one of these).

For example, a SINGLE table ([tblMembership]) could include a PersonID from
a tblPerson and a DateOfMembership (date) field, plus whatever other info
you wish to keep about the status/quality of membership. Then, when you
needed to find all members joining in November (or December, or April,
2006), it is a simply query against the one table. No updates, no
"refreshing", ...
 
Jeff,

I have mislead you. I keep only one table. tblMembership2004 is the
name used for this example. It is not an annual table.

The November table is a table of additions and revisions performed by
others and supplied to me in order to keep the central records up to
date. There will be other incoming revisions in the future. The name
Nov is used by me to identify the incoming file. It will be deleted
after the update is performed.

I hope that I have explained myself better.

Thanks,

Robin Chapple
 
:
The November table is a table of additions and revisions performed by
others and supplied to me in order to keep the central records up to
date. There will be other incoming revisions in the future. The name
Nov is used by me to identify the incoming file. It will be deleted
after the update is performed.
Click to expand...
Robin Chapple
Click to expand...

If the fields are the same type and size, a simple update query should do.
I'd run a select query to assure that those ID numbers are the same, then
turn it into an update query, replacing field for field.

Roxie Aho
roxiea at usinternet.com
 
In addition to the Update Query, you may need to use an Append Query for new
members.
 
Van T. Dinh said:
In addition to the Update Query, you may need to use an Append Query for
new
members.
Click to expand...

No, one query will both update existing records and add new ones.

Example:

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
 
Yes, Tom.

However, I wrote "You may need .." depending on the Update Query. I find a
number of programmers tend to do these in 2 steps rather than one.
Conceptually, I tend to agree that 2 steps are easier to understand / decode
but efficiency-wise, one step is definitely better.

Personally, I use both depending how complex the query (or queries) need to
be.

--
HTH
Van T. Dinh
MVP (Access)


Tom Lake said:
Van T. Dinh said:
In addition to the Update Query, you may need to use an Append Query for
new
members.
Click to expand...

No, one query will both update existing records and add new ones.

Example:

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
Click to expand...
 
Back
Top