Re: Membership database updates

I don't mind bluntness but I did explain that the list I am handling is for a
local branch of a charity. The main database is in HQ and they send me a list
of members in my branch. HQ can do all sorts of things with the database but
the branch needs to understand its members so that is why I want to look at
local trends. I was able to create all the necessary Reports and Charts but
the difficulty I have is when I receive an update from HQ. that omits
ex-members but may have address changes and other changes that means I cannot
use the members table I have currently.

Perhaps what I need to do is
a) import the update into a new table and name it NewMembers,
b) run the "unmatched query wizard" with the CurrentMembers table to
identify the ex-members ,
c) check the MembersRemoved field and delete the rest
d) merge this list with the NewMembers table
e) delete the CurrentMembers table and rename the NewMembers table as
CurrentMembers so that the Reports and Charts work.

Incidentally, when I rename a table does it automatically update all
references to that table because I tried this and it didn't seem to work.

If you can help me write a query to do this in less steps it would be
brilliant.

"Fred" wrote:

If you don't mind bluntness that only intended to try to be helpful.

I run membership data / dabases for 6 organizations, 2 of them for over 20
years.

You never explained the key points, but they are finally starting to come
out.

Now it looks like there is something fundamentally wrong with what's
happening. It sounds your organization is running two databases that are,
doing the same thing. And they are sending you "updates" in the worst
possible way (just the whole list, with no useful fields about additions /
deletions) and then you are trying to go through contortions trying to deal
with this fundamentally mixed up situations. So, your efforts to deal with
that mess are a good tough workout for learning Access, but is much more
difficult that it should be.

The best thing would be for your organization to decide who is running the
membership database, and for that function there should only be one database.
All CHANGES should then get entered in THAT database. And all data and
reports that people need should come from that database.

Sincerley,

Fred


"Pennington" wrote:

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

.

Relevant Pages

  • Re: A little more meat this week
    ... said data includes lists." ... implementation with a query processor capable of returning a result set ... then we can call it a result bag (SQL ... I'm looking at the data model and not database tools at this ...
    (comp.databases.pick)
  • Re: Append Query Problem
    ... Completed all steps including importing into a blank database. ... It is not an attached table, linked to a back end that is missing. ... If you are still stuck, see if this query returns your linklist table: ... there are 2 internal canonical lists of tables. ...
    (microsoft.public.access.queries)
  • Re: Data sources for different mailmerge letters
    ... try the following (I'm not going to go along the Query route ... In another database, I have Test B with "Verbal Comprehension" field, ... If you have two OALs (Office address lists) with identical data fields, ...
    (microsoft.public.word.mailmerge.fields)
  • Re: qeury other list using input from selected item in current list
    ... With SPQuery you can query a list based on a CAML (Collaborative Application ... query" objects from different lists or objects in your site collection. ... the query and the database which the query ...
    (microsoft.public.sharepoint.windowsservices)
  • Re: Append Query Problem
    ... Completed all steps including importing into a blank database. ... > in the append query drop down. ... see if this query returns your linklist>> table: ... >> In Access 2000 and later, there are 2 internal canonical lists of>> tables. ...
    (microsoft.public.access.queries)