Membership database updates

J

John W. Vinson

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!
 
P

Pennington

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.
 
J

John W. Vinson

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.

The expression can be - and would be, in this instance - something like

[Othertable].[fieldname]
 
E

Evi

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
 
P

Pennington

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 said:
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 said:
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.
 
E

Evi

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 said:
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 said:
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 said:
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.

:

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

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!
 
P

Pennington

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 said:
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 said:
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 said:
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




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.

:

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

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!
 
E

Evi

You'll have no trouble doing it, if you've done all this but just test it
all thoroughly on a copy of your db before letting it loose on your precious
data.

The thing the macro won't do is to do the copy /paste from Excel because
you need to check visually if the columns are in the correct order.
So, you've saved your Update & Append & Delete macros

Click on the Macro page. Click New
Under Action, in the first row, choose OpenQuery.
Next to Query Name choose the name of your first query in Step 4 from the
dropdown list
In the next row in the Action column, choose OpenQuery again and next to
Query Name choose the name of your next query from step 5.
etc, etc
To run the macro, go to the macro page and double click it.

There are ways of doing it all without you having to OK each step but do try
it this way for now and read any messages which appear. There could easily
be something neither of us have thought of yet.

That RemovedDate....
If you are going to use the date when you run the update then you can put
Date()
in the UpdateTo line under the RemovedDate field - the RemovedDate will be
the current date when you run the query.

Otherwise you are going to learn another trick - a parameter query
In the NotInList Update query, in the UpdateTo line, in the RemovedDate
column, type
[What Removed Date?]

Because you don't actually have field called What Removed Date? When that
part of the macro runs, a box with the text What Removed Date? above a blank
box. Type your RemovedDate into the box.

Again, test on a copy to see if it works the way you expect it to.

Evi

Pennington said:
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 said:
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 said:
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

:

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




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.

:

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

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!
 
P

Pennington

It works - brilliant. Many thanks for you help
One more thing, how do I put the Date Member Removed in a Report box so it
shows in the title of the report?

I might have others later as I go through the Reports and Charts. Is it OK
if I post them here?

Evi said:
You'll have no trouble doing it, if you've done all this but just test it
all thoroughly on a copy of your db before letting it loose on your precious
data.

The thing the macro won't do is to do the copy /paste from Excel because
you need to check visually if the columns are in the correct order.
So, you've saved your Update & Append & Delete macros

Click on the Macro page. Click New
Under Action, in the first row, choose OpenQuery.
Next to Query Name choose the name of your first query in Step 4 from the
dropdown list
In the next row in the Action column, choose OpenQuery again and next to
Query Name choose the name of your next query from step 5.
etc, etc
To run the macro, go to the macro page and double click it.

There are ways of doing it all without you having to OK each step but do try
it this way for now and read any messages which appear. There could easily
be something neither of us have thought of yet.

That RemovedDate....
If you are going to use the date when you run the update then you can put
Date()
in the UpdateTo line under the RemovedDate field - the RemovedDate will be
the current date when you run the query.

Otherwise you are going to learn another trick - a parameter query
In the NotInList Update query, in the UpdateTo line, in the RemovedDate
column, type
[What Removed Date?]

Because you don't actually have field called What Removed Date? When that
part of the macro runs, a box with the text What Removed Date? above a blank
box. Type your RemovedDate into the box.

Again, test on a copy to see if it works the way you expect it to.

Evi

Pennington said:
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 said:
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



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

:

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




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.

:

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

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!
 
E

Evi

Remember, you will eventually have a list of members who have been removed
on various dates.
You can put a text box in your report header and type

=[DateRemoved]

You can combine this date with text by typing

="Members Removed By " & [DateRemoved]


It will show the DateRemoved of the first record though. This is fine if you
are then going to filter the report so that you show only one group of
removals at a time or if you sort your Dates in Descending Order in the
sorting grouping box so it shows the highest date first.

Or you can type
=Min[DateRemoved] & " to " & Max[DateRemoved]

so that you will see the lowest date on your report and the highest with the
word 'to' in the middle.

You can filter your report using a combo box in a form so you can select the
date you want to see or print. Great fun!

Or you can group your records, using the Sorting/Grouping box by this
DateRemoved and put the DateRemoved field in the Group Header so that you
can see the list of member who were grouped on 21/08/07, the group who were
removed on 20/08/06 etc


Evi



Pennington said:
It works - brilliant. Many thanks for you help
One more thing, how do I put the Date Member Removed in a Report box so it
shows in the title of the report?

