Results 1 to 8 of 8
  1. #1
    Ian Barton is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Aug 2020
    Location
    Adelaide, South Australia
    Posts
    4

    Relationships advice

    I am just getting back into MS Access again after a 20yr break and I have forgotten so much.

    I am putting together a simple database just to play with in an attempt to try and refresh my brain a bit and need some assistance setting up relationships. The database will be of a Club Membership which has assets which can be loaned to the Members. This database will keep a record of all assets and which assets are on loan to members.

    I am planning to have 2 tables to start with. These will be a Membership Table "tblMember" and an Asset Table "tblAsset".



    The table tblMember will have the fields fldMemberID (Primary Key), fldLastName, fldFirstName, fldAge, fldAsset. The table tblAsset will have the fields fldAssetID (Primary Key), fldAsset.

    I expect I will have to create a relationship between these two tables. The questions in my mind are:-
    What relationship(s) do I create? These are the options I am considering and I have no idea which one to use and why.
    Will it be tblMember:fldMemberID to tblAsset:fldAssetID or
    will it be tblMember:fldMemberID to tblAsset:fldAsset or
    will it be tblMember:fldAsset to tblAsset:fldAsset?

    Will the relationship be "One to Many" or "One to One" and if it is "Many" is the "Many" end at the tbleAsset or at the tblMember.

    Also, I am assuming I need to create a Primary Key in tblAsset. Is that correct?

    These are simple questions I appreciate but I just need to reestablish some basics before moving on.

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    So members can borrow many items and items can be loaned to many members. A many to many relationship which would be represented by a table in which each record would have the Pk of the member and the Pk of the item.
    EDIT:
    Below is a link to a post in which one of our members called Orange gives no end of useful links for those just starting with Access or needing to refresh
    https://www.accessforums.net/showthr...133#post352133
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you need to describe the relationship between members and assets.

    As described from your table design, many members can have the same asset at the same - and only one asset.

    think you need a joining table and lose the fldAsset in the member table

    tblMemberAssets
    MemberAssetPK
    MemberFK
    AssetFK
    DateLoaned
    DateReturned


    you may need rules to ensure an asset cannot be loaned out if already on loan - indicated by using something like Dcount("*","tblMemberAssets","DateReturned is null AND AssetFK=" & me.assetFK)>0 on a form

    and perhaps members cannot borrow more than one asset at a time - indicated with something like indicated by something like Dcount("*","tblMemberAssets","DateReturned is null AND MemberFK=" & me.MemberFK)>0

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Ian,

    There are several data models at Barry Williams' site. Here is one related to IT Assets, but can easily be edited to represent any Asset and Member(rather than Employee).


    More info on Asset Management at that site.

    Good luck with your project.

    Thanks Bob for the kind words and noting the reference page.
    Last edited by orange; 08-28-2020 at 06:34 AM. Reason: spelling

  5. #5
    Ian Barton is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Aug 2020
    Location
    Adelaide, South Australia
    Posts
    4
    Ajax
    Thanks for your reply. You have suggested several things I hadn't thought of such as flagging that any given asset can only be loaned once and also the dates associated with that loan.
    Ian

    Quote Originally Posted by Ajax View Post
    you need to describe the relationship between members and assets.

    As described from your table design, many members can have the same asset at the same - and only one asset.

    think you need a joining table and lose the fldAsset in the member table

    tblMemberAssets
    MemberAssetPK
    MemberFK
    AssetFK
    DateLoaned
    DateReturned


    you may need rules to ensure an asset cannot be loaned out if already on loan - indicated by using something like Dcount("*","tblMemberAssets","DateReturned is null AND AssetFK=" & me.assetFK)>0 on a form

    and perhaps members cannot borrow more than one asset at a time - indicated with something like indicated by something like Dcount("*","tblMemberAssets","DateReturned is null AND MemberFK=" & me.MemberFK)>0

  6. #6
    Ian Barton is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Aug 2020
    Location
    Adelaide, South Australia
    Posts
    4
    Bob

    Thanks for this information. I will need to go through it and pick out what I need. I have always found it difficult to set up tables and fields and relationships after a 20yr break I am not finding it any easier.

    Ian

  7. #7
    Ian Barton is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Aug 2020
    Location
    Adelaide, South Australia
    Posts
    4
    Orange

    Thanks for that. I hadn't envisaged using 4 tables for the assets. I had envisaged using just a single table but I will check out what you have supplied to see if I need to get that detailed.

    Ian

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Might look at MS Lending Library database template.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Need your advice
    By As226 in forum Access
    Replies: 6
    Last Post: 03-18-2019, 11:54 AM
  2. Replies: 7
    Last Post: 08-05-2016, 11:53 AM
  3. Replies: 2
    Last Post: 07-27-2016, 10:25 AM
  4. Explicit Relationships and Implicit Relationships
    By Dazza666 in forum Database Design
    Replies: 2
    Last Post: 07-17-2013, 02:11 AM
  5. Need some advice and help
    By winterh in forum Access
    Replies: 9
    Last Post: 04-18-2012, 06:41 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums