Appending Only New Data to a current Table

G

GLT

Hi,

I have a query that appends data to a table. This works fine however I only
want new records that have been collected to be appended - at the moment it
keeps appending thousands of records every time the query is run.

I do remember reading something about this but I have lost documentation on
it.

If anyone could point me in the right direction I would be most grateful.

Cheers,
GLT.
 
J

John Spencer

You might consider posting the SQL of the append query (MENU View SQL).

Also, tell us how one identifies NEW records in the source. By date range,
by matching fields between the source table and the destination table.


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
G

GLT

Hi John,

Thanks for your reply:

The SQL is this:

INSERT INTO [Backup Problems - Failed Work Table] ( Received, Status,
Client, Server, JobID, Backup )
SELECT [Backup Problems].Received, [Backup Problems].Status, [Backup
Problems].Client, [Backup Problems].Server, [Backup Problems].JobID, [Backup
Problems].Backup
FROM [Backup Problems]
WHERE ((([Backup Problems].Status) Like "FAILED"));

I would like to use the 'Received' and 'Server' feilds to identify unique
records.

The received feild is the date / time.

Cheers,
GLT.
 
J

John Spencer

I would try the following.

INSERT INTO [Backup Problems - Failed Work Table] ( Received, Status,
Client, Server, JobID, Backup )
SELECT [Backup Problems].Received, [Backup Problems].Status, [Backup
Problems].Client, [Backup Problems].Server, [Backup Problems].JobID, [Backup
Problems].Backup
FROM [Backup Problems] LEFT JOIN [Backup Problems - Failed Work Table]
ON [Backup Problems].Received = [Backup Problems - Failed Work
Table].Received
AND [Backup Problems].Server= [Backup Problems - Failed Work Table].Server
WHERE [Backup Problems].Status = "FAILED" AND
[Backup Problems - Failed Work Table].Server Is Null


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

GLT said:
Hi John,

Thanks for your reply:

The SQL is this:

INSERT INTO [Backup Problems - Failed Work Table] ( Received, Status,
Client, Server, JobID, Backup )
SELECT [Backup Problems].Received, [Backup Problems].Status, [Backup
Problems].Client, [Backup Problems].Server, [Backup Problems].JobID,
[Backup
Problems].Backup
FROM [Backup Problems]
WHERE ((([Backup Problems].Status) Like "FAILED"));

I would like to use the 'Received' and 'Server' feilds to identify unique
records.

The received feild is the date / time.

Cheers,
GLT.

John Spencer said:
You might consider posting the SQL of the append query (MENU View SQL).

Also, tell us how one identifies NEW records in the source. By date
range,
by matching fields between the source table and the destination table.


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
Click to expand...
Click to expand...
 
G

GLT

Hi John,

I tried the statement that you provided but it reports an SQL error (syntax
error in JOIN statement)...

Is the 'LEFT' meant to be before the word 'JOIN'?

Cheers,
GLT

John Spencer said:
I would try the following.

INSERT INTO [Backup Problems - Failed Work Table] ( Received, Status,
Client, Server, JobID, Backup )
SELECT [Backup Problems].Received, [Backup Problems].Status, [Backup
Problems].Client, [Backup Problems].Server, [Backup Problems].JobID, [Backup
Problems].Backup
FROM [Backup Problems] LEFT JOIN [Backup Problems - Failed Work Table]
ON [Backup Problems].Received = [Backup Problems - Failed Work
Table].Received
AND [Backup Problems].Server= [Backup Problems - Failed Work Table].Server
WHERE [Backup Problems].Status = "FAILED" AND
[Backup Problems - Failed Work Table].Server Is Null


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

GLT said:
Hi John,

Thanks for your reply:

The SQL is this:

INSERT INTO [Backup Problems - Failed Work Table] ( Received, Status,
Client, Server, JobID, Backup )
SELECT [Backup Problems].Received, [Backup Problems].Status, [Backup
Problems].Client, [Backup Problems].Server, [Backup Problems].JobID,
[Backup
Problems].Backup
FROM [Backup Problems]
WHERE ((([Backup Problems].Status) Like "FAILED"));

I would like to use the 'Received' and 'Server' feilds to identify unique
records.

The received feild is the date / time.

Cheers,
GLT.

John Spencer said:
You might consider posting the SQL of the append query (MENU View SQL).

Also, tell us how one identifies NEW records in the source. By date
range,
by matching fields between the source table and the destination table.


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi,

I have a query that appends data to a table. This works fine however I
only
want new records that have been collected to be appended - at the
moment
it
keeps appending thousands of records every time the query is run.

I do remember reading something about this but I have lost
documentation
on
it.

If anyone could point me in the right direction I would be most
grateful.

Cheers,
GLT.
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Probasbly a problem in line wrapping. Try the following.

INSERT INTO [Backup Problems - Failed Work Table] ( Received, Status,
Client, Server, JobID, Backup )
SELECT [Backup Problems].Received, [Backup Problems].Status
, [Backup Problems].Client, [Backup Problems].Server
, [Backup Problems].JobID, [Backup Problems].Backup
FROM [Backup Problems] LEFT JOIN
[Backup Problems - Failed Work Table]
ON [Backup Problems].Received =
[Backup Problems - Failed Work Table].Received
AND [Backup Problems].Server=
[Backup Problems - Failed Work Table].Server
WHERE [Backup Problems].Status = "FAILED" AND
[Backup Problems - Failed Work Table].Server Is Null

