Make table from the last record 3

[peljo]

I want to make a table containing only the last record of the old table.For example i have a table orders.I want to make a new table orders1 where only the last record exists with all the fields available.Is it possible ? The autonumber of the table is called orderid

 

[fredericofonseca]

insert into new_table
select * from old_table
where orderid =
(select max(orderid) from old_table
group by orderid) --- not sure if on this case you need the group by.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[TheAceMan1]

How are ya peljo . . .

You can use the [blue]DoCmd.CopyObject[/blue] method to copy the table, then use an [blue]append query[/blue] to append the last record from the origional table.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[peljo]

Thank you for your reply. My code somehow does not work.Would you have a loo ?

it is as follows

Dim db As Database
Set db = CurrentDb
CurrentDb.Execute ("INSERT INTO [buffer] WHERE orderid = (select max(orderid) from [order details]")

 

[RgGray3]

I have two thoughts

First, Use a make table query (or SQL if it must be "code") and export out only your last record into it

However after additional thought I would expect that the reason creating a table with only the last record is to continue a program with a fresh almost empty table....

But the name would have changed...

So why not use a make table to archive all the old records... with some descriptive name "MyTable_Archived_2006-10-14_tbl" and...

run a delete query where the where clause is < DMax(Key field)

Run a compact and repair and you have a table ready to pick up and go....

Without more detail on the structure and functionality of the program, it is a little difficult to determine the best way to manage it...

 

[fredericofonseca]

peigo, look well at my code. you forgot something on yours.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[TheAceMan1]

How are ya fredericofonseca . . .

I think your post origination is one of the best ways to go (although I can only personally favor mine!)

In monitoring this thread I believe [blue]peljo[/blue] doesn't see [blue]Tek-Tips[/blue] . . . I believe [blue]peljo[/blue] see's [purple]Tek-Tips Do it All For You![/purple]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[TheAceMan1]

peljo . . .

You make a post origination asking for a solution . . . of which you are give two viable obtainable results . . . yet you toss them aside in lieu of what you already have that you should've posted in your origional!

[blue]What are we to think? . . .[/blue]

See Ya! . . . . . .

 

[peljo]

Thank you for all your suggestions.You must not be angry with me,actualy i am dummy in Access i simply cannot solve my poblem and it is important to me
I think i have forgotten to add the group by orderid in your lines.
I have aded them but now i get a "syntax error in the INSERT INTO statement.
Would you please help me ?
Here is the line where the error occurs :

CurrentDb.Execute ("INSERT INTO [buffer] WHERE orderid = (select max(orderid) from [order details] group by orderid)")

 

[fredericofonseca]

peijo,


read again my second post, and follow what I said there.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com