It works - brillant, thanks ever so much. I tried it out on a few records to
prove to myself that it added new members, annoted removed members and
changed current members details.
I suppose the next step is to write a macro that executes all 7 steps? The
only intervention would be to insert the Removed Date in Step 5. Can that be
done? I havn't written macros in Access before.

"Evi" wrote:

I would suggest this as step 3 onwards.

3 You don't even need to import the Excel spreadsheet. Copy and paste your
Excel data from the spreadsheet straight into your MembersUpdate table. Just
make sure that the columns in the spreadsheet are in the same order as the
fields on your table. (adjust the order of the fields in both your table if
they aren't) and don't select more of the spreadsheet than the required rows
and columns ie don't select a load of blank cells by selecting using the
column headings. - the easiest way to select a long list is to start at the
bottom right of the list and sweep the mouse upwards and left.
To copy and paste into your new table, select the first row by clicking in
the grey area to the left of the row (don't click in the field).
Press Paste on the toolbar.
4.Run the Update query to update Members details from your ExcelTable (leave
out the space when naming it).
5.Run the Find Unmatched and Update query to identify which members are no
longer in your new list and Update their DateRemoved field.
6.After doing all else, Append all the members from your ExcelTable to your
Members Table (duplicate MemberNumbers won't be imported because you
presumably have set your Primary Key field to No Duplicates)
7.When everything looks right, run a delete query to empty your ExcelTable
ready for the next import.

Evi



"Pennington" wrote in message
...
OK lets see if I have got this right:
1. Back up my database
2. Create a copy of the Members table without the data. Name it Members
Update table (This allows me to keep the field names which might be
different/additional to the import data)
3. Import Excel data into a new table named Excel table (This is because
the
field names might be different)
4. Create and run an Append Query to add the data in the Excel table to
the
Members Update table (This is so that the field names can be matched)
5. Create and run an Append Query to add the new members to the Members
table
6. Create and run an Unmatched Query to identify Members Removed and
change
it to an Update Query with the #Date Removed# in the "Update To" line,
then
run the Query. (I will have now identified ex-members and new members but
not
updated current members details if they have changed - this is the next
step)
7. Create Update Query linking Members table and members Update table by
MemberNumber with [Members Update Table]![fieldname] (except member
number)
in the Update To row and RUN Query (Member details are now uopdated
8. Create and run a Delete Query on the Members Update table and the Excel
table to make them ready for receiving the next update.

I could probably skip step 4 if I make sure the fields of the Excel file
are
the same as the Members table before I import it and import directly into
the
members Update Table.

Anyway I will try this out and thanks for your help

"Evi" wrote:

Yes of course. Back up your db before you start these procedures in
case
they don't do what you expect them to.
I suggest that when you import stuff from Excel, first use an Append
query
to put it into a table that has all the field names and data formats you
require (ie a copy/paste of the Main table but without its data. That
way,
you can save all your append/update queries and use them next time
because
none of the field or Table names will have changed. Your final query in
the
series will be a Delete Query which will delete everything from this
ImportTable ready for next time.
.. Eventually, when you know that everything is working as you expect it
to,
you can create a macro which runs everything, one after the other but
you
can imagine the chaos it can create if it updates or appends the wrong
thing
and you don't even see it as it happens..

If you want to replace everything for that member (and of course, I
should
have thought of that!) different or not, then you can still use the
Update
query. Join the 2 tables by the MemberNumber in Query Design. Put all
the
fields from your *Current Table* (except MemberNumber) into your query
grid.
Change to an Update Query.

In the UpdateTo row under each column, type eg

[YourImportTablesName]![FirstName]
or
[YourImportTablesName]![Surname]
etc

Substituting with the correct Table and field names. Remember, these
Update
To entries must not have quote marks around them. Press the Red
Exclamation
mark and run the query.
Evi




"Pennington" wrote in message
...
Thanks, I will try Evi's solution but when I read the blurb in the
Access
Help I got the impression that I had to type an expression or value in
the
Update To cell and I didn't want to have to do that - I just wanted to
replace the content if it was different. In fact it would not matter
if
all
matching cells were replaced even if there was no difference.

"John W. Vinson" wrote:

On Fri, 21 Mar 2008 12:21:01 -0700, Pennington
wrote:

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?

In what way is an update query inappropriate? If you want to update
existing
records from a second table matching by primary key, an Update Query
is
exactly what you would use!

--

John W. Vinson [MVP]