Results 1 to 13 of 13
  1. #1
    aakkam22 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    7

    One-to-many Relationship

    Hi



    I am building an access database from scratch, to be used for asset tracking. I tried to use the asset tracking template to do this, but every time I changed something, it would break the database. So I'm building it from scratch. Here's how I have it set up:

    I have an "Assets" table, which includes the AssetTagNumber, Model, and AssignedUser fields. I have another "Users" table, which has an AssignedUser field and a Department field.

    I want to be able to use a form to select a user from the users table, and then view all the assets assigned to them. I read online about one-to-many relationships where I can link one user to multiple different assets. Which fields in the tables should I set this relationship for? And how do I then create a subform that will allow me to select a user's name from the "Users" table and view all the assets that they have assigned to them?

    I already have a form that can insert a new asset and a new user, but these forms are not linked together.

    My knowledge of access is basic, I am self-teaching myself everything. I have only been at this for a few days.

    Thank you for any help!!

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    the first thing to clarify is

    1. can an asset be assigned to more than one person?
    2. If so, at the same time or only one person at a time?
    3. and if so, do you need to keep a history of which person at any point in time?
    4. can a person have more than one asset assigned to them? (assumed yes)

    if the answer to 1, 2 or 3 is yes, you have what is called a many to many relationship and that is managed by what is called a junction table. What that junction table looks like depends on your answer to 2 and 3 but as a minimum will contain a foreign key to the asset primary key and a foreign key to the user primary key.

    If the answers are no - i.e. the asset is only every assigned to one person at a time and you are not interested in the assignment history then your asset table needs a foreign key to the user primary key

  3. #3
    aakkam22 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    7
    Thanks for the reply Ajax.

    Each asset may will have one and only one owner. The only other possibility would be its status is "awaiting deployment" and not assigned to anyone.

    Keeping a history of owners would be a nice feature to have later, but its not necessary at the moment since I'm just looking for establishing basic functionality.

    And yes, an owner must be able to have more than one asset assigned to them. For example, someone is assigned a laptop and 2 monitors, for a total of 3 assets.

    Any help on the field relationships? I want to have a list of everyone in the organization, and by clicking on their name be able to view all assets assigned to them. Would I do that through a subform?

  4. #4
    aakkam22 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    7
    Inventory.accdb

    Here is the file I'm working on, it only lays out the fields I have in each table.

    How do I tie the "AssignedUser" field in the assets table to the users table since the users table is first and last name on separate fields?

    Thanks for all your help

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    so based on

    Each asset may will have one and only one owner.
    And yes, an owner must be able to have more than one asset assigned to them
    you need a userFK field in the asset table which links back to the userPK field in the user table

    Would I do that through a subform?
    can do, but depends on your form design - you might have a continuous form based on the assets table and select a user from a combo dropdown or listbox in the form header for example

  6. #6
    aakkam22 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    7
    How do I keep the names split into first and last name and use a primary key on them?

    I don't quite understand how to link the fk and pk together

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    you need an autonumber PK in each table - standard practice for databases

    and the asset table also needs a userFK field of type long

    I've modified your db - see attached

    the relationship does not have referentiality enforced because you can have assets not allocated to any user
    Attached Files Attached Files

  8. #8
    aakkam22 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    7
    Here is what I have now (this is the database I created, not the one you updated because I want to learn how to do it myself) How do I get the Assigned User field in the assets table to display the first AND last name? do I need to run a query to combine both fields from the users table and then point the lookup wizard to the query?

    EDIT: I also read online that it's not recommended to use a lookup field on the table level and I should instead use that on my forms. Is that a problem?

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    EDIT: I also read online that it's not recommended to use a lookup field on the table level and I should instead use that on my forms. Is that a problem?
    yes

    How do I get the Assigned User field in the assets table
    you don't - tables are for storing data in its raw form, not displaying it. You use your query, form or report to display data

    do I need to run a query to combine both fields from the users
    yes but the query can be part of your form query or a rowsource to a combo. Or you can use the dlookup function. depends on what you want to do.

    I don't download files from anywhere other than as an attachment here - it is recommended you compact the database then zip it before attaching

  10. #10
    aakkam22 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    7
    Inventory.zip

    OK, here is the compacted/zipped database. I'm working on adding a form right now - what's the difference between a form with subform and a linked form?

  11. #11
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    What's the difference between a form with subform and a linked form?
    don't know, not heard the term 'linked form' before

    with regards the attachment, what do you want me to say?

    you have asset number as text, but populating with numbers. You have not added an autonumber PK
    text does not make a good primary key - it can be changed and although you don't have any other tables right now, when you do, you will experience problems
    you are using lookups in tables - already you are building complexity by giving the field a name based on what you see, not what it actually is
    you will be much better off maintaining a standard terminology, particularly regarding PK and FK - AssignedUser - UserID, either give them both the same name (userID) or identify which end of the join the field is (UserPK, UserFK)
    at some point you are going to have lookup tables for departments and cubicles - maybe even relating them

    numbers take up less space that text - a long number takes up 4 bytes, a 3 character text field takes something like 13. This matters as the amount of data grows - searching on text will take a lot longer
    keeping it simple means you will make fewer mistakes further down the line, so be clear about your naming convention. Don't jump in head first, learn about normalisation, design your tables and relationships on paper before even opening access. Talk to whoever about what the app is required to do now and what it might be expected to do in the future, otherwise you will probably need to throw it all away and start from scratch

    A good thing is you are not using spaces in field names - the amount of time that wastes in coding errors just so the label caption does not need to 'corrected'. It is also good you want to go through the process yourself, rather than being happy to be spoonfed with solutions, so you get a better understanding

    I know it's easy for me to say, I've been building databases for many years. By all means, learn by your mistakes (believe me you will make plenty, I certainly have over the years) but do take on board the advice given

  12. #12
    aakkam22 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    7
    Thank you very much for your help Ajax, I worked all of yesterday on the db and it's starting to get where I want it. It doesn't look pretty yet but that's the last thing I will address. I attached the latest revision I made. Can you help me with another thing now?

    When the "AddAsset" form is opened and a name is typed in the "assigned user" combo box that isn't in the list, the form "adduser" opens to allow a new contact to be added. Is there a way to prepopulate the "adduser" form that opens with the first and last name that was typed in the combo box? That way, the user doesn't have to retype the value that they had just typed in the combo box.

    inventory.zip

    Thanks again

  13. #13
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    there is no code in your attachment relating to 'not in list'

    basically in the not in list event you would need code to open the adduser form, passing via the openargs parameter the value typed in the combo box.

    then in the adduser form open event you would need code to break this down into firstname/lastname. How you do this depends on how users enter the data so you will need to cater for all eventualities

    you might want to use the split function to split on space


    something like

    firstname=split(trim(openargs)," ")(0)
    lastname=trim(replace(openargs,firstname,""))

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

Similar Threads

  1. One to One Relationship
    By caniread in forum Database Design
    Replies: 8
    Last Post: 02-07-2017, 05:43 AM
  2. A little help with relationship
    By stanislav in forum Database Design
    Replies: 3
    Last Post: 12-24-2016, 06:47 AM
  3. Replies: 3
    Last Post: 03-19-2015, 05:26 PM
  4. Replies: 1
    Last Post: 12-06-2014, 12:49 PM
  5. Relationship 1:1
    By MrLestat in forum Database Design
    Replies: 1
    Last Post: 05-18-2011, 07:13 AM

Tags for this Thread

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