A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Membership database updates



 
 
Thread Tools Display Modes
  #11  
Old March 21st, 2008, 12:11 AM posted to microsoft.public.access.tablesdbdesign
Evi
external usenet poster
 
Posts: 898
Default Membership database updates

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

easier
way of
doing this like running a find/replace query?









  #12  
Old March 21st, 2008, 01:33 AM posted to microsoft.public.access.tablesdbdesign
Pennington
external usenet poster
 
Posts: 21
Default Membership database updates

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

easier
way of
doing this like running a find/replace query?










  #13  
Old March 21st, 2008, 09:24 AM posted to microsoft.public.access.tablesdbdesign
Evi
external usenet poster
 
Posts: 898
Default Membership database updates

Phew, you outlasted me Pennington.
Update and Append queries:
When you open any Query in Design View, click on the toolbar just next to
the Query Type button (it looks like 2 rectangles in 'cascade' view and says
Query type on the Tooltip when you hover the cursor over it). There is a
black down-arrow where you can change your query to an Append or Update
query (plus a few useful others). If you don't see those choices, click the
double arrow at the bottom of your list to see the full list.
No need to learn Sql to create them.
I'm guessing that if you look at this fresh in the morning you will kick
yourself copiously for not having thought of it but having been 'raised' on
Win95 I too frequently forget about the darn double arrow.
This list will give you the choice of an Update or Append Query.
I thought you were already using an Append to put your imported Excel data
into your Access table otherwise I would have said so but now I see what is
happening. You are importing your Excel table, naming that as your current
table and renaming your old one.
No more, Pennington, no more! You will lose all your Default Values and
clever stuff if you do that.

To Append your data (ONLY once you have edited your Current table to get rid
of Ex members by updating the DateRemoved column with an update query)
Click on your Imported table while it is closed.

Go to Insert, choose query, choose Design View. Drag all the fields of your
imported table into the query grid. Use your Query Type button to change the
query to an Append and choose to append to your Current Table.
In the Append To row of the query, choose the fields to which you want to
add each column of data. Click the red Exclamation Mark to run the query.You
will be warned that you are adding x rows to your table. When you click Yes,
it will add. If you have any current members in your Import list, they won't
append and you will get a confusing message saying that Access didn't add x
rows because of Key violation. If you try to append your member's name to
your date field or something like that, then you will get the message that x
rows (all of them) weren't added because of Data Type violations.

I recommend that if you aren't used to using them that you make a copy of
your database (copy and paste in Windows Explorer) and practice updates and
appends there until you get used to them.
Evi






"Pennington" wrote in message
...
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

easier
way of
doing this like running a find/replace query?












  #14  
Old March 21st, 2008, 09:49 AM posted to microsoft.public.access.tablesdbdesign
Evi
external usenet poster
 
Posts: 898
Default Membership database updates

Can I also suggest that you add a new field to your Current Table. Call it
AppendDate. It will be there for auditing only and you won't need to put
anything in it yourself or see it in any of your forms or reports.
Make its Default Value :
Now()
and change the Format to General Date so that it automatically records the
date and time when a new record is added. Then, if you accidentally append
something you didn't mean to append, you can use this field to find what it
was.
Evi

"Evi" wrote in message
...
Phew, you outlasted me Pennington.
Update and Append queries:
When you open any Query in Design View, click on the toolbar just next to
the Query Type button (it looks like 2 rectangles in 'cascade' view and

says
Query type on the Tooltip when you hover the cursor over it). There is a
black down-arrow where you can change your query to an Append or Update
query (plus a few useful others). If you don't see those choices, click

the
double arrow at the bottom of your list to see the full list.
No need to learn Sql to create them.
I'm guessing that if you look at this fresh in the morning you will kick
yourself copiously for not having thought of it but having been 'raised'

on
Win95 I too frequently forget about the darn double arrow.
This list will give you the choice of an Update or Append Query.
I thought you were already using an Append to put your imported Excel data
into your Access table otherwise I would have said so but now I see what

is
happening. You are importing your Excel table, naming that as your current
table and renaming your old one.
No more, Pennington, no more! You will lose all your Default Values and
clever stuff if you do that.

To Append your data (ONLY once you have edited your Current table to get

rid
of Ex members by updating the DateRemoved column with an update query)
Click on your Imported table while it is closed.

Go to Insert, choose query, choose Design View. Drag all the fields of

your
imported table into the query grid. Use your Query Type button to change

the
query to an Append and choose to append to your Current Table.
In the Append To row of the query, choose the fields to which you want to
add each column of data. Click the red Exclamation Mark to run the

query.You
will be warned that you are adding x rows to your table. When you click

