cut and paste from one table to another

[steve111]

hi,

I have 2 tables , one table I want to use as temporary information the other to use as permanent information

for example if I have 6 records in one table , I want to be able to press a button that removes those 6 records from the temporary table and put them in the permanent table. then later as i add more to the temporary data table again press a button and then temporary data is added to the permanent table

therefore the temporary table will always be empty when I press the button and the permanent table will keep adding the information I transfer across

thanks
steve

 

[olek_w1]

My sugeston is to make two queries

First which (INSERT TYPE) wil add data from Allocation table Into stock Allocation table

It's view in Sql mode should be like this:

INSERT INTO [stock allocation] ( [qty allocated], material, [order number], [date], clear, [order detailid] )
SELECT Allocation.[qty allocated], Allocation.material, Allocation.[order number], Allocation.date, Allocation.clear, Allocation.[order detailid]
FROM Allocation;

And after this query is executed , You will need run second one , which will delete all the records from temporary Allocation table

It's view in Sql mode should be like this:

DELETE *
FROM Allocation;

You can use run these queries by macro or by VBA code
using VBA command:

DoCmd.OpenQuery

 

[Galaxiom]

hi,

I have 2 tables , one table I want to use as temporary information the other to use as permanent information

What is your reason for this?

If you are wanting to display data as entered but not save anything until a button is pressed I would recommend you use the transactions with bound forms technique as discussed in this thread.

 

[RainLover]

What is your reason for this?

If you are wanting to display data as entered but not save anything until a button is pressed I would recommend you use the transactions with bound forms technique as discussed in this thread.

I am with Galaxiom on this one. You need to justify your reason in order to give quality advise.

 

[steve111]

thank you
example


TABLE allocated has a part called " qwerty" and allocated to the part was a quantity of 10


another table I have is called "requisition" it has that part " qwerty" in it and the required qty is 100


the total requirement is now 90 ( this is the amount I need to buy of the part " qwerty"


I have this working ok .


problem is


when I have purchased the part " qwerty" 90 off the requirement for that part has now gone ( still all ok )


if another requirement arrives for the part " qwerty" lets say for 50 off


the allocated parts 0f 10 is removed again now wanting 40 off (this is wrong)
there fore If I delete the records in table allocated the 50 that I want can be purchased unless I allocated a different number to part " qwerty"


but to keep records of what I have allocated I want that information in another table


hope that explains a little better


thanks
steve

 

[RainLover]

I am not 100% sure on your objectives and the design of your tables, but I can submit this for your consideration.

It is not good to delete your history. You should be able to run reports that tell you what the situation is now and what was it like a Day ago, or 2 days or 100 days and anything in between. You may not need this kind of information at the moment but there is a Wright and wrong way to do things. Deleting used records is wrong 99.99% of the time.

Instead of deleting a record you would be better served by marking that record in an additional field that the record is achieved..

 

[steve111]

hi,

yes I agree, that's why I want to copy the data to another table

this formula still looks at previous records which is wrong so if I delete the data from allocation it cannot read old data

text45: IIf(DLookUp("[material]","Allocation","material= '" & Stocklist.material & "'") Is Null,[qty outstandng],[qty outstandng]+DSum("[qty allocated]","Allocation","material= '" & Stocklist.material & "'"))

and the only way I can think of trying to do it is by cut and pasting it , unless the formula can be changed. ( there is tick box I could use in the table allocation if that could be used to say ignor that value for that part when the DSUM works

thanks
steve

 

[Galaxiom]

Moving data between tables generally means you are embedding information in structure. This is the wrong approach entirely and almost inevitably will lead to many problems especially when you get to reporting.

Information should be stored in records as field values. You need to add a field that holds a value that can be used as the equivalent of moving the data to another table.

I have not looked closely at what you are doing but it may be that you could have a field that stores the ID of whatever the record has been allocated to. If there are multiple allocations then this data might be stored in a junction table to support a many-to-many relationship.

Then design your queries to select based on the existing allocation data.

 

[steve111]

hi, to be honest I am really struggling with this part .
I don't really need to report or keep records of this allocation data/qty because when the parts are being ordered the allocation qty (parts from the stock) will have been removed from the qty required.

but like others and yourself have said it is good to keep records

if 50 parts required for an order , the system look at how many of them parts are in stock say ( 20 ) then system then says order 30

if I want to use 20 of them parts from stock to start the order early , I can and I alter the stock to zero, problem is now the system says I need to order 50 not 30 ,

so what I now do is use the allocation table this tells me I have took 20 the stock is zero, but then system looks at the order requirement ( 50 ) sees I took 20 as it looks at the allocation table and still orders 30. but I will order more ( 40 )to keep some in stock say ( 10 )

all ok

when I get a new order for the same part say 100
it looks at stock 10 off but the problem is it looks at that allocation again ( 20 ) and says to order 70 this is wrong it should be 90 unless I allocated those 10 from stock which would tell me to order 90

that the part I cannot solve unless I delete the parts from allocation every time

steve

 

[MarkK]

Moving data from table to table in a database is like taking the stairs instead of the elevator. Obviously it is possible to take the stairs, but it is not efficient.

 

[Galaxiom]

Moving data from table to table in a database is like taking the stairs instead of the elevator. Obviously it is possible to take the stairs, but it is not efficient.

More like going out the window and climbing up the outside wall.

 

[Galaxiom]

The problem really begins because you are not working entirely with transactions but are storing the stock quantity.

Once you change the value in the quantity field you have no record of why it changed. In a transaction based system each action has a time and the state of the system can be calculated for any arbitrary time in history.

Calculate the available stock from the sum of the movements. The allocation is a stock transaction record. It may be implicit from a status of the line in a sales transaction record.

 

[steve111]

hi ,

thanks for your advise , I have re done some formulas etc and I think I have now managed to keep the data in one table without deleting it but could you please look at the query material details 91 days andtell me why I am getting a prompt for qty as I don't want this although the results are still ok when I ignor it

it is I think in the formula called " qty required"

thanks again
stee

 

[RainLover]

http://allenbrowne.com/AppInventory.html

This link may help you out. Allen Browne is a MS VIP and has a good reputation.

His discussion on Inventory Control may be of benefit to you. There is a working example on this site that may also help.