By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,264 Members | 1,100 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,264 IT Pros & Developers. It's quick & easy.

Update Query vs Append Query

P: 7
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.
Jul 25 '07 #1
Share this Question
Share on Google+
10 Replies


missinglinq
Expert 2.5K+
P: 3,532
You have posted your question in the Articles section rather than the Forum section. I have moved it across for you.

Linq ;0)>
Jul 25 '07 #2

P: 7
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)>
Jul 25 '07 #3

JKing
Expert 100+
P: 1,206
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
Expand|Select|Wrap|Line Numbers
  1.       Field; Type; IndexInfo
  2.       StudentID; AutoNumber; PK
  3.       Family; String; FK
  4.       Name; String
  5.       University; String; FK
  6.       Mark; Numeric
  7.       LastAttendance; Date/Time
  8.  
Jul 25 '07 #4

P: 7
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
Jul 26 '07 #5

JKing
Expert 100+
P: 1,206
The append query would be something like this:

Expand|Select|Wrap|Line Numbers
  1. INSERT into tblStatus (Item, Invoice, [Invoice Code], Description)
  2. SELECT tbl2007.Item, tbl2007.Invoice, tbl2007.[Invoice Code], tbl2007.Description
  3. FROM tbl2007
  4. WHERE tbl2007.Item Not In (SELECT tblStatus.Item From tblStatus)
  5.  
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?
Jul 26 '07 #6

P: 7
kwc
Exactly what I needed. I will go try it...... thanks!
Jul 26 '07 #7

FishVal
Expert 2.5K+
P: 2,653
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
Expand|Select|Wrap|Line Numbers
  1. SELECT tbl2007.Item, tbl2007.Invoice, tbl2007.[Invoice Code], tbl2007.Description
  2. FROM tbl2007
to retrieve necessary data any time you like?
Jul 26 '07 #8

P: 7
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
Expand|Select|Wrap|Line Numbers
  1. SELECT tbl2007.Item, tbl2007.Invoice, tbl2007.[Invoice Code], tbl2007.Description
  2. 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...
Jul 26 '07 #9

FishVal
Expert 2.5K+
P: 2,653
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?
Jul 26 '07 #10

NeoPa
Expert Mod 15k+
P: 31,494
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.
Jul 26 '07 #11

Post your reply

Sign in to post your reply or Sign up for a free account.