I might have others later as I go through the Reports and Charts. Is it OK
if I post them here?

Evi said:
You'll have no trouble doing it, if you've done all this but just test it
all thoroughly on a copy of your db before letting it loose on your precious
data.

The thing the macro won't do is to do the copy /paste from Excel because
you need to check visually if the columns are in the correct order.
So, you've saved your Update & Append & Delete macros

Click on the Macro page. Click New
Under Action, in the first row, choose OpenQuery.
Next to Query Name choose the name of your first query in Step 4 from the
dropdown list
In the next row in the Action column, choose OpenQuery again and next to
Query Name choose the name of your next query from step 5.
etc, etc
To run the macro, go to the macro page and double click it.

There are ways of doing it all without you having to OK each step but do try
it this way for now and read any messages which appear. There could easily
be something neither of us have thought of yet.

That RemovedDate....
If you are going to use the date when you run the update then you can put
Date()
in the UpdateTo line under the RemovedDate field - the RemovedDate will be
the current date when you run the query.

Otherwise you are going to learn another trick - a parameter query
In the NotInList Update query, in the UpdateTo line, in the RemovedDate
column, type
[What Removed Date?]

Because you don't actually have field called What Removed Date? When that
part of the macro runs, a box with the text What Removed Date? above a blank
box. Type your RemovedDate into the box.

Again, test on a copy to see if it works the way you expect it to.

Evi

Pennington said:
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.

:

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



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

:

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




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.

:

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

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!
 
P

Pennington

In the Criteria cell of DateJoined in QryNewMembers I had put a date of the
form Between #dd/mm/yyyy# And #dd/mm/yyyy# and got a list of members who
joined between those dates. I now want it to work so that when I open the
corresponding Report I am prompted to put the month or year and I get a list
of members who joined in that month or year. I have tried the Expression
builder but can't seem to get it to work.

Evi said:
Remember, you will eventually have a list of members who have been removed
on various dates.
You can put a text box in your report header and type

=[DateRemoved]

You can combine this date with text by typing

="Members Removed By " & [DateRemoved]


It will show the DateRemoved of the first record though. This is fine if you
are then going to filter the report so that you show only one group of
removals at a time or if you sort your Dates in Descending Order in the
sorting grouping box so it shows the highest date first.

Or you can type
=Min[DateRemoved] & " to " & Max[DateRemoved]

so that you will see the lowest date on your report and the highest with the
word 'to' in the middle.

You can filter your report using a combo box in a form so you can select the
date you want to see or print. Great fun!

Or you can group your records, using the Sorting/Grouping box by this
DateRemoved and put the DateRemoved field in the Group Header so that you
can see the list of member who were grouped on 21/08/07, the group who were
removed on 20/08/06 etc


Evi



Pennington said:
It works - brilliant. Many thanks for you help
One more thing, how do I put the Date Member Removed in a Report box so it
shows in the title of the report?

I might have others later as I go through the Reports and Charts. Is it OK
if I post them here?

Evi said:
You'll have no trouble doing it, if you've done all this but just test it
all thoroughly on a copy of your db before letting it loose on your precious
data.

The thing the macro won't do is to do the copy /paste from Excel because
you need to check visually if the columns are in the correct order.
So, you've saved your Update & Append & Delete macros

Click on the Macro page. Click New
Under Action, in the first row, choose OpenQuery.
Next to Query Name choose the name of your first query in Step 4 from the
dropdown list
In the next row in the Action column, choose OpenQuery again and next to
Query Name choose the name of your next query from step 5.
etc, etc
To run the macro, go to the macro page and double click it.

There are ways of doing it all without you having to OK each step but do try
it this way for now and read any messages which appear. There could easily
be something neither of us have thought of yet.

That RemovedDate....
If you are going to use the date when you run the update then you can put
Date()
in the UpdateTo line under the RemovedDate field - the RemovedDate will be
the current date when you run the query.

Otherwise you are going to learn another trick - a parameter query
In the NotInList Update query, in the UpdateTo line, in the RemovedDate
column, type
[What Removed Date?]

Because you don't actually have field called What Removed Date? When that
part of the macro runs, a box with the text What Removed Date? above a blank
box. Type your RemovedDate into the box.

Again, test on a copy to see if it works the way you expect it to.

Evi

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.

:

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



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

:

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




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
 
E

Evi

Coding a command button, creating a Unique Query, Using DateSerial to find
the first and last date of available months:

