Access Macro Query

[John Thow]

LO Group.

If this isn't the right NG for this, could someone please point me at which of
the several Access NGs I should be asking? Thanks.

We have an Access membership DB with current and inactive members. What we
need to do is copy the current members to another table in the DB and export
this, plus copies of the linked tables, to an external DB.

My guess is that we should be able to do this with an Access module, but I'm
not up to speed with those.

TIA

--
John Thow
an optimist is a guy/ that has never had/ much experience -
certain maxims of archie; Don Marquis.

To e-mail me, replace the DOTs in the Reply-To: address with dots!

 

[Kevin]

We have an Access membership DB with current and inactive
members. What we

need to do is copy the current members to another table in the DB and export
this, plus copies of the linked tables, to an external DB.

My guess is that we should be able to do this with an Access module, but I'm
not up to speed with those.

John,

You can make a copy of a table by simply cutting and
pasting. Or to add only selected fields and/or selected
records, create a query, run it, and then Save As a table.

I'm curious as to why you're copying the data. Are you
planning to have redundant versions, or are you
transferring the data to another system? The former has
synchronization and data integrity issues.

In any case, please provide more detail on the target DB
system.

Best regards.
Kevin Sprinkel
Becker & Frondorf

 

[John Thow]

John,

You can make a copy of a table by simply cutting and
pasting. Or to add only selected fields and/or selected
records, create a query, run it, and then Save As a table.

I'm curious as to why you're copying the data. Are you
planning to have redundant versions, or are you
transferring the data to another system? The former has
synchronization and data integrity issues.

In any case, please provide more detail on the target DB
system.

Best regards.
Kevin Sprinkel
Becker & Frondorf

Kevin,

Thanks for the speedy response.

I can't see how to save query output as a table??? (Access 97)

The situation is that I'm the (unpaid!) membership secretary for a
small-business organisation. The (simple) database consists of 4 tables:-

BusinessType; Business; Service; Service Provided

The relationships are:-

1 Business Type to Many Businesses
1 Business to Many Services Provided
1 Service to Many Services Provided

We are putting our current members' details onto a website and our 'webmaster'
(also unpaid) wants to use the - I think- Lotus Notes-based generator which
he's comfortable with, rather than exporting Access reports as HTML. He says
he can read in an Access DB OK. What I want to give him is a copy of my DB,
but with only the current paid-up members on it. Obviously, if he's to be
able to create the website, he needs to know the business type and what
services each business offers. So, that'd be selected records from the
business table, plus the other 3 tables complete.

Thanks for looking at this.

--
John Thow
an optimist is a guy/ that has never had/ much experience -
certain maxims of archie; Don Marquis.

To e-mail me, replace the DOTs in the Reply-To: address with dots!

 

[Kevin]

So, that'd be selected records from the

business table, plus the other 3 tables complete.

Thanks for looking at this.

OK, just understand that if you're maintaining the
database two different places at the same time, they will
be out of sync. Assuming you've got that covered, and
that Access 97 cannot save a table from a query, copy your
current table (File, Save As) or Cut and Paste from the
Tables window.

Empty all the records from the new table. Now open the
query you've created that selects the records for non
deadbeat members in Design view. :^) Select Query,
Append. When prompted, enter the new empty table name to
append the records to.

Verify the fields listed in the Append To row match up.
They should, since the names are the same in both tables.
Run the query, and the selected records will be sent to
the previously empty table, which you can give to your
sacrificing co-volunteer.

Best regards.
HTH
Kevin Sprinkel
Becker & Frondorf

 

[John Thow]

So, that'd be selected records from the

OK, just understand that if you're maintaining the
database two different places at the same time, they will
be out of sync. Assuming you've got that covered,

Absolutely! That's why the original concept was for the Membership Secretary
also to be the webmaster. Unfortunately, it all got a bit much. I do have to
do some paid-for work now & again. ;-)

I've made the copy DB read-only so, at least, when my co-volunteer opens it
it tells him he can't update it. All I have to do is remember to send him a
replacement when I've received updates.

and

that Access 97 cannot save a table from a query, copy your
current table (File, Save As) or Cut and Paste from the
Tables window.

Empty all the records from the new table. Now open the
query you've created that selects the records for non
deadbeat members in Design view. :^) Select Query,
Append. When prompted, enter the new empty table name to
append the records to.

Verify the fields listed in the Append To row match up.
They should, since the names are the same in both tables.
Run the query, and the selected records will be sent to
the previously empty table, which you can give to your
sacrificing co-volunteer.

Thanks very much for your advice, Kevin. It (mostly) worked.

The only problem I had was that the primary key in the original table is an
AutoNumber. This meant it changed in the copy, so links to other tables were
lost. I overcame the problem by re-defining the primary key in the copy to
Number, so it accepts the original and it's just fine.

Thanks again.
--
John Thow
an optimist is a guy/ that has never had/ much experience -
certain maxims of archie; Don Marquis.

To e-mail me, replace the DOTs in the Reply-To: address with dots!