Combine Multiple Tables

[Chrisss]

My wife and I have a Ministry and I’m trying to create a basic DAM (Digital Asset Management) db. I used to do a great deal of db work but haven’t touched it in 6 years and it is amazing how rusty I am.

I need to track 3 basic types of Assets: Video, Audio only, Documents; each Asset Type has very different characteristics. And these Assets may be combined in different ways into what I call Projects. A one Project may have 15 different Assets combining several Video, Audio and Document Assets. Another Project may only have a single Document and no Video or Audio. The Assets may be reused in several different Projects.

So, Projects have many Assets and Assets have many Projects. I don’t have any trouble with the Assets for Projects join that is a simple many-to-many table. My trouble is trying to get the 3 different Asset Types unified into a single table. I’ve worked for days and tried just can’t figure this out, any help is appreciated.

tblVideoAsset -> VidAsstID_PK, ResolutionID_FK, FrameRateID_FK, CodecID_FK & others

tblAudioAsset -> AudAsstID_PK, BitRateID_FK, FormatID_FK, SampleRateID_FK, LanguageID_FK & others

tblDocumentAsset -> DocID_PK, WordCount, Synopsis, LanguageID_FK



tblAssets -> AssetID_PK, AssetTitle, AssetDate, ????

Among many other things, I tried creating a tblTYpe -> TypeID_PK, Type (Audio, VIdeo, Document) then
tblAsset-Type -> TypeID_FK, Asset_ID and tried joining each of the various Asset tables to it with Type but that didn't work

tblProject -> PrjID_PK, PrjName, PrjDate



tblAssets4Projects -> PrjID_FK, AssetID_FK (creating a compound PK)

 

[theDBguy]

Hi. Welcome to AWF!

You have two options. Either use one table with potential null fields, due to non-applicable attributes for each asset, or you can look up the EAV (entity-attribute-value) approach.

 

[Uncle Gizmo]

How about having a main form with the basic asset details and a subform displaying the attributes something like attached:-

 

[Uncle Gizmo]

subform displaying the attributes something like attached:-

One of the main bugbears with this approach is that in this simple form the operator needs to select each of the attributes from a combobox. However, this can be done automatically by using my checklist code which is explained on my website here:-

Check List Extras - Nifty Access

Check List Extras I have two video series on YouTube which explain how to build Check-Lists in Microsoft Access. The first series shows how to build a single checklist and the second series of videos expands on the information provided for the single checklist, and demonstrates how to add...

www.niftyaccess.com

Along with several examples, one in particular which would be applicable in your case..