Re: Membership database updates

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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" <Pennington@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:844AA98D-12B1-4D0A-A387-00E12A51AE49@xxxxxxxxxxxxxxxx
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" <Pennington@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4CB0F97B-1E54-4378-BF79-B00287587F20@xxxxxxxxxxxxxxxx
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?






.

Relevant Pages

  • Re: Membership database updates
    ... of members who joined in that month or year. ... You can put a text box in your report header and type ... Next to Query Name choose the name of your first query in Step 4 from ... in the UpdateTo line under the RemovedDate field - the RemovedDate will ...
    (microsoft.public.access.tablesdbdesign)
  • RE: Duplicate entries in a query
    ... An Access report rather than a Word document is the simplest method. ... members who have the same name, but still order the report by name. ... You can then call the function in a query such as this: ... Paul H, address, phone, 55 Olds ...
    (microsoft.public.access.queries)
  • Re: Membership database updates
    ... you will eventually have a list of members who have been removed ... You can put a text box in your report header and type ... Next to Query Name choose the name of your first query in Step 4 from ... in the UpdateTo line under the RemovedDate field - the RemovedDate will ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Membership database updates
    ... I have only just got around to testing that the unmatched query works on ... I have a members table and a members update table and step 5 is to identify ... You can put a text box in your report header and type ... in the UpdateTo line under the RemovedDate field - the RemovedDate will ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Using one report for different queries
    ... the other queries you made are All ... Members query but with some criteria done to it to get a smaller list. ... Now when the report is opened, ...
    (comp.databases.ms-access)