If that still fails, then I am stuck.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hi John,

I tried the statement that you provided but it reports an SQL error (syntax
error in JOIN statement)...

Is the 'LEFT' meant to be before the word 'JOIN'?

Cheers,
GLT

John Spencer said:
I would try the following.

INSERT INTO [Backup Problems - Failed Work Table] ( Received, Status,
Client, Server, JobID, Backup )
SELECT [Backup Problems].Received, [Backup Problems].Status, [Backup
Problems].Client, [Backup Problems].Server, [Backup Problems].JobID, [Backup
Problems].Backup
FROM [Backup Problems] LEFT JOIN [Backup Problems - Failed Work Table]
ON [Backup Problems].Received = [Backup Problems - Failed Work
Table].Received
AND [Backup Problems].Server= [Backup Problems - Failed Work Table].Server
WHERE [Backup Problems].Status = "FAILED" AND
[Backup Problems - Failed Work Table].Server Is Null


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

GLT said:
Hi John,

Thanks for your reply:

The SQL is this:

INSERT INTO [Backup Problems - Failed Work Table] ( Received, Status,
Client, Server, JobID, Backup )
SELECT [Backup Problems].Received, [Backup Problems].Status, [Backup
Problems].Client, [Backup Problems].Server, [Backup Problems].JobID,
[Backup
Problems].Backup
FROM [Backup Problems]
WHERE ((([Backup Problems].Status) Like "FAILED"));

I would like to use the 'Received' and 'Server' feilds to identify unique
records.

The received feild is the date / time.

Cheers,
GLT.

:

You might consider posting the SQL of the append query (MENU View SQL).

Also, tell us how one identifies NEW records in the source. By date
range,
by matching fields between the source table and the destination table.


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi,

I have a query that appends data to a table. This works fine however I
only
want new records that have been collected to be appended - at the
moment
it
keeps appending thousands of records every time the query is run.

I do remember reading something about this but I have lost
documentation
on
it.

If anyone could point me in the right direction I would be most
grateful.

Cheers,
GLT.
Click to expand...
Click to expand...
Click to expand...
 
G

GLT

Hi John,

Works a treat - thankyou for your assistance.

I also viewed the SQL in the QBE grid so now I can visullay see how to set
this up.

Thanks again,
GLT.

John Spencer said:
Probasbly a problem in line wrapping. Try the following.

INSERT INTO [Backup Problems - Failed Work Table] ( Received, Status,
Client, Server, JobID, Backup )
SELECT [Backup Problems].Received, [Backup Problems].Status
, [Backup Problems].Client, [Backup Problems].Server
, [Backup Problems].JobID, [Backup Problems].Backup
FROM [Backup Problems] LEFT JOIN
[Backup Problems - Failed Work Table]
ON [Backup Problems].Received =
[Backup Problems - Failed Work Table].Received
AND [Backup Problems].Server=
[Backup Problems - Failed Work Table].Server
WHERE [Backup Problems].Status = "FAILED" AND
[Backup Problems - Failed Work Table].Server Is Null

If that still fails, then I am stuck.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hi John,

I tried the statement that you provided but it reports an SQL error (syntax
error in JOIN statement)...

Is the 'LEFT' meant to be before the word 'JOIN'?

Cheers,
GLT

John Spencer said:
I would try the following.

INSERT INTO [Backup Problems - Failed Work Table] ( Received, Status,
Client, Server, JobID, Backup )
SELECT [Backup Problems].Received, [Backup Problems].Status, [Backup
Problems].Client, [Backup Problems].Server, [Backup Problems].JobID, [Backup
Problems].Backup
FROM [Backup Problems] LEFT JOIN [Backup Problems - Failed Work Table]
ON [Backup Problems].Received = [Backup Problems - Failed Work
Table].Received
AND [Backup Problems].Server= [Backup Problems - Failed Work Table].Server
WHERE [Backup Problems].Status = "FAILED" AND
[Backup Problems - Failed Work Table].Server Is Null


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi John,

Thanks for your reply:

The SQL is this:

INSERT INTO [Backup Problems - Failed Work Table] ( Received, Status,
Client, Server, JobID, Backup )
SELECT [Backup Problems].Received, [Backup Problems].Status, [Backup
Problems].Client, [Backup Problems].Server, [Backup Problems].JobID,
[Backup
Problems].Backup
FROM [Backup Problems]
WHERE ((([Backup Problems].Status) Like "FAILED"));

I would like to use the 'Received' and 'Server' feilds to identify unique
records.

The received feild is the date / time.

Cheers,
GLT.

:

You might consider posting the SQL of the append query (MENU View SQL).

Also, tell us how one identifies NEW records in the source. By date
range,
by matching fields between the source table and the destination table.


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi,

I have a query that appends data to a table. This works fine however I
only
want new records that have been collected to be appended - at the
moment
it
keeps appending thousands of records every time the query is run.

I do remember reading something about this but I have lost
documentation
on
it.

If anyone could point me in the right direction I would be most
grateful.

Cheers,
GLT.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

check while appending data 3
INCREMENTAL NBR VS. AUTONUMBER WHEN APPENDING 2
Appending to a data record 1
conditional appending 1
conditional appending through append query 1
append query question in Access97 1
Append only new records to a table 2
Large Union Queries and Appending the data 1

Top