An Update query can be used to do this.
When using Updates or Appends I always back up my database (s) first so that
if anything goes wrong, I can just go back to my original copy.

Add both your Current Table and your Imported table into the query grid.
Join them by the Membership Number (very important to do this)

Add eg Address from your CurrentTable to the query grid
Now (*changing the names to match your real data, of course*)
type in the Criteria row

[ImportedTable]![Address]

That gives you a list of all the members whose addresses are different in
your current table from what they are in your Imported table.
Check visually.
Change the query to an Update Query. it will still be filtered
In the UpdateTo row under your CurrentTable's Address field type
[ImportedTable]![Address]

This will copy the data from the Imported Table Address field for that row
into the Current Table Address field.

Note I've put 'boxes' around the field and table name just in case they
contain nasty stuff like spaces.

Eventually, this can be done more quickly using a macro to run the queries
one after another at the press of a button but for now, until you get used
to doing this, do it one field at a time, using a different query each time
so that you don't accidentally leave stuff in the query and check visually
after each update.

Evi




"Pennington" wrote in message
...
I have encountered a problem.
When I run the Append Query it adds new members to the Members Table and
does not add duplicates which is fine but I want to also update the
Members
Table where the data in a field is different (The Primary Key will not
change
but titles, addresses and e-mails may change). I have looked at an Update
Query but this is not appropriate. What other type of query can I use for
this?

"Pennington" wrote:

Its OK. I have now installed the Query Type button using the Customise
feature - I can now proceed to follow your instructions Evi.

"Pennington" wrote:

Many thanks, its a lot to take in and its getting late over here. Just
a few
more details befiore I sign off for the day.

As I have a DateJoined field I don't need to know when someone left
then
came back becuase if the re-applied they will have a different joining
date.

You mention Update Queries and Append Queries but I can't find how to
write
these. I am using Access 2000 and the Help does mention Design View
having an
arrow next to Query Type but I don't see this. I also don't seem to be
able
to reveal it on the toolbar either. When I click New Queries, Update
Query,
Append Query are not among the choices given. Can you help?

In case you are still wondering why I have different tables, I
explained
that in the previous post. The list I receive from HQ is as Excel file
so I
have to import it don't I?

Many thanks for your indulgence - I weill be back on-line in 8hrs or
so.

"Evi" wrote:

To supply a missing field, in the Append query add a column into
which you
can type

MemberRemoved:True
(if Member Removed is a tick field which you want to set to Ticked)
or (for example)
MemberRemoved:27/04/07
if it's a date field and you want to put the date when they were
removed (a
better idea than a tick field). If you haven't been given an actual
date
then you can use the date when you received your new list or the
Date
indicated by the table name in your Append Query if you are
appending past
records.

That will add your data from your old tables.

When you get your new data, you will use your Find Unmatched query
to see
who isn't there, add your MemberRemoved field from your main table
to the
grid, check you have the correct data, change the query to an
UpdateQuery,
untick the fields you don't want to update but keep them in the
query grid
if they are filtering something and put the date you desire in the
Update
To line

Dates have to be surrounded by # and have the format month slash day
slash
year in full
eg
#07/25/2008#

Having done that, you can now append your new members. Because your
MemberRemoved field is left blank, you don't need to do anything
about that.

As you correctly say, you can now use filtered queries to show old
and new
members.

There is only one thing you may need to consider which will make the
whole
thing more tricky. (but still do-able). I'm still wondering what the
thinking was behind having the different tables in the first place.

Do you have to keep a record of when members were removed and
re-added? If
yes then we will need to change the structure of your database. As
it is
currently, if a member was removed in 1997 and re-added this year
but then
removed, you will have no way of knowing that he was a member in
1996.

Of course, if this doesn't matter, then all is well but if it is
necessary
then you will need to add another table to your database where a
member is
added and removed more than once so that you can see each time he
was in the
club. (Yes, Old Howard has been with us for 35 years now - although
he only
actually paid for 2 of those years!)

You will still need your members table, which will contain each
member only
once and will have the date he originally joined the club but the
members
Table will be added to this second table by MemberNumber field.
The new table could have an Autonumber Primary Key field. It will
have
MemberNumber and your Members table will be linked to it via this.

.. So all members will be appended when they first subscribe but if
they
lapse and are suspended, the date of their suspension will be
recorded in
this new table and if they are re-instated they will be added again
with the
new date of their re-instatement

Just something to ruin your sleep....

Any comments on this, Fred? You are the membership db expert. I'm
assuming
that poor Pennington won't be able to insist on the updates that
will make
his job easier. He may even be knocking his head against my pet
hate 'We've
always done it this way!'
Evi



"Pennington" wrote in message
...
Many thanks, this is most helpful.

Yes you are correct the Reports do have a source record but the
Charts do
not even though the query on which they are based still exists.

