Add records from one table to another table in Access

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

Guest

Without going into too much details, I have a number of tables. I wish to add
records from the clients table to a snag list table. I originally used the
append method which set up the table but as snags are created daily these
need to be logged, can anyone help.
 
You normally use an append query to copy data from one table to another.
You'll need to give us more details about what you mean concerning logging
and why an append query won't work.
 
Thanks Ken
As already mentioned, I used an append query to originally set up this
table. If I use the append query again it will duplicate entries that are
already in this table.

I need a query where a certain condition is true then add the new records to
this table as a snag list. These records are new to the clients table and
have conditions to meet which are placed in other tables. I really don't know
how to explain it ant better.
 
You can still use an append query if you put in a WHERE clause that selects
only the records that you want to append. As you've not given us any details
about the data, the source of the data, the process that is running the
append query, where the append query gets its data, etc., I cannot offer
much more suggestion than this. If you can give us more info, including the
append query's SQL statement, we may be able to provide more detailed
suggestions.
--

Ken Snell
<MS ACCESS MVP>
 
Suppose you have two tables: tblSource and tblSnag, with tblSnag being
the one that you are appending to. Suppose there is field that
uniquely identifies records in tblSource, and that it is also in
tblSnag.

Then, in the query that appends your records, you can add tbleSnag,
and relate it via the unique linking field. Right-click on the line
that joins the two tables, and change it to "include all records from
tblSource". Then apply a criterion to the linking field IN TBLSNAG,
and look for records where the value is NULL. These are the records
that exist in your tblSource but do not exist in your tblSnag.

Once you have identified those records, you can proceed with your
append query.



Thanks Ken
As already mentioned, I used an append query to originally set up this
table. If I use the append query again it will duplicate entries that are
already in this table.

I need a query where a certain condition is true then add the new records to
this table as a snag list. These records are new to the clients table and
have conditions to meet which are placed in other tables. I really don't know
how to explain it ant better.
Click to expand...


**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security
 
Again Thanks Ken

I will try my best in trying to give a fuller process. I think

I have a table which contains clients records, this is the parent table
where the others tables relate, in the table, contains reference's, name's
addresses etc, and certain information that needs to be actioned as soon as
possible. Unfortunately the database is in the process of being built. There
are times, where I am asked question about certain info, in this case I try a
simple query to give answers to those questions in access itself.

Some records are instantly moved to another table to be processed seperately
to the others due to a different process is needed.

These records are highlighted at the update of the parent table, where
actioned by another team. Lets try an example:

If we receive 400 records and are added to the Parent (Clients) there will
be say 40 of them with a certain field with the value zero that needs special
attention before being returned to be processed. Hence the reason for the
question.
 
So it sounds as if you can use that field that contains a zero value to
filter out and select the records that need to be appended? Your explanation
helps, but I still don't have enough information to know what happens to the
records once you "copy" them to the other table. Do they stay there forever?
Do you remove them once their actions have been done?

You'll need to show some specific examples of the actual data in the tables,
and show what you start with and what you should end up with, to allow us to
be more helpful.

--

Ken Snell
<MS ACCESS MVP>
 
Thanks Ken and Jack

INSERT INTO [Drainage Issues] ( [Batch Ref] )
SELECT Clients.[Batch Ref]
FROM Clients LEFT JOIN [Drainage Issues] ON Clients.[Batch Ref] = [Drainage
Issues].[Batch Ref]
WHERE (((Clients.[P/Area])="No Band or Lower"));

This append query gives me all the data required, however this duplicates
the data that already exists in the table Drainage Issues. When really I just
require the data that does not exists in this table.

I must say I not up with Sql at all and its been 10 years since developing
databases

Kindest Regards
 
Ken and Jack thanks very much for your help.

But I have found the solution to my query.

INSERT INTO [Drainage Issues] ( [Batch Ref] )
SELECT Clients.[Batch Ref]
FROM Clients LEFT JOIN [Drainage Issues] ON Clients.[Batch Ref] = [Drainage
Issues].[Batch Ref]
WHERE ((([Drainage Issues]![Batch Ref]) Is Null) AND ((Clients.[P/Area])="No
band or lower"));

Again thanks to you both and your time

Kindest regards
Dukky poos


Duckula said:
Thanks Ken and Jack

INSERT INTO [Drainage Issues] ( [Batch Ref] )
SELECT Clients.[Batch Ref]
FROM Clients LEFT JOIN [Drainage Issues] ON Clients.[Batch Ref] = [Drainage
Issues].[Batch Ref]
WHERE (((Clients.[P/Area])="No Band or Lower"));

This append query gives me all the data required, however this duplicates
the data that already exists in the table Drainage Issues. When really I just
require the data that does not exists in this table.

I must say I not up with Sql at all and its been 10 years since developing
databases

Kindest Regards



Jack MacDonald said:
Suppose you have two tables: tblSource and tblSnag, with tblSnag being
the one that you are appending to. Suppose there is field that
uniquely identifies records in tblSource, and that it is also in
tblSnag.

Then, in the query that appends your records, you can add tbleSnag,
and relate it via the unique linking field. Right-click on the line
that joins the two tables, and change it to "include all records from
tblSource". Then apply a criterion to the linking field IN TBLSNAG,
and look for records where the value is NULL. These are the records
that exist in your tblSource but do not exist in your tblSnag.

Once you have identified those records, you can proceed with your
append query.






**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security
Click to expand...
Click to expand...
 
Back
Top