Move records from one table to another

[Khushalay]

Hi there

I need a help in Access. I have a full database including too many fields. Now I want to insert a checkbox 'shipped'. When I tick it, I need the 'ticked' records to automatically move(cut) from the order table/file to shipped table/file.
Is it possible?
If yes, how can I achieve this?
Plz help me.

Thanks in advance.

 

[Galaxiom]

I have a full database including too many fields.

By a "full database" do you mean the file is approach 2GB? If you are near that limit you should be using a database server backend. Are you are wanting to move records between multiple back ends?

Access supports 250 field per table. If you are anywhere near that limit your data structure is wrong.

I think you need to tell us more about the problem rather than assuming the solution involved moving records from one table to another.

 

[Simon_MT]

Rather than split the file, you would be better to change your approach. Try having your query to filter out the Shipped transactions and another to show the history i.e. has been shipped. If you index the Shipped field it will probably improcve performance.

I did once try splitting as file but it was painful. Instead I acheived the same result, whilst still being able to get a complete profile of transactions when required.

Simon

 

[Khushalay]

By a "full database" do you mean the file is approach 2GB? If you are near that limit you should be using a database server backend. Are you are wanting to move records between multiple back ends?

Access supports 250 field per table. If you are anywhere near that limit your data structure is wrong.

I think you need to tell us more about the problem rather than assuming the solution involved moving records from one table to another.

My db has exactly 116 fields and its working fine. Just need to move records. What could be done?

 

[Khushalay]

Rather than split the file, you would be better to change your approach. Try having your query to filter out the Shipped transactions and another to show the history i.e. has been shipped. If you index the Shipped field it will probably improcve performance.

I did once try splitting as file but it was painful. Instead I acheived the same result, whilst still being able to get a complete profile of transactions when required.

Simon

Thanks Simon
I have already split my db in back end and front end. Is that what you are suggesting? or some other approach?

Ok so I will design a query to filter the shipped records but then I need to move those records from the ''order' table to the ''shipped'' table. I need it to be deleted from the órder' table. How can I achieve this?
Waiting for your response.

 

[bob fitz]

Ok so I will design a query to filter the shipped records but then I need to move those records from the ''order' table to the ''shipped'' table. I need it to be deleted from the órder' table. How can I achieve this?
Waiting for your response.

Please explain why you think the records need to be moved to another table.

 

[Simon_MT]

Don't split the table just filter out the records.

Simon

 

[Khushalay]

Please explain why you think the records need to be moved to another table.

We have one table for all the orders coming. Now when the orders get ready and are shipped, they need to be moved from the orders table to the shipped table. So that the orders table only contain the new/pending orders.

 

[Khushalay]

Don't split the table just filter out the records.

Simon

I can filter the records but how can I move them to another table. Plz help

 

[bob fitz]

I can filter the records but how can I move them to another table. Plz help

You keep saying that you need to move the records but you are not saying what that need is. If you can filter the records I see no reason to move them to another table.

 

[bob fitz]

We have one table for all the orders coming. Now when the orders get ready and are shipped, they need to be moved from the orders table to the shipped table. So that the orders table only contain the new/pending orders.

It would be better to keep all the records in one table and just flag the orders that have been shipped.

 

[Khushalay]

It would be better to keep all the records in one table and just flag the orders that have been shipped.

sometimes we need to only deal with the pending orders and like printing and all and sometimes we need to play with the shipped orders. So we want to keep it in separate tables.

Can I get the solution how to move records from one table to another please?

 

[bob fitz]

sometimes we need to only deal with the pending orders and like printing and all and sometimes we need to play with the shipped orders. So we want to keep it in separate tables.

Can I get the solution how to move records from one table to another please?

But you don't need to have different tables. Just filter the table for whichever records you need using a query.

 

[Khushalay]

ok suppose I want to shift then...

you are not answering my question

 

[Minty]

Everyone is avoiding telling you because, normally, moving records is a sign of a poor design that causes masses of extra work further down the line.
e.g. What if an order is part shipped? Do you end up with two copies of it?
What if you want to see all orders shipped and outstanding for a customer? - you have to pull the data in from 2 tables which is messy.
Normally, as everyone is telling you, is that your order table simply has a shipped date, and you filter on that.

 

[Khushalay]

Everyone is avoiding telling you because, normally, moving records is a sign of a poor design that causes masses of extra work further down the line.
e.g. What if an order is part shipped? Do you end up with two copies of it?
What if you want to see all orders shipped and outstanding for a customer? - you have to pull the data in from 2 tables which is messy.
Normally, as everyone is telling you, is that your order table simply has a shipped date, and you filter on that.

alright got the point. One more thing if I only need to take print of the forms that are shipped or not shipped. How to go about that.

Consider em a novice and help please.

 

[Minty]

If you have a shipped date in your order table, then your query would be be something like

Outstanding Orders
SELECT * From YourOrderTable WHERE ShippedDate is Null

Shipped Orders
SELECT * From YourOrderTable WHERE ShippedDate is Not Null

Shipped Orders in the last thirty days

SELECT * From YourOrderTable WHERE ShippedDate > Date() -31

Does this make sense?

 

[Khushalay]

I have just a check box for shipment. If ticked then shipped if unchecked, it means still pending.

 

[Simon_MT]

We are not being obstuce, we tried doing what you want to do and have go back to a single table. In my experience, it was about the number of records, it took a lot of work, a lot of unnecessary work - a total waste of time - it was a dumb thing to do, particularly irritating as a Indexed Flag was all that was needed.

Simon

 

[Khushalay]

aye got it, now I want to give a print command to print only those orders that are not shipped.