Moving records 2

[kpryan]

Hi all,

Could I have some advice on how to move records from one table to another. I have a tbl which holds invoice data. I have a field 'status which related to whether an invoice has been paid or not. I want to be able to move the 'paid' invoices to another tbl.

Many thanks,

Ken

 

[ClydeData]

Why not ether have a status of Paid on your invice table,

Or a sub table of invoice payments where you will have BalanceDue= InvoiceAmount - Sum(invoicepaments)


Then all invoices can be viewed whether paid/PartPaid/unpaid


Hope this helps

Jimmy

 

[kpryan]

Hi ClydeData,
Yes the status field is marked as 'paid', once payment is received..
I just want to move the 'paid' invoices to another tbl so that all current invoiced are accessible. I just want to reduce the amount of data in the table.

Ken

 

[ClydeData]

This here is a cut down of what I use for my own invoices.

http://www.box.net/shared/qqsojsbzd3

It will extract to C:\InvoiceSample

Filters have been put in to view only those owing money


Jimmy

 

[JoeAtWork]

It's just a matter of two simple SQL statements:
1. An INSERT INTO where the source is a SELECT statement on the original table, filtered by the Status field
2. Then a DELETE against the first table using the same WHERE clause when you did the INSERT

You can build these SQL statements in the query analyzer, and the syntax can easily be found in Google searchs and/or your favourite SQL reference book.