Subtype Record Entry by Form

[SolemnWishes]

Hi all,

I've tried Google for hours and can't find a solution to my problem. In the forums, this poster asked the exact question I have, but there was no solution. So I'm going to ask again.

Goal: A form to input a particular subtype record, where the supertype contains some of the qualities of the subtype.

Scenario: The following are simplified versions of my database's table (excluded some non-relational fields):
tblSupply
SupplyID (PK)
SupplyTypeID (FK)

tblSupplyType
SupplyTypeID (PK)
TypeName (Short Text)
IsAsset (Yes/No)

tblAsset
SupplyID (PK & FK)
SerialNum (Short Text)
AssetModelID (FK)

tblAssetModel


AssetModelID (PK)
ModelName (Short Text)
SupplyTypeID(FK)

tblConsumable
SupplyID (PK & FK)
QuantityOnHand (Number)

So basically I have Supply supertype, which is separated in Asset and Consumable subtype. Asset would be something like a specific nailgun and Consumable would be something like a single nail. It should be noted that each Asset's SupplyTypeID can be found by two paths: in tblSupply via SupplyID or in tblAssetModel via AssetModelID. This bring me a side minor issue:

Defining an Asset's SupplyType twice is the basic rules of normalization, but I want to associate each AssetModel with a SupplyType since they are not independent. That is an AssetModel is always only one SupplyType. Is this a wise denormalization? Or is there a better setup?

My primary problem is that I don't know how to create a form that records new Assets. If I start with a form based on tblAsset, it won't automatically pull an autonumber SupplyID. Also, I won't be able to input SupplyTypeID on tblSupply.

Is there some way to create a textbox control for both SupplyID and SupplyTypeID?
Should I make a form based on tblSupply and add a subform based on tblAsset? And how would I ensure that the record is linked?
Would setting up PKs for Assets and Consumables be helpful in any way?

Please help. Thank you very much.

[June7]

Don't think I would lump assets and consumables both as supplies. An asset is usually considered an equipment item and has depreciable life (car, TV, nailgun, computer) - except for real estate. Consumables (nails, pencils, staples) are just depleted. Most business operations have a threshold based on category and/or dollar value to determine if something is an accountable asset or a consumable supply expense.

[SolemnWishes]

June7,

You are totally right! But I'm not looking to track depreciation at all.

We have field crews that request assets and consumables from home base, where I'm at. I am making a database to record orders, deliveries, returns... I'll also need to print reports on activity and run statistics that don't discriminate between the two. Operationally speaking, assets and consumables are not very different in this situation as both are items we need to deliver to the jobsites.

Honestly, this started out simple in my mind, but then I started reading about normalizing, which made things complicated. And my obsessive personality just couldn't let it go.

Also, i have a similar supertype/subtype problem for my different type of supply transactions. Specifically, supertype tblTransactions is separated into subtype tblDeliveries, subtype tblMaintenance, subtype tblDisposals... Each have their unique attributes, e.g. DeliveryLocation, EmployeeThatPerformedMaintenance, DisposalMethod, respectively. I thought the Supply scenario was easier to understand, so I used it as a platform to ask my question about handling supertypes and subtypes.

Thank you very much for your input. Please let me know if there is some reading I can do. I am more than happy to purchase a book.

[June7]

Does this help http://msdn.microsoft.com/en-us/library/cc505839.aspx

[SolemnWishes]

June7,

Thanks for deeper into this. I stumbled on that article as well in my research before posting. Its very helpful in terms of theory, but it doesn't really talk about using it in terms of queries and forms. Unfortunately, that is where I need help most.

[June7]

Can't say I've ever had to build a db of this nature - I count myself lucky. Reading your OP again makes my head hurt.

The difference between assets and consumables is you expect the assets to come back, the consumables should be, well, consumed. But it seems you want to track return of unused consumables as well as return of assets. I am still thinking they should be tracked independently. What statistical analysis would consider them equally?

I don't think I can offer much else. Hopefully someone with more expertise and insight will jump in.

[orange]

You may get some ideas from this material. There are many "theory oriented articles" but few practical examples.
Find references to Celko and you will info to help you with decisions. Most examples deal with SQL Server or other database that has check constraints at the table level. You can look at some links and see if you can use/apply the approach to your application.

Powerpoint slides re Generalization/Specification Sub/supertype and more
Good luck

Let us know what you decide and how sub/supertype might apply.

[SolemnWishes]

June7,

