Solved

Access Design question for tracking an item that will later be split

Posted on 2014-01-14
10
375 Views
Last Modified: 2014-01-15
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......
0
Comment
Question by:wlwebb
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +3
10 Comments
 
LVL 27

Assisted Solution

by:MacroShadow
MacroShadow earned 125 total points
ID: 39781422
Why create a totally different serial for the split items, just append a letter to the end so ABC123XYZ becomes ABC123XYZa and ABC123XYZb. Then you can easily grab all items that begin with the original sn.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 39781530
One way:
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)
0
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 39781947
I'd agree with MacroShadow, but of course that depends on exactly what you're referring to. If you could give us more detail, we might be able to flesh it out further.

On the surface, however, if you do that (create a new item), then you'd have a standard Parent-Child relationship:

tItem
----------------
ItemID
ItemDesc
ItemSerial
ItemParent
etc etc

So when I receive ItemX, with a serial of SX123, I'd enter a new record in tIteM

INSERT INTO tItem(ItemDesc, ItemSerial, ItemParent) VALUES('ItemX', 'SX123', 'SX123')

This inserts with an ItemID of 100.

I then cut a piece from ItemX, name it ItemX-1 and Serialize it as SX123-1. I insert an new record:

INSERT INTO tItem(ItemDesc, ItemSerial, ItemParent) VALUES('ItemX-1', 'SX123-1', 'SX123')

I could then query back on my items using the ItemParent field, so I'd find all "child" items of a single Parent. If I need to go back to more than one generation, I'd have to create a more complex query, or create a function that would stuff all the data into a temp table.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 48

Expert Comment

by:Dale Fye
ID: 39782007
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.
0
 

Author Comment

by:wlwebb
ID: 39782442
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

???
0
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 39782633
I don't know if you need a M-2-M join or not, since we really don't know much about the entire process.

But in the scenario I described above, you wouldn't have any Blank or Null values. When you insert the "root" item, you add the ItemName and ItemParentName, and the values are the same. Note I changed the names slightly from my previous suggested table.

When you "split" that item, then you insert a NEW ItemName, and the ItemParentName would be the "root" item from which the split occurred. For example:

tItem
----------------
ItemID
ItemName
ItemParentName
etc etc

Now add the Root time 'X123':

INSERT INTO tItem(ItemName, ItemParentName) VALUES('X123', 'X123')

Now split X123:

INSERT INTO tItem(ItemName, ItemParentName) VALUES('X123-1', 'x123')
INSERT INTO tItem(ItemName, ItemParentName) VALUES('X123-2', 'x123')

Now split X123-2 into 4 items:

INSERT INTO tItem(ItemName, ItemParentName) VALUES('X123-2-1', 'x123-2')
INSERT INTO tItem(ItemName, ItemParentName) VALUES('X123-2-2', 'x123-2')
INSERT INTO tItem(ItemName, ItemParentName) VALUES('X123-2-3', 'x123-2')
INSERT INTO tItem(ItemName, ItemParentName) VALUES('X123-2-4', 'x123-2')

Now split X123-2-3 into 2 items:

INSERT INTO tItem(ItemName, ItemParentName) VALUES('X123-2-3-1', 'x123-2-3')
INSERT INTO tItem(ItemName, ItemParentName) VALUES('X123-2-3-2', 'x123-2-3')

... and so on.

If you've only got a few 'levels', this would work fairly well, and the only headache would be when you needed to find all offspring of a "root" item. You'd have to run iterative queries, and perhaps use a temp table to hold the results.

If you have many levels - say more than about 5 "splits" - then you may need to reconsider what you're doing, or perhaps come up with alternative designs. Of course, without knowing more about exactly what you're doing it's hard for us to provide more suggestions. If you could provide us some of the details of what you're doing, using real-world terms and situations, then perhaps we could dive a bit further in ...
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 39782708
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.
0
 

Author Comment

by:wlwebb
ID: 39783024
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.
0
 
LVL 48

Accepted Solution

by:
Dale Fye earned 125 total points
ID: 39783094
In that case, what you would probably do is when you create a new (actually older) parcel, you would provide your user with a way, maybe in a subform or list, to select the newer parcel(s) that should be identified as children of the older parcel.

You would only need to display those records from newer RecPage, RecDates in this list or subform.  But once the new (older) parcel is recorded, you would record it's TracktID as the Parent of all of those parcels that were children of that parcel.
0
 

Author Closing Comment

by:wlwebb
ID: 39784040
Thank you all!  Appreciate all of the input and feedback.
0

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question