Append new records

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

D - 27 Jan 2006 20:12 GMT

Hi:

I would like to append to a table just the new records;
So if today I appended 1 and 2; and tomorrow the result of the query is 1,2
and 3, I want to append just the 3 one.

How do I do that?

Thanks,

Dan

Reply to this Message

Rick B - 27 Jan 2006 20:17 GMT

Huh? What are you appending from and to?

Why are you copying records to another table? Normally you would not want
that redundancy.

To answer your question, your append query should only pull (and thus
append) the new records. If you do this daily, then use a date added field
in the record to only pull records that apply. But, again, you most likely
should not be doing this.

Signature

Rick B

> Hi:
>
[quoted text clipped - 8 lines]
>
> Dan

Reply to this Message

Lynn Trapp - 27 Jan 2006 20:19 GMT

I'm afraid I'm at a loss as to what you want. The only kind of record you
can ever append to a table is a new record. You can update old records, but
can only append new ones.

Signature

Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

> Hi:
>
[quoted text clipped - 8 lines]
>
> Dan

Reply to this Message

D - 27 Jan 2006 20:28 GMT

Hi Lynn:

The old records are already there: ID(S) 1 and 2 are in the table that I am
appending to. Later, the result of the qry is 1,2 and 3=new; I want to append
just 3.

You can do that by making the ID (no duplicates) but I would not do that.

Thanks,

Dan

> I'm afraid I'm at a loss as to what you want. The only kind of record you
> can ever append to a table is a new record. You can update old records, but
[quoted text clipped - 12 lines]
> >
> > Dan

Reply to this Message

Rick B - 27 Jan 2006 20:33 GMT

Then only pull item 3 to your query.

Or do an unmatched query to find which ones are missing.

BUT, you either have a one-to-one relationship (each table has one record
for each control) or you are duplicating data. If you have a one-to-one
relationship, then you don't need to jump through hops and use a query, just
build your relationship.

Signature

Rick B

> Hi Lynn:
>
[quoted text clipped - 27 lines]
>> >
>> > Dan

Reply to this Message

Lynn Trapp - 27 Jan 2006 20:35 GMT

So why don't you explain to me how you are trying to append 3? Are you using
an append query?

Why do you not want to set the ID to no duplicates? If it is the primary key
of your table it cannot be any other way.

Signature

> Hi Lynn:
>
[quoted text clipped - 27 lines]
>> >
>> > Dan

Reply to this Message

D - 27 Jan 2006 20:45 GMT

Yes; please see below:

INSERT INTO [DQ Issues] ( StoreNo )
SELECT [Mac+Nots].[Natl#]
FROM [Mac+Nots] LEFT JOIN [DQ Issues] ON [Mac+Nots].[Natl#] = [DQ
Issues].NatlStoreNo
WHERE ((([DQ Issues].NatlStoreNo) Is Null));

Thanks,

Dan

> So why don't you explain to me how you are trying to append 3? Are you using
> an append query?
[quoted text clipped - 33 lines]
> >> >
> >> > Dan

Reply to this Message

John Spencer - 27 Jan 2006 20:36 GMT

Assuming
TableA as the Source table
TableB as table being appended
A way to link the two tables (primary key same in both)

INSERT INTO TableB (FieldA, FieldB, FieldC)
SELECT A, B, C
FROM TableA LEFT JOIN Table B
ON TableA.PrimaryKey = TableB.PrimaryKey
WHERE TableB.PrimaryKey is Null

> Hi:
>
[quoted text clipped - 8 lines]
>
> Dan

Reply to this Message

D - 27 Jan 2006 20:47 GMT

Thanks John!

I am doing the same; but is this a "bullet proof"; are there any other ways?

Thanks,

Dan

INSERT INTO [DQ Issues] ( StoreNo )
SELECT [Mac+Nots].[Natl#]
FROM [Mac+Nots] LEFT JOIN [DQ Issues] ON [Mac+Nots].[Natl#] = [DQ
Issues].NatlStoreNo
WHERE ((([DQ Issues].NatlStoreNo) Is Null));

> Assuming
> TableA as the Source table
[quoted text clipped - 19 lines]
> >
> > Dan

Reply to this Message

John Spencer - 27 Jan 2006 21:00 GMT

Bullet proof? I don't know.

Other ways - there are always other ways.
Use VBA and step through Mac+Nots records
Check Dq Issues for existing record
If no existing record, add a record and populate it.
If an error occurs, ...

> Thanks John!
>
[quoted text clipped - 34 lines]
>> >
>> > Dan

Reply to this Message