Want to export some but not all records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to export select table records from database1 to another database's
table (database2). I have designed a select query in database1 to find the
desired records. I have created a macro in database1 with the intent of
exporting the query results to a table in database2 but when I run the macro
I get the message that my TABLE in database1 can't be deleted due to
relationships it is participating in. I don't want to delete the table, just
some of the records. Here are the parameters I have set in the macro...

Action TransferDatabase

Transfer Type Export
Database Type Microsoft Access
Database Name full path including extension of the database2 (where I want
to export to)
Object Type Query (got the same error message when I had this set to
Table)
Source name of my select query in database1
Destination Name of table in database2 where I want to export the
records
Structure Only No

Another thought crosses my mind; should I attempt to do this through VB
code? If so, do you have code you could share?
 
It would be better if you link to the table in database2, and then run an
append query to copy data from the query in database1 to the table in
database2.
 
Great idea! Would the append query delete the records in database1 or would
I also have to follow the append query with a delete query to accomplish this?
 
You would need to run a delete query after the append query in order to
delete the records in database1.
 
Ken, I'm having trouble with the delete query. When I view the results of
the query I see the records I want to delete but when I run it I get an error
message that states I need to specify the table containing the records I want
to delete. Here is the SQL on the delete query.

DELETE S.[Silv Primary Key], S.[Completion Date of Exam or Activity],
S.[Target Date], S.Type, S.[Silv Issue or Forest Health], S.[Planned
Activity], S.Comments, S.Completed, S.CompDate, S.FreeToGrow
FROM S INNER JOIN qFreeToGrow_Silv_Records ON S.Type =
qFreeToGrow_Silv_Records.Type
WHERE (((S.[S Primary Key])=[qFreeToGrow_Silv_Records].[S Primary Key]));

I first started with a select query named qFreeToGrow which queried the
"Free" field in the S table and returned 19 records.

Then I created a second query named qFreeToGrow_Silv_Records that takes the
values returned in the "Type" field of the first query and searches for all
records in the S table with that "Type". It returns the 137 records I want
to export and then delete.

The delete query is based on an inner join between the second query
(qFreeToGrow_Silv_Records) and the S table.

--
Lorraine


Ken Snell said:
This is one title that I've not been called before..... :-)

You're welcome.
Click to expand...
 
Delete queries often can be picky when you use a join between tables.

Try this variation for your SQL statement - it might work:

DELETE DISTICNT S.[Silv Primary Key], S.[Completion Date of Exam or
Activity],
S.[Target Date], S.Type, S.[Silv Issue or Forest Health], S.[Planned
Activity], S.Comments, S.Completed, S.CompDate, S.FreeToGrow
FROM S INNER JOIN qFreeToGrow_Silv_Records ON S.Type =
qFreeToGrow_Silv_Records.Type
WHERE (((S.[S Primary Key])=[qFreeToGrow_Silv_Records].[S Primary Key]));


If that doesn't work, then this revised query should do the trick (note: not
tested, obviously):

DELETE S.[Silv Primary Key], S.[Completion Date of Exam or Activity],
S.[Target Date], S.Type, S.[Silv Issue or Forest Health], S.[Planned
Activity], S.Comments, S.Completed, S.CompDate, S.FreeToGrow
FROM S
WHERE S.[S Primary Key] IN
(SELECT [qFreeToGrow_Silv_Records].[S Primary Key]
FROM [qFreeToGrow_Silv_Records]
WHERE [qFreeToGrow_Silv_Records].[Type] = S.Type);


--

Ken Snell
<MS ACCESS MVP>

Lorraine said:
Ken, I'm having trouble with the delete query. When I view the results of
the query I see the records I want to delete but when I run it I get an
error
message that states I need to specify the table containing the records I
want
to delete. Here is the SQL on the delete query.

DELETE S.[Silv Primary Key], S.[Completion Date of Exam or Activity],
S.[Target Date], S.Type, S.[Silv Issue or Forest Health], S.[Planned
Activity], S.Comments, S.Completed, S.CompDate, S.FreeToGrow
FROM S INNER JOIN qFreeToGrow_Silv_Records ON S.Type =
qFreeToGrow_Silv_Records.Type
WHERE (((S.[S Primary Key])=[qFreeToGrow_Silv_Records].[S Primary Key]));

I first started with a select query named qFreeToGrow which queried the
"Free" field in the S table and returned 19 records.

Then I created a second query named qFreeToGrow_Silv_Records that takes
the
values returned in the "Type" field of the first query and searches for
all
records in the S table with that "Type". It returns the 137 records I
want
to export and then delete.

The delete query is based on an inner join between the second query
(qFreeToGrow_Silv_Records) and the S table.
Click to expand...
 
Back
Top