Using Key Tables to Manage Relationships?
Using Key Tables to Manage Relationships?
(OP)
I am dealing with a new type of schema that was recommended to me because of the possibilities of many to many relationships between each table of information.
To give a summary - We have projects. Each project will have assets or media that we received. Each asset can be a different asset type, such as hard drives, CDs, DVDs etc.
Each asset gets a unique asset tag comprised of the project id (intematter) a sequential counter with a 00001 padding, and the asset type. so an example would be 123456_00001_HDD.
We process each asset into a tool call Nuix. We may create multiple nuix cases in a case, and each asset may be processed into multiple pieces. This allows us to split 1 hard drive into multiple sets of evidence, based on assigning portions of a drive to different people or departments.
Sorry for the long description, but I have read that the better the explanation, the better people will be able to help out.
This is the full table list:
It was recommended that I have (for instance) the tblHBCase table and to link tblCaseAssets, I have a tblKeyHBCaseAssets table to link them. Normally I would have an FK in tblCaseAssets to the tblHBCase PK. The issue is that 1 asset could be used in multiple cases. For this reason, I need to be able to link any case to any asset at any time.
I am trying to create the front end forms in Access. I don't understand how to update the key tables and link the joining tables based on the key table. Does this make sense?
I have a link to an image of the schema too, in case that helps. The backend tables are in SQL.
Thank you for any help!
To give a summary - We have projects. Each project will have assets or media that we received. Each asset can be a different asset type, such as hard drives, CDs, DVDs etc.
Each asset gets a unique asset tag comprised of the project id (intematter) a sequential counter with a 00001 padding, and the asset type. so an example would be 123456_00001_HDD.
We process each asset into a tool call Nuix. We may create multiple nuix cases in a case, and each asset may be processed into multiple pieces. This allows us to split 1 hard drive into multiple sets of evidence, based on assigning portions of a drive to different people or departments.
Sorry for the long description, but I have read that the better the explanation, the better people will be able to help out.
This is the full table list:
CODE
Table Field
tblCustodian PKCustodianID
tblCustodian txtCustodianFull
tblCustodian txtCustodianAlias
tblProcessingStatus PKProcessingStatusID
tblProcessingStatus txtProcessingStatus
tblHBCase PKHBCaseID
tblHBCase txtHBCaseName
tblHBCase intCMatter
tblHBCase intEMatter
tblAssetType PKAssetType
tblAssetType txtAssetType
tblAssetType txtAssetTypeDesc
tblCaseAssets PKCaseAssetID
tblCaseAssets FKAssetType
tblCaseAssets intAssetNo
tblCaseAssets txtAsset
tblCaseAssets dtDateCollected
tblCaseAssets txtCollectedLocation
tblCaseAssets txtCollectedFrom
tblCaseAssets txtPCName
tblCaseAssets intMailstoreCount
tblCaseAssets txtSourceDesc
tblCaseAssets txtSerialNumOrig
tblCaseAssets txtSerialNumCopy
tblNuixCase PKNuixCaseID
tblNuixCase txtNuixCaseName
tblKeyHBCaseProcessing PKHBCaseProcessingKeyID
tblKeyHBCaseProcessing FKHBCase
tblKeyHBCaseProcessing FKProcessing
tblKeyNuixProcessing PKNuixCaseProcessingKeyID
tblKeyNuixProcessing FKNuixCase
tblKeyNuixProcessing FKProcessing
tblKeyAssetProcessing PKAssetProcessingKeyID
tblKeyAssetProcessing FKCaseAssets
tblKeyAssetProcessing FKProcessing
tblKeyAssetProcessing intNextEvidence
tblKeyHBCaseAssets PKHBCaseAssetKeyID
tblKeyHBCaseAssets FKHBCase
tblKeyHBCaseAssets FKCaseAsset
tblKeyHBCaseAssets intNextCaseAsset
tblProcessing PKProcessingID
tblProcessing FKCustodian
tblProcessing txtNuixEvidenceFileBatch
tblProcessing txtProfileDirectoryName
tblProcessing FKProcessingStatus
tblProcessing dtDateProcessed
tblCustodianAlt PKCustodianAltID
tblCustodianAlt FKCustodian
tblCustodianAlt txtCustodianAlt
tblCustodian PKCustodianID
tblCustodian txtCustodianFull
tblCustodian txtCustodianAlias
tblProcessingStatus PKProcessingStatusID
tblProcessingStatus txtProcessingStatus
tblHBCase PKHBCaseID
tblHBCase txtHBCaseName
tblHBCase intCMatter
tblHBCase intEMatter
tblAssetType PKAssetType
tblAssetType txtAssetType
tblAssetType txtAssetTypeDesc
tblCaseAssets PKCaseAssetID
tblCaseAssets FKAssetType
tblCaseAssets intAssetNo
tblCaseAssets txtAsset
tblCaseAssets dtDateCollected
tblCaseAssets txtCollectedLocation
tblCaseAssets txtCollectedFrom
tblCaseAssets txtPCName
tblCaseAssets intMailstoreCount
tblCaseAssets txtSourceDesc
tblCaseAssets txtSerialNumOrig
tblCaseAssets txtSerialNumCopy
tblNuixCase PKNuixCaseID
tblNuixCase txtNuixCaseName
tblKeyHBCaseProcessing PKHBCaseProcessingKeyID
tblKeyHBCaseProcessing FKHBCase
tblKeyHBCaseProcessing FKProcessing
tblKeyNuixProcessing PKNuixCaseProcessingKeyID
tblKeyNuixProcessing FKNuixCase
tblKeyNuixProcessing FKProcessing
tblKeyAssetProcessing PKAssetProcessingKeyID
tblKeyAssetProcessing FKCaseAssets
tblKeyAssetProcessing FKProcessing
tblKeyAssetProcessing intNextEvidence
tblKeyHBCaseAssets PKHBCaseAssetKeyID
tblKeyHBCaseAssets FKHBCase
tblKeyHBCaseAssets FKCaseAsset
tblKeyHBCaseAssets intNextCaseAsset
tblProcessing PKProcessingID
tblProcessing FKCustodian
tblProcessing txtNuixEvidenceFileBatch
tblProcessing txtProfileDirectoryName
tblProcessing FKProcessingStatus
tblProcessing dtDateProcessed
tblCustodianAlt PKCustodianAltID
tblCustodianAlt FKCustodian
tblCustodianAlt txtCustodianAlt
It was recommended that I have (for instance) the tblHBCase table and to link tblCaseAssets, I have a tblKeyHBCaseAssets table to link them. Normally I would have an FK in tblCaseAssets to the tblHBCase PK. The issue is that 1 asset could be used in multiple cases. For this reason, I need to be able to link any case to any asset at any time.
I am trying to create the front end forms in Access. I don't understand how to update the key tables and link the joining tables based on the key table. Does this make sense?
I have a link to an image of the schema too, in case that helps. The backend tables are in SQL.
Thank you for any help!
misscrf
It is never too late to become what you could have been ~ George Eliot
Talk To Other Members
RE: Using Key Tables to Manage Relationships?
It looks like the subform needs to have a query that links to the key table. I tried having the subform control source contain the main form table, the key table and subform table, but it didn't like that, so I took the mainform table out.
Now I can create a new main record ( a new HBcase). When I try to complete creating a new CaseAsset record, I get told "The Microsoft Jet database engine cannot find a record in the table "tblCaseAssets" with a key matching field(s) 'FKCaseAsset'".
So I am thinking that when I start a new record, I have to update the FKCaseAsset from the key table with the new PKCaseAssetID that has just been generated. Problem is, there can't be a new record for the query without the FKCaseAsset and that won't exist until there is a PKCaseAssetID generated.
I am confusing myself here! lol
Any help is appreciated!
misscrf
It is never too late to become what you could have been ~ George Eliot