Yes,
it will add. If you have any current members in your Import list, they

won't
append and you will get a confusing message saying that Access didn't add

x
rows because of Key violation. If you try to append your member's name to
your date field or something like that, then you will get the message that

x
rows (all of them) weren't added because of Data Type violations.

I recommend that if you aren't used to using them that you make a copy of
your database (copy and paste in Windows Explorer) and practice updates

and
appends there until you get used to them.
Evi






"Pennington" wrote in message
...
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
easier
way of
doing this like running a find/replace query?














  #15  
Old March 21st, 2008, 09:58 AM posted to microsoft.public.access.tablesdbdesign
Pennington
external usenet poster
 
Posts: 21
Default Membership database updates

Good morning Evi. Well whatever you might think I should see on my screen, I
hate to tell you that I can't find the Query Type button and I have hovered
over every button, even added the Query Design Toolbar and no luck - I would
send you a screen shot if I knew how to attach it to this post.

"Evi" wrote:

Phew, you outlasted me Pennington.
Update and Append queries:
When you open any Query in Design View, click on the toolbar just next to
the Query Type button (it looks like 2 rectangles in 'cascade' view and says
Query type on the Tooltip when you hover the cursor over it). There is a
black down-arrow where you can change your query to an Append or Update
query (plus a few useful others). If you don't see those choices, click the
double arrow at the bottom of your list to see the full list.
No need to learn Sql to create them.
I'm guessing that if you look at this fresh in the morning you will kick
yourself copiously for not having thought of it but having been 'raised' on
Win95 I too frequently forget about the darn double arrow.
This list will give you the choice of an Update or Append Query.
I thought you were already using an Append to put your imported Excel data
into your Access table otherwise I would have said so but now I see what is
happening. You are importing your Excel table, naming that as your current
table and renaming your old one.
No more, Pennington, no more! You will lose all your Default Values and
clever stuff if you do that.

To Append your data (ONLY once you have edited your Current table to get rid
of Ex members by updating the DateRemoved column with an update query)
Click on your Imported table while it is closed.

Go to Insert, choose query, choose Design View. Drag all the fields of your
imported table into the query grid. Use your Query Type button to change the
query to an Append and choose to append to your Current Table.
In the Append To row of the query, choose the fields to which you want to
add each column of data. Click the red Exclamation Mark to run the query.You
will be warned that you are adding x rows to your table. When you click Yes,
it will add. If you have any current members in your Import list, they won't
append and you will get a confusing message saying that Access didn't add x
rows because of Key violation. If you try to append your member's name to
your date field or something like that, then you will get the message that x
rows (all of them) weren't added because of Data Type violations.

I recommend that if you aren't used to using them that you make a copy of
your database (copy and paste in Windows Explorer) and practice updates and
appends there until you get used to them.
Evi






"Pennington" wrote in message
...
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

  #16  
Old March 21st, 2008, 10:07 AM posted to microsoft.public.access.tablesdbdesign
Pennington
external usenet poster
 
Posts: 21
Default Membership database updates

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

easier
way of
doing this like running a find/replace query?


  #17  
Old March 21st, 2008, 06:40 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default Membership database updates

Couple of notes from the sidelines:

- Another way to get it is via the "query" dropdown menu when in design mode
in a query


- Sounds to me like your HQ is actually maintaining the membership database.
You are receiving and using copies of it. Can you just import and directly
use the whole thing that they send you each time?
  #18  
Old March 21st, 2008, 07:19 PM posted to microsoft.public.access.tablesdbdesign
Pennington
external usenet poster
 
Posts: 21
Default Membership database updates

I have now run Append and Update queries on a copy of the database and I am
now in the process of appending records for updates.

I only get a copy of the database filtered for my branch. I think I said
previously that in the lists HQ send me, they don't include those who have
left. I am trying to get them to denote deaths, resignations and suspensions
as I don't want to send emails to dead people pursuading them to rejoin. So I
need to compare current and updated lists to identify the leavers.

Thanks for your help. I'll be back when I have suceeded or failed.

"Fred" wrote:

Couple of notes from the sidelines:

- Another way to get it is via the "query" dropdown menu when in design mode
in a query


- Sounds to me like your HQ is actually maintaining the membership database.
You are receiving and using copies of it. Can you just import and directly
use the whole thing that they send you each time?

  #19  
Old March 21st, 2008, 08:21 PM posted to microsoft.public.access.tablesdbdesign
Pennington
external usenet poster
 
Posts: 21
Default Membership database updates

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

  #20  
Old March 21st, 2008, 11:24 PM posted to microsoft.public.access.tablesdbdesign
Evi
external usenet poster
 
Posts: 898
Default Membership database updates

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


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:03 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.