Results 1 to 12 of 12
  1. #1
    ConfusednoobinNJ is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2015
    Posts
    7

    Unhappy Database Question

    I am sorry in advance if this is not the proper forum for this topic.




    I am hoping someone on here can provide some structural and technical guidance for putting together a database in Access 2010.



    I am trying to pull together a database that tracks different awards my club has won. We want to include several fields:



    The year the award was won.

    The name of the award

    Type A tag for the award

    Type B tag for the award

    Type C tag for the award



    The tags will be fixed lists, so I want the user to pick from choices in each type.



    I was thinking the best way to approach this was to create one master table for data entry and then three other tables that would contain the tag lists. All of the tags are static and I want to make sure everyone who uses the data would be picking from the same values. At the same time, in the future, we may need to add additional tags to the respective lists, so I want to make it very easy and universal across the database. Therefore, I was thinking I would just use 3 lookup columns in the main table that point to the respective sub-tables. (I hope you are with me.)



    Then, I want to be able to create a form where we can basically pull up records based on user's data tag choices. I guess they are basically just functioning as filters. However, I want the filters to actually look at the values in the original tag tables, so all the possible choices show. Some tag records may also contain more than one tag, so i want to be able to search by individual tags.



    I hope I have explained myself well. I have been spending hours on this and I just am not making any progress. I'd really appreciate if someone out there on the net could swoop in and give me some guidance!



    Thanks!







  2. #2
    InsuranceGuy is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Aug 2015
    Location
    Colorado
    Posts
    126
    Sounds like you'll want a table for the awards and a separate one to associate awards to the tag list.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,870
    A few things to consider:

    What exactly will the database be about? Could you describe it in 4 or 5 lines in plain English?

    You have Awards and some attributes including the year in which the award was won.
    I have no ideas what a tag or 3 different Tags might be without some details.

    From a database perspective -- DO NOT use LOOKUPs at the Table field level.

    Here's a tutorial that will show you how to design and build a database from a clear description of the business involved. Work through the tutorial and you will learn. And you can use what you learn for any database.
    The key is to start with a clear understanding of WHAT your business is and the things involved and the relationships between them.

    You've probably seen this or similar, but here is a reference to Normalization that you should be familiar with.

    Good luck with your project.

  4. #4
    ConfusednoobinNJ is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2015
    Posts
    7
    Quote Originally Posted by orange View Post
    A few things to consider:

    What exactly will the database be about? Could you describe it in 4 or 5 lines in plain English?

    You have Awards and some attributes including the year in which the award was won.
    I have no ideas what a tag or 3 different Tags might be without some details.

    From a database perspective -- DO NOT use LOOKUPs at the Table field level.

    Here's a tutorial that will show you how to design and build a database from a clear description of the business involved. Work through the tutorial and you will learn. And you can use what you learn for any database.
    The key is to start with a clear understanding of WHAT your business is and the things involved and the relationships between them.

    You've probably seen this or similar, but here is a reference to Normalization that you should be familiar with.

    Good luck with your project.
    Thanks. I will give that a look. To put a finer point on it. I want to be able to look up records by their tags, for example by a region (one tag), by a participant level and so on. The amount of data I am tracking is really minimal. In fact, I thought about just doing this on a spreadsheet, but the only problem was that while I could could create a multiselect dropdown for some cells. I really need to be able to do look up these tags individual, and I think that is possible here. I just dont know how to set this up.

    Does that help at all?

  5. #5
    ConfusednoobinNJ is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2015
    Posts
    7
    We are calling it an Accolade Repository

  6. #6
    ConfusednoobinNJ is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2015
    Posts
    7
    We basically just want to tag all the awards we win in 3 different categories (some may attach to more than one) and be able to look them up by individual categories. Does that make sense?

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,870
    Your general structure would be along this line

    tblAward--->jncTaggedAward<--tblTag

    where:
    tblAward is a list of Awards along with any info you want to record that is specific to the Award
    eg.
    AwardUniqueIdentifier, NameOfAward, sponsor, purpose, yearAwarded,....

    tblTag is a list of all Tags along with info about each Tag (specific to the Tag) you wish to record
    eg

    TagID, TagName, TagDescription....

    and jncTaggedAward is a table identifying the Award and the associated Tag


    This set up should allow you to have several Awards and several Tags, and a record for each Award*Tag combination.
    Good luck with your project

  8. #8
    ConfusednoobinNJ is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2015
    Posts
    7
    Ok. Thanks. I think that is where I am now basically. I will just take the tags out of the award table and combine all the data (in a separate table?)


    Can anyone tell me how to integrate all this info, so it is searchable by the tags? That is what I am really struggling with.

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,870
    Find some youtube videos on intro Access (look for videos by 599cd)

    Richard has a complete set 00--12 starting at
    https://www.youtube.com/watch?v=hMzf6u8hWqk

    Watch the series to get an
    -overview of objects --tables, queries and forms
    -intro to queries

    Do some samples and get a little experience. You can rerun/stop/restart the tutorials.

  10. #10
    ConfusednoobinNJ is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2015
    Posts
    7
    I appreciate all the feedback I've received and I've been viewing the tutorials as well, but I really don't feel this is capturing what I want to do.

    Right now I have four tables: Awards (with fields year and award name) and Tags1, Tags2, and Tags3. The tags tables will contain only ONE field and I want that data to be ostensibly function like a drop down would where the user can select multiple tags in each field from this defined list.

    However, since you have all suggested I not add lookups to my table, I do not know how to integrate this setup. What am I missing here? How can I combine these tables to do what I am trying to do. This is for our high school debate team.

  11. #11
    ConfusednoobinNJ is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2015
    Posts
    7
    Was giving this more thought. Tell me if this is the right approach.

    In my AwardsTable. Fields are now AwardYear, AwardName, AwardTags1, AwardTags2...

    In my Tags[#] tables I still have only one field per table with several values.

    I now should create a form, on the form I create a list box that writes to the AwardTags1 field in the main table but whose values are sourced from the Tags1table.

    Is that right?

  12. #12
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,870
    No. When you have repeating fields AwardTags1, AwardTags2... it is a signal that says your tables are not normalized.

    A Tag is something that is related to an Award, so that is a separate idea/concept than Award. In database terms this new concept would be represented by a table.

    Do yourself a favor-- define(1-2 lines) Award and Tag, then create a few examples. It will help.

    I have attached a small mock up to help.

    Good luck.
    Attached Files Attached Files

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

Similar Threads

  1. Database Security Question
    By princess12 in forum Access
    Replies: 12
    Last Post: 04-04-2015, 06:57 AM
  2. Question about database
    By Candeh in forum Access
    Replies: 2
    Last Post: 03-11-2015, 05:42 AM
  3. Database design question
    By D347HxD in forum Database Design
    Replies: 3
    Last Post: 10-17-2013, 12:02 PM
  4. Replies: 2
    Last Post: 09-07-2011, 06:00 AM
  5. database and tables question
    By aaronlalonde in forum Database Design
    Replies: 0
    Last Post: 08-03-2009, 06:51 PM

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