May I suggest that rather than using a parameter report that you create a
blank form for filtering your reports and opening them.
You can open several reports and queries from this form and it is much more
flexible than the parameter query.
Eventually, you can even put a button on this to run your importing Macro



Click on your main table. Go to Insert Query. Call it QryDateJoined.

Don't drag any fields from your table into your query. Instead, in the top
row of the first 2 blank columns type



StartDate: DateSerial(Year([DateJoined]),Month([DateJoined]),1)

EndDate: DateSerial(Year([DateJoined]),Month([DateJoined])+1,0)

(this last gives you the end date of the month)

Click in the grey area below the white Query grid.

Click Properties

If you have clicked in the correct area, you will see one line which says
Unique Values

Choose Yes next to that.



Put 2 combo boxes into your blank form. For the first one, choose only the
first column of this query (StartDate). Click on this combo, click on
Properties and the Other tab.

Name the combo cboFromDate.

for the second combo choose only the second column and name this combo
cboToDate



Add a command button to your form. Using the Wizard, choose Report
Operations, Preview Report, and your report

Click the Code Button on the toolbar of your form's design view.
you will see the code that opens your report
Just under where it says

Dim stDocName As String

type this



Dim Crit as String



If IsNull(Me.cboFromDate) Or IsNull(Me.cboToDate) Then

'if either of the combos are blank then open the report unfiltered
Crit = ""
Else
Crit = "[DateJoined]>=" & Format(Me.cboFromDate, "0") & " AND
[DateJoined]<=" & Format(Me.cboToDate, "0")
End If







Now change the line that says

DoCmd.OpenReport stDocName, acPreview



so that it now says

DoCmd.OpenReport stDocName, acPreview, , Crit


Evi






Pennington said:
In the Criteria cell of DateJoined in QryNewMembers I had put a date of the
form Between #dd/mm/yyyy# And #dd/mm/yyyy# and got a list of members who
joined between those dates. I now want it to work so that when I open the
corresponding Report I am prompted to put the month or year and I get a list
of members who joined in that month or year. I have tried the Expression
builder but can't seem to get it to work.

Evi said:
Remember, you will eventually have a list of members who have been removed
on various dates.
You can put a text box in your report header and type

=[DateRemoved]

You can combine this date with text by typing

="Members Removed By " & [DateRemoved]


It will show the DateRemoved of the first record though. This is fine if you
are then going to filter the report so that you show only one group of
removals at a time or if you sort your Dates in Descending Order in the
sorting grouping box so it shows the highest date first.

Or you can type
=Min[DateRemoved] & " to " & Max[DateRemoved]

so that you will see the lowest date on your report and the highest with the
word 'to' in the middle.

You can filter your report using a combo box in a form so you can select the
date you want to see or print. Great fun!

Or you can group your records, using the Sorting/Grouping box by this
DateRemoved and put the DateRemoved field in the Group Header so that you
can see the list of member who were grouped on 21/08/07, the group who were
removed on 20/08/06 etc


Evi



Pennington said:
It works - brilliant. Many thanks for you help
One more thing, how do I put the Date Member Removed in a Report box so it
shows in the title of the report?

I might have others later as I go through the Reports and Charts. Is it OK
if I post them here?

:

You'll have no trouble doing it, if you've done all this but just
test
it
all thoroughly on a copy of your db before letting it loose on your precious
data.

The thing the macro won't do is to do the copy /paste from Excel because
you need to check visually if the columns are in the correct order.
So, you've saved your Update & Append & Delete macros

Click on the Macro page. Click New
Under Action, in the first row, choose OpenQuery.
Next to Query Name choose the name of your first query in Step 4
from
the
dropdown list
In the next row in the Action column, choose OpenQuery again and next to
Query Name choose the name of your next query from step 5.
etc, etc
To run the macro, go to the macro page and double click it.

There are ways of doing it all without you having to OK each step
but do
try
it this way for now and read any messages which appear. There could easily
be something neither of us have thought of yet.

That RemovedDate....
If you are going to use the date when you run the update then you
can
put
Date()
in the UpdateTo line under the RemovedDate field - the RemovedDate
will
be
the current date when you run the query.

Otherwise you are going to learn another trick - a parameter query
In the NotInList Update query, in the UpdateTo line, in the RemovedDate
column, type
[What Removed Date?]

Because you don't actually have field called What Removed Date? When that
part of the macro runs, a box with the text What Removed Date? above
a
blank
box. Type your RemovedDate into the box.

Again, test on a copy to see if it works the way you expect it to.

