I think you want to introduce a third table.
CREATE TABLE Moves
(
MoveID int IDENTITY NOT NULL PRIMARY KEY,
Date datetime NOT NULL
)
CREATE TABLE Items
(
ItemID int IDENTITY NOT NULL PRIMARY KEY,
ItemDescription varchar(30) NOT NULL,
)
CREATE TABLE ItemsOnMove
(
ItemsOnMoveID int IDENTITY NOT NULL PRIMARY KEY,
MoveID int NOT NULL FOREIGN KEY REFERENCES Moves(MoveID),
ItemID int NOT NULL FOREIGN KEY REFERENCES Items(ItemID),
quantity int NOT NULL
)
This way you have one table that keeps track of all moves. You have another table that keeps track of items that can be moved. the third table actually keeps track of items moved on a particular move.
For example:
Moves
---------------------------
MoveID || Date
---------------------------
1 || 01/21/08
2 || 01/22/08
---------------------------
Items
---------------------------------
ItemID || ItemDescription
----------------------------------
1 || Table
2 || Chair
3 || Pool Table
---------------------------------
ItemsOnMove
-------------------------------------------------------
ItemsOnMoveID|| MoveID || ItemID|| Quantity
-------------------------------------------------------
1 || 1 || 2 || 4
2 || 1 || 1 || 1
3 || 1 || 3 || 1
4 || 2 || 1 || 1
5 || 2 || 2 || 2
so for example to look at everything that was moved for Move 1 we can say
SELECT ItemDescription, Quantity
FROM Items i
INNER JOIN ItemsOnMove iom
ON i.ItemID = iom.ItemID
WHERE MoveID = 1
Which should give the output
ItemDescription || Quantity
--------------------------------
Chair || 4
Table || 1
Pool Table || 1
Does that help? I am new to this myself so further input, corrections, refinements would be much appreciated from my end as well.
Thank you,
Aaron
Marked Solved 
