Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login
Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Tek-Tips LinkedIn® Group

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site
Partner Button
(Download This Button Today!)

Feedback

"...It's fun to see others going through the same stuff I did and be able to help. It's also a way for me to stay sharp and not lose the stuff I've learned..."
More...

Geography

Where in the world do Tek-Tips members come from?
Click Here To Find Out!

Tek-Tips Shirts!

Get Tek-Tips Swag
Get your
Tek-Tips Forums
SWAG here!
Home > Forums > Programmers > DBMS Packages > Microsoft: Access Tables and Relationships Forum

Using Key Tables to Manage Relationships?

thread700-1654058
Read More Threads Like This One
misscrf (TechnicalUser)
6 Jul 11 15:15
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:

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

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

misscrf (TechnicalUser)
6 Jul 11 16:19
I realize that this is a many to many relationship question.  I am researching to understand.

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

Read More Threads Like This One

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To Microsoft: Access Tables and Relationships

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close