Evi

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.

:

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



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

:

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




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
 
J

John W. Vinson

In the Criteria cell of DateJoined in QryNewMembers I had put a date of the
form Between #dd/mm/yyyy# And #dd/mm/yyyy# and got a list of members who
joined between those dates. I now want it to work so that when I open the
corresponding Report I am prompted to put the month or year and I get a list
of members who joined in that month or year. I have tried the Expression
builder but can't seem to get it to work.

Part of the problem is that literal dates in Access MUST be entered in either
American mm/dd/yyyy format, or an unambiguous format such as yyyy-mm-dd.

Try creating a little form with two combo boxes, cboYear and cboMonth. The
former would have values for year numbers of interest for your case - 1950
through 2050 to be generous; the latter would have values "All" and 1 through
12. Your criterion would be

DateJoined >= DateSerial(Forms!yourform!cboYear,
IIF([Forms]![yourform]![cboMonth] = "All",1,[Forms]![yourform]![cboMonth]), 1)
AND DateJoined < DateSerial(Forms!yourform!cboYear,
IIF([Forms]![yourform]![cboMonth] = "All",13,[Forms]![yourform]![cboMonth])+1,
1)
 
P

Pennington

I have only just got around to testing that the unmatched query works on
sequential updates and find that it overwrites any previous removed date. To
recap:
I have a members table and a members update table and step 5 is to identify
members not in the updated list. The unmatched query adds a Date Removed if
any members are not found in the Update list. I need to compare the members
and update members tables and set two conditions instead of one :
a) MemberNumber Is Null (In Update table)
b) DateRemoved Is Not Null (In Members table where member number Is Null in
Update Table)
How might I do this?



Pennington said:
In the Criteria cell of DateJoined in QryNewMembers I had put a date of the
form Between #dd/mm/yyyy# And #dd/mm/yyyy# and got a list of members who
joined between those dates. I now want it to work so that when I open the
corresponding Report I am prompted to put the month or year and I get a list
of members who joined in that month or year. I have tried the Expression
builder but can't seem to get it to work.

Evi said:
Remember, you will eventually have a list of members who have been removed
on various dates.
You can put a text box in your report header and type

=[DateRemoved]

You can combine this date with text by typing

="Members Removed By " & [DateRemoved]


It will show the DateRemoved of the first record though. This is fine if you
are then going to filter the report so that you show only one group of
removals at a time or if you sort your Dates in Descending Order in the
sorting grouping box so it shows the highest date first.

Or you can type
=Min[DateRemoved] & " to " & Max[DateRemoved]

so that you will see the lowest date on your report and the highest with the
word 'to' in the middle.

You can filter your report using a combo box in a form so you can select the
date you want to see or print. Great fun!

Or you can group your records, using the Sorting/Grouping box by this
DateRemoved and put the DateRemoved field in the Group Header so that you
can see the list of member who were grouped on 21/08/07, the group who were
removed on 20/08/06 etc


Evi



Pennington said:
It works - brilliant. Many thanks for you help
One more thing, how do I put the Date Member Removed in a Report box so it
shows in the title of the report?

I might have others later as I go through the Reports and Charts. Is it OK
if I post them here?

:

You'll have no trouble doing it, if you've done all this but just test it
all thoroughly on a copy of your db before letting it loose on your precious
data.

The thing the macro won't do is to do the copy /paste from Excel because
you need to check visually if the columns are in the correct order.
So, you've saved your Update & Append & Delete macros

Click on the Macro page. Click New
Under Action, in the first row, choose OpenQuery.
Next to Query Name choose the name of your first query in Step 4 from the
dropdown list
In the next row in the Action column, choose OpenQuery again and next to
Query Name choose the name of your next query from step 5.
etc, etc
To run the macro, go to the macro page and double click it.

There are ways of doing it all without you having to OK each step but do try
it this way for now and read any messages which appear. There could easily
be something neither of us have thought of yet.

That RemovedDate....
If you are going to use the date when you run the update then you can put
Date()
in the UpdateTo line under the RemovedDate field - the RemovedDate will be
the current date when you run the query.

Otherwise you are going to learn another trick - a parameter query
In the NotInList Update query, in the UpdateTo line, in the RemovedDate
column, type
[What Removed Date?]

Because you don't actually have field called What Removed Date? When that
part of the macro runs, a box with the text What Removed Date? above a blank
box. Type your RemovedDate into the box.

Again, test on a copy to see if it works the way you expect it to.

Evi

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.

:

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



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

:

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top