How to store records into history table

[Kurosagi85]

i need help and its urgent...

i have created a table with fields: ItemNo, ItemName, IncomingStocks
and also DateReceived.

The IncomingStocks and DateReceived are volatile. it is expected to
change several times during the life of a record

I have tried the simple append query
but it keep on copying the whole records instead of the 'ONE' record
that i change..

What code do i need to use to have it append only the record the user
has
touched? I have created a append query, but this will append all the
records
from the main table to history table.

INSERT INTO ItemHistory ( ItemNo, ItemName, DateReceived,
IncomingStocks )
SELECT Item.ItemNo, Item.ItemName, Item.DateReceived,
Item.IncomingStocks
FROM Item;

can u guys please help me....

tx

[pietlinden]

How about doing something really different and using a WHERE clause?
Urgent? Sounds like you urgently need to read the help file. And why
are you copying records around? Why not just stick a boolean field in
your table and set the records you want archived to Active=False or
something simple like that?

Try starting at the beginning. Create a query that selects the records
you want. Then change it into an update/append/delete query.

[Jamey Shuemaker]

pi********@hotmail.com wrote:

How about doing something really different and using a WHERE clause?
Urgent? Sounds like you urgently need to read the help file. And why
are you copying records around? Why not just stick a boolean field in
your table and set the records you want archived to Active=False or
something simple like that?

Try starting at the beginning. Create a query that selects the records
you want. Then change it into an update/append/delete query.

[Jamey Shuemaker]

how about just binding the data correctly?

[Kurosagi85]

but i need to store the exact same records..same field...for the
archive. for instance

DateReceived : 1-sep-2006
ItemNo : 1
ItemName : Sweets
IncomingStock : 50 boxes
and then the next day i bought another 10 boxes of sweets and i have to
fill in the form for 10

DateReceived : 2-sep-2006
ItemNo : 1
ItemName : Sweets
IncomingStock : 10

so in my archive i want it to be like this:

DateReceived ItemNo ItemName IncomingStock

1-sep-2006 1 Sweets 50

2-sep-2006 1 Sweets 10

and so on... however...when i'm using my append it become like this if
i have another item:

DateReceived ItemNo ItemName IncomingStock

1-sep-2006 1 Sweets 50

2-sep-2006 1 Sweets 10

1-sep-2006 1 Sweets 50

2-sep-2006 1 Sweets 10

3-sep-2006 2 Choco 15
Can sumone help me..i'm new in this area

[pietlinden]

I'm not sure why a single table won't do the job. if you change your
structure to this:

DateReceived : 1-sep-2006
ItemNo : 1
ItemName : Sweets
IncomingStock : 50 boxes

>>ArchiveDate: <Null>

then change the ArchiveDate when you want the record to "disappear".

UPDATE Inventory
SET ArchiveDate = Date
WHERE ArchiveDate IS NULL

The only practical difference is that instead of basing your queries
for current inventory or whatever on a _table_ you would base them on
the _query_ that showed the active records.
The nice thing about doing it this way is that if you want a full
history, you just query the entire table.

[pietlinden]

Why don't we back up a little? What are you trying to get OUT of this
data? What questions are you trying to answer? That's what would
dictate the structure of your database. If you don't know that,
there's absolutely no way to validate the structure of your database.