Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-16-2016, 05:30 AM   #1
txmmoore
Newly Registered User
 
Join Date: Dec 2014
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
txmmoore is on a distinguished road
Append only 1 record

I am trying to write an append query that will ONLY append the last record of one table into that of another table. Every time I try this, Access wants to Append all the records. How do you force it to only append the last record?

txmmoore is offline   Reply With Quote
Old 08-16-2016, 05:48 AM   #2
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,847
Thanks: 55
Thanked 2,183 Times in 2,094 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Append only 1 record

it would be easier if you have AutoNumber field on your source table:

Insert Into TargetTable (a, b, c) Select Top 1 x, y, z From SourceTable Order By AutoField Desc;
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 08-16-2016, 05:56 AM   #3
txmmoore
Newly Registered User
 
Join Date: Dec 2014
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
txmmoore is on a distinguished road
Re: Append only 1 record

So, something like this?

Insert Into Prerelease_info (ID Select) Select Top 1 [ID] From Customer_Info Order By AutoField Desc;

I get a Syntax error. I am new at this, so please help.

txmmoore is offline   Reply With Quote
Old 08-16-2016, 06:20 AM   #4
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,847
Thanks: 55
Thanked 2,183 Times in 2,094 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Append only 1 record

what is the fieldname of autonumber field?
what are the field you want to insert?
dont include the autonumber in the insert.

if ID is autonumber field in your source table:

insert into Prerelease_info (targetFiel1, targetField2) Select Top 1 sourceField1, sourceField2 From Customer_Info Order By ID Desc;
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 08-16-2016, 06:33 AM   #5
txmmoore
Newly Registered User
 
Join Date: Dec 2014
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
txmmoore is on a distinguished road
Re: Append only 1 record

Still having problems. Here is my summary:

Table1 -
ID - auto number
Name - Person's name

Table2 -
ID Select - Needs to be the same as ID in Table 1
Name - Needs to be the same as Name in Table 1

Please advise how the Insert command should look.

I only want to copy the last record in Table 1 into Table 2

Thank you!
txmmoore is offline   Reply With Quote
Old 08-16-2016, 06:38 AM   #6
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,934
Thanks: 10
Thanked 2,147 Times in 2,102 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Append only 1 record

Why? Action queries are generally tools of people who don't fully understand how databases work. You shouldn't "move" data around a database. Instead you use data to identify special records.

Please explain what the difference between Table1 and Table2 is? If you could provide actual table names that would help as well. My guess is the actual solution to your real issue is the addition of a new field to Table1.
plog is offline   Reply With Quote
Old 08-16-2016, 06:42 AM   #7
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,847
Thanks: 55
Thanked 2,183 Times in 2,094 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Append only 1 record

Insert Into Prerelease_info ([ID Select],[Name]) Select Top 1 [ID],[Name] From Customer_Info Order By [ID] Desc;

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 08-16-2016, 08:08 AM   #8
txmmoore
Newly Registered User
 
Join Date: Dec 2014
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
txmmoore is on a distinguished road
Re: Append only 1 record

Still get Syntax error!
See attached screen shot. Why can't access be easier!
Attached Images
File Type: jpg Capture.JPG (47.3 KB, 38 views)
txmmoore is offline   Reply With Quote
Old 08-16-2016, 07:21 PM   #9
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,847
Thanks: 55
Thanked 2,183 Times in 2,094 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Append only 1 record

you use DoCmd or DBEngine(0)(0).Execute:

DoCmd.RunSQL "Insert Into Prerelease_info ([ID Select],[Name]) Select Top 1 [ID],[Name] From Customer_Info Order By [ID] Desc;"

or

DbEngine(0)(0).Execute "Insert Into Prerelease_info ([ID Select],[Name]) Select Top 1 [ID],[Name] From Customer_Info Order By [ID] Desc;"
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 08-17-2016, 03:46 AM   #10
txmmoore
Newly Registered User
 
Join Date: Dec 2014
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
txmmoore is on a distinguished road
Re: Append only 1 record

Thank you. That is exactly what I needed and it worked! Case Closed!

txmmoore is offline   Reply With Quote
Reply

Tags
append , query

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Check if record exists, if no then append record Steph007 Modules & VBA 8 04-20-2013 01:40 AM
Append to next record? Michael Auer Queries 11 02-01-2008 05:45 AM
Append ONE Record aerofuego Modules & VBA 2 04-04-2005 08:38 PM
How to append every nth record? azgambit Queries 3 01-10-2005 01:57 AM
Append record margt_a General 6 02-24-2003 10:33 AM




All times are GMT -8. The time now is 07:27 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World