The reason I was renaming tables is that I built the database
using the
Dec
07 members list I received. I received an update in Feb 08 and
created a
new
table. Although I could easily establish who the new members were
from the
DateJoined field I used the "Unmatched Query Wizard" to find the
members
that
were not in the list as there is no MembersRemoved field in the
lists I am
sent (I have asked for this data but as yet I am not being sent
it)

Now I have gone back to the first table I created and have
created a new
query as you suggested and presumably I simply produce another
copy with
different criteria depending on whether I want a list of new
members or a
list of ex-members.

Now, how do I import the updated lists for Feb and Mar as there is
no
MemberRemoved field? Even if I create such a field before I import
it, it
will be blank. If I import the data into the existing table I
won't know
if
any members have been removed from the later list

"Evi" wrote:

The reason your report's record source is blank is beccause the
table on
which they were based no longer exists. If you click next to
RecordSource
you can then choose a different table or query from the list on
which to
base your report. When you stop renaming your tables, this will
no
longer
happen.
If you don't have to remove non-current members then it is even
easier -
no
need for an archive table.

You definitely *don't* need a different field for current
members, just
filter using your DateRemoved field or even a tickbox Yes/No
field if
you
need also need some other way to indicate someone has left.
Your unique membership number will ensure that you don't
accidentally
add
member twice.
I really don't understand why you have been renaming tables. Is
it
because
you need to look back to who was your member on any one year? I
can see
why
that could be tricky if a member is suspended and then
re-instated but
there
will be a way of doing that if it is needed.

You will create a query based on your members table. It can have
all the
same fields if you wish. You could call it QryCurrentMembers
In the criteria line under Date Removed, type
Is Null

(for past members your criteria will be Is Not Null)

In Design View of your report, click next to Record Source and
choose
this
query instead of your Table.

Which are the queries you are unsure about creating? We can
'talk' you
through them if we know what you don't already know.

Evi









"Pennington" wrote in
message
...
Yes you are correct. Each member does have a unique membership
number
which I
make the primary key. There is also a field Date Joined but
unfortunately
the
new list I receive has new members but not suspended members
who may
return
or those who have resigned.

From your explanation I think it is what I am seeking but I am
not
sure I
understand it completely. Could your solution mean I have one
list of
members
with a field for indicating "Date Removed" so that after
importing the
updated list into a new table I run a query that adds new
members to
the
members table and for members not found in the update it adds
the
current
month/year in the Date Removed field. Some of those members
that do
not
appear in an updated list may simply be suspended because they
have
not
paid
and are reinstated once they have paid up to date so I need to
keep
their
details on record.

I don't think I need to archive the data as we have only 1300
members
in
the
branch and we don't expect it to go beyond 10,000.

In a Report that I have created using the wizard the record
source
field
is
blank and when I have added a different source record from the
one on
which
it was created I find it does not work. Why is this?

I do need help writing these queries as I am not an expert in
Access,
just
a
guy who volunteered to do the job as I have some experience in
using
it.

"Evi" wrote:

I'm guessing that when you receive your list, you don't know
if the
members
in your current table are also in the new list.
If you do have a way of identifying members (other than name
and
Date of
birth, which is never 100% reliable) like a unique
membership
number, we
can
help you to make a query which sets a CurrentMember tickbox
to True
if
the
member appears in your new list and all the other members in
your
Current
Table to False.
If you need to Archive your old records (because there are
loads of
names),
you can use an Append query to add all the 'False member's
to an
archive
table which will have a Year field so that you know which
year they
were
members. The False members will be deleted from your main
table
using a
simple delete query.
The Archive Table is created by copy/pasting your current
table
(without
the
data).
Add a Year field to both tables to contain the year number.

This method means that you have the option to retrieve a
member from
the
Archive table and append him back if you still want his data
and
even
use a
Union Query to unite the Archive and normal table so that
you can
view
eg a
membership history of your charity.

You can now use your table without any name changes for
reports and
queries.
You can also copy and pasted of some of your reports and
queries,
changing
their name to eg QryArchiveMembers, RptArchivePayments
adjusting the
table
in the query grid to your Archive table and adjusting the
Record
Source
of
the reports to point to these 'Archive' queries

The only alteration you will need to do to reports is to add
a Year
field
and group them by that.
Evi



"Pennington" wrote in
message
...
I have created a membership database for our local branch
of a
charity
and
designed various queries and reports to analyze particular
trends.
The
source
table I called Members Jan 08 but I receive updates every
quarter
and
when
I
import the new membership list naming it Members Mar 08
and change
references
to Jan 08 to Mar 08 in the queries and reports several of
them
fail to
work.
I have had to recreate the queries and reports all over
again.

I don't want to have to do this every quarter so is there
an