Access Design question for tracking an item that will later be split
Hello experts!
I am perplexed on how to best design something. For sake of simplicity let's assume it is an inventory item since that's universally understood.
Let's say I'm designing a tracking for Diamonds. Each Diamond is assigned a unique ID or serial number.
So in a table I would create say one table for Inventory Descriptions to limit or control what class each Diamond is put in to. Then another table for where I would assign every Diamond it's Serial Number and select the Inv Description.
Now then, let's say I have Diamond serial number ABC123XYZ. Diamond's often are split or re cut. So then, I'd like to have a function where Diamond ABC123XYZ becomes
ABC123ABC
and
ABC123GHI
However, when I start pulling history reports I would like to see the trail from the first to the end.
If I have one table for Diamond Serial number how do I accomplish that????????
Do I setup another table for that historical detail ???? I'm having a mental block on how it could be accomplished.
By the way.....it's really not Diamonds......
I am perplexed on how to best design something. For sake of simplicity let's assume it is an inventory item since that's universally understood.
Let's say I'm designing a tracking for Diamonds. Each Diamond is assigned a unique ID or serial number.
So in a table I would create say one table for Inventory Descriptions to limit or control what class each Diamond is put in to. Then another table for where I would assign every Diamond it's Serial Number and select the Inv Description.
Now then, let's say I have Diamond serial number ABC123XYZ. Diamond's often are split or re cut. So then, I'd like to have a function where Diamond ABC123XYZ becomes
ABC123ABC
and
ABC123GHI
However, when I start pulling history reports I would like to see the trail from the first to the end.
If I have one table for Diamond Serial number how do I accomplish that????????
Do I setup another table for that historical detail ???? I'm having a mental block on how it could be accomplished.
By the way.....it's really not Diamonds......
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
No points please.
I agree with Scott that the Parent-Child relationship is a great way to go in this case. It can take a bit of work to get the list of items that came from the same root (parent) stone, especially if you cut the stone and then cut it's children making it a grandparent- parent-child type relationship, but it can be done.
I agree with Scott that the Parent-Child relationship is a great way to go in this case. It can take a bit of work to get the list of items that came from the same root (parent) stone, especially if you cut the stone and then cut it's children making it a grandparent- parent-child type relationship, but it can be done.
ASKER
Scott
So as I understand it, my table would have a field where most of the time the field will be null or blank. Is that correct? Am I understanding that right... OR would you set up a second table that is a many to many with just
ItemID and ItemParentID
???
So as I understand it, my table would have a field where most of the time the field will be null or blank. Is that correct? Am I understanding that right... OR would you set up a second table that is a many to many with just
ItemID and ItemParentID
???
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Since an item can have only one "parent" you would use the self-referencing technique described by Scott. Don't try to use the serial number to track parentage. This would get cumbersome in a hurry.
Kitting, which is common in manufacturing applications, goes the other way and combines individual items into one unit. That requires a separate table since any individual part could belong to one or more kits.
Kitting, which is common in manufacturing applications, goes the other way and combines individual items into one unit. That requires a separate table since any individual part could belong to one or more kits.
ASKER
Scott
Thank you for the input...
Ok for more detail then....... I'm dealing with Land so if I'm tracking land backwards in time and entering records here is the scenario
I've defined two tables key to this tracking I need to do.
First is a records table that has the RecBookType, RecPage and RecDate. My primary key is [RecID].
Deeds can have multiple tracts of land defined on the Deed. So I created a table for Land Tracts where I reference the RecID. So within the Land Tracts table I have LandTractID, RecID, LandTractDescription.
Now then as the research is done. you start with the most current Deed. That info gets set up in the tables via the forms I've created.
Within each Deed the description generally (but not always in detail) will include a reference to the prior deed where the person selling bought it and its Book, Page and Date.
As info is recorded backwards in time you inevitably find that today's parcel x-24 came from a split of parcel X and parcel x came from a split of parcel n which n was a combination of parcels a and b that get split to n and p etc....
That's what I'm doing.
Thank you for the input...
Ok for more detail then....... I'm dealing with Land so if I'm tracking land backwards in time and entering records here is the scenario
I've defined two tables key to this tracking I need to do.
First is a records table that has the RecBookType, RecPage and RecDate. My primary key is [RecID].
Deeds can have multiple tracts of land defined on the Deed. So I created a table for Land Tracts where I reference the RecID. So within the Land Tracts table I have LandTractID, RecID, LandTractDescription.
Now then as the research is done. you start with the most current Deed. That info gets set up in the tables via the forms I've created.
Within each Deed the description generally (but not always in detail) will include a reference to the prior deed where the person selling bought it and its Book, Page and Date.
As info is recorded backwards in time you inevitably find that today's parcel x-24 came from a split of parcel X and parcel x came from a split of parcel n which n was a combination of parcels a and b that get split to n and p etc....
That's what I'm doing.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you all! Appreciate all of the input and feedback.
Is to use numeric code and the alpha as description.
So 10 then 11 12 13 14 15
20 then 21 22 23 24 25
You may have main table: categories(ID, description)
Sub table : profucts(catID, prodID)