Update Query vs Append Query

[kwc]

I am pretty new at this, so please bear with me.

I have a table with all my records. I update my table. Today, I updated my table with three new records. I want to take only those 3 records and put them into another smaller table which has only 4 fields. I can't make it happen... I can get it to Append all the records, but I only want the three new one.

Your help is appreciated.

[missinglinq]

You have posted your question in the Articles section rather than the Forum section. I have moved it across for you.

Linq ;0)>

[kwc]

Thank you very much !

You have posted your question in the Articles section rather than the Forum section. I have moved it across for you.

Linq ;0)>

[JKing]

Hi there. Could you post the metadata for the two tables you are using.

Here is an example of how to post table MetaData :
Table Name=tblStudent

      Field; Type; IndexInfo
      StudentID; AutoNumber; PK
      Family; String; FK
      Name; String
      University; String; FK
      Mark; Numeric
      LastAttendance; Date/Time

[kwc]

thanks for your help

Table Name – tbl2007

FieldName; Type,

Item# AutoNumber
Invoice #
Invoice Code
Description
Date
Acct#
Return#
ContactName
ContactAddress
ContactCity
ContactState
ContactZip


I want to update tblStatus with only a few fields, and only the new records in tbl2007

Table Name – tblStatus

(Fields to be updated from tbl2007)

Item# AutoNumber
Invoice #
Invoice Code
Description

[JKing]

The append query would be something like this:

[code=sql]
INSERT into tblStatus (Item, Invoice, [Invoice Code], Description)
SELECT tbl2007.Item, tbl2007.Invoice , tbl2007.[Invoice Code], tbl2007.Descrip tion
FROM tbl2007
WHERE tbl2007.Item Not In (SELECT tblStatus.Item From tblStatus)
[/code]

This is the basic lay out of what you will need to do. I'm assuming here that you will want to append any records from tbl2007 that aren't currently in tblStatus. This is handled by the where clause. If this is not the case how do you plan on determining which records are new in tbl2007? By date perhaps?

[kwc]

Exactly what I needed. I will go try it...... thanks!

[FishVal]

Exactly what I needed. I will go try it...... thanks!

Hi, kwc.

Just a silly question.

What a special reason do you have to maintain separate table which is just field subset of another one?

Why not to use simple query like this

SELECT tbl2007.Item, tbl2007.Invoice, tbl2007.[Invoice Code], tbl2007.Description
FROM tbl2007

to retrieve necessary data any time you like?

[kwc]

Hi, kwc.

Just a silly question.

What a special reason do you have to maintain separate table which is just field subset of another one?

Why not to use simple query like this

SELECT tbl2007.Item, tbl2007.Invoice, tbl2007.[Invoice Code], tbl2007.Description
FROM tbl2007

to retrieve necessary data any time you like?

NEVER a silly question - it actually helps me think through how to do things in a better more efficient way. I greatly appreciated. My problem is - and help me if there is a better way - I have to export into excel, and in my excel spreadsheet (which I have to email to the managers) I add a few more fields and mess with it....

I am actually confusing and probably complicating my situation with multiple places where I need to update data...

[FishVal]

NEVER a silly question - it actually helps me think through how to do things in a better more efficient way. I greatly appreciated. My problem is - and help me if there is a better way - I have to export into excel, and in my excel spreadsheet (which I have to email to the managers) I add a few more fields and mess with it....

I am actually confusing and probably complicating my situation with multiple places where I need to update data...

Ok. I have some additional questions.

• You want to export to excel only some records from tbl2007. What criteria do you want to use to select records (date range, those never been sent previously etc.) ?
• What are these additional fields in excel spreadsheet? I mean whether they are being added to each record becoming a part of query or they are separate single fields?
• Do you want to use some template excel file or you want to build it from scratch?

[NeoPa]

Fish, you're on the right lines there. The OP probably doesn't want a separate table for this stuff.
KWC, if you can answer FishVal clearly I'm sure he can help you to do this in a more straightforward (natural) way.