Most of our consumables are highly unlikely to return per your intuition. However, we do collect empty acetylene tanks after our crews use them up. Treating the tanks as assets is not exactly viable, because we can't mark them as our gas vendor switches them out will full ones. In other words, empty tanks aren't refilled, but replaced. And also because I'm obsessive and want to cover possibilities of returning surpluses in the field.

As to statistical analysis, it's more about field leadership performance. Our crews have a tendency to make last-minute request that cripples our logistics department. But more importantly, their efficiency can be significantly affected or even zero without the proper supplies. Our crews are also notorious for "lost" assets. So I am hoping to create a database system that will track their order dates, lead times, and their assets. The aim is to create more transparency to improve performance. This theory of increasing transparency has shown improvement when we tested with a much more cumbersome and less detailed excel worksheet.

Lastly, I thought it might be about time this company had someone that understood Access.


Orange,

Thank you very much for your response. I have to agree that the internet has a lot of resources in theory of supertype and subtype, but not a lot of instructions of working with it, especially in Access. There is a bit more practical information for SQL Server like you said. But I think that is a bit out of my league at the moment. I'm actually a little surprised there isn't more information for Access, because supertype and subtype seems so fundamental to real-world scenarios where things are not always simple. Not even my 1,100+ page Microsoft Access 2013 Bible has anything about it.

I'm feeling pretty happy with my table structures at the moment, I just need some practical Access knowledge.


Again, thank you both for your time.

[June7]

I just noticed your older thread on related topic. https://www.accessforums.net/databas...ent-48829.html

[SolemnWishes]

I just noticed your older thread on related topic. https://www.accessforums.net/databas...ent-48829.html

Thank you for noticing! As you can tell it's a little disgusting.

But in regards to the main topic, I came up with a few ways to accomplish this per the table below:

Method	Description
Subforms	Create a subform with bound controls for the child table. E.g., my main form's control source is Supply and subform is Asset.
Unbound Controls	Add unbound controls to the Subform method for all bound controls within the subform. Hide subform. Then use VBA code to transfer values to subform.
All VBA	Use unbound form and DAO or ADO to record information.

I chose to use Unbound Controls method because it was the best balance between ease to code and foolproofness for my abilities.

Since my painful research through the internet did not provide guidance as to practical application of supertypes/subtypes, I'll go over each a little more. And hopefully, you guys might have some insight!

With the Subform method, which was my original idea, it was easy to setup the controls. Formatting is a bit more difficult because there isn't one unified layout table to align the controls. It takes a bit of hard input of sizes and positions. The biggest problem was saving. The goal is to have all fields completed to their individual requires, e.g. IsAsset has a Yes/No value. But Access will save the main form entry as soon as focus is switched to the subform. This means potential for bad data when forms are filled halfway. There are also a myriad of issues with invalid information, changing information, and basic command controls like Save, Undo, New... It's not that these can't be solve, but at my level, it is troublesome managing a form with a subform of this nature. And I am just too lazy to train end users how to avoid pitfalls.

So I turned to using the Unbound Controls method. You duplicate all bound controls in the subform onto the main form. It will cause an error because the control source property will have an invalid value, so you make them all unbound. Also you shrink the subform and set Visible property to No. Now, with some basic VBA, you can force the user to fill in all required fields before any kind of save. Upon validation, have VBA duplicate control values into the corresponding subform controls. And you can force the subtype table to always save immediately after the supertype table. This is applicable for other operations like Undo and New. Basically, you can avoid the background Access processes has with subforms and main forms. This is still not perfect in my opinion, because there are still a few rare contingencies that aren't covered.

I think these final contingencies can be covered if all types of operations like Save, Undo, New... are controlled through VBA with DAO or ADO. This is the last method, All VBA. Of course this takes a lot of knowledge in order to include all types of error handling. Frankly, I don't have the time table nor a foreseeable return in investing time to learn about Access to that depth. To begin with, I have Bachelors in Chemistry and Economics, not anything related to Computer Programming. I doubt anyone will use this method, because at that caliber the developer might as well use SQL Server coding and probably had classes covering supertype/subtype theory and application.


For now, I used this Unbound Controls method on just the Supply-Asset-Consumable-Supertype/Subtype entry form. As June7 mentioned, I still need to make transaction-related forms that have a supertype/subtype structure. So i will update if I run into new fun (absolute sarcasm) issues with this method. Again, I appreciate any kind of input. Thanks ahead!