Results 1 to 7 of 7
  1. #1
    ArthurDent is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    4

    Most efficient data entry form for many to many relationship.

    Hi,



    I'm working on a database for a teacher training programme of which I'm a student. The basic idea is an asana (yoga pose) database based on our training material which stores all associated information, including cues, contraindications, modifications and so on. So far I'm happy with the structure. Ultimately this will be an interactive reference, a class planner and a quiz generator.

    I've created tables for the attributes described and a joining table to link them together with referential integrity. Most of them take the form:

    Asana: (ID,Name, Picture...), Asana Risks: (AsanaID,RiskID), Risks (ID,Text)

    My Cues relation has an extra field 'type' and a table which holds the types.

    Asana: (ID,Name,...) [1 - n] Asana Cues: (AsanaID, CueID) [n - 1] Cues: (ID,Text, TypeID) [1 - n] Cue Types: )ID, Type Name)


    This seems to work fine for my purposes. Where I'm scratching my head a little is how to build a form or forms to enter all this data effectively. I've looked up a bunch of examples online and they seem to take the same format with a pre populated database linking records together.

    I'll lead with the Cues example as that's the slightly more complicated one. So it's easy enough to build a Form based on the Asana table and have a subform based on Asana Cues which uses Cue ID to look up the Text field from Cues and display in a dropdown box. I have a separate form which allows me to input Cues so Text and Type based on a lookup from the Cue Type table. This requires a few steps, I need to open the Cues form, add the new data, go to the asana form (refresh it if it's already open) then link the newly added Cues to the Asana. This is a little clunky and I'd like to streamline this as I would like to share this database with my fellow students and some are not so tech literate.

    So my question is what is the most effective/ efficient way to design this form? I would, if possible, like to enter data on one form; is this possible with a well structured query or VBA?

    I can provide a copy of the database if thats more useful.

    Thanks for any help!

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You might want to put the brakes on for a bit. There are a few things in your post that tell me that you might need to post a db (or at least a pic of your relationships window) for analysis. I'm no yoga expert, but I'm not seeing a need for a linking table at the moment. What I suspect is that you are starting with some bad habits:
    - about naming things. You seem to be using reserved words (Type, Name) and allowing spaces in object names - maybe special characters too. Also ID is a poor name for a field because you will find that you end up with a lot of ambiguity.
    - maybe you plan on storing pictures in the db (you seem to have a field by that name) - usually not a good idea
    - if you don't need the linking table, it might mean you need to brush up on database normalization

    There are lots of web pages about these topics and might prove beneficial to your project. Naming conventions are like opinions - everybody has one. Find one that you like but don't necessarily settle on the first one you find. Here are starting points:

    https://www.devhut.net/2017/12/21/naming-conventions/
    http://access.mvps.org/access/general/gen0012.htm

    I had a lot of good links on such topics but for now, they were all lost when my hard drive died, so you're on your own unless someone chirps in. Sorry.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    ArthurDent is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    4
    Hey thanks, to be fair I've abbreviated a few names with the intention of not bogging down the post, clearly that's backfired. Yes I'm planning to store small images, I'm only going to have around 70 records total it might not get much further than 20 depending on scope certainly for prototyping. I've looked into options regarding this and I appreciate the cost/benefits. If it becomes a problem I can always switch this to storing the path to a local file which seems to be the most recommended approach. For now I want to keep the project self contained for ease of sharing.

    Re. the normalisation I'm doing as much as I remember from college/uni (it was a long time ago!). Basically there are a bunch of many to many situations with the info we have in our manuals which are short bulleted lists of text. There's a fair amount of overlap. Cues is a great example because many poses will have many cues. Many cues will be relevant for many poses. So it makes sense to have a link table. Some of the others I mentioned, could be construed as a one to many, it would simplify things a bit but my initial reasoning (which I'm starting to question) is again there can be a fair amount of overlap and reductions in redundancy simplify things overall. And once I have a good solution working with this pattern I can use it as a cookie-cutter type approach to some of the other info.

    I certainly appreciate any advice on best practise. As I mentioned it's been a long time since I've done this sort of thing.


    Heres a screenshot of the relationships

    https://drive.google.com/file/d/1OH_...ew?usp=sharing

  4. #4
    ArthurDent is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    4
    Hey thanks, to be fair I've abbreviated a few names with the intention of not bogging down the post, clearly that's backfired. Yes I'm planning to store small images, I'm only going to have around 70 records total it might not get much further than 20 depending on scope certainly for prototyping. I've looked into options regarding this and I appreciate the cost/benefits. If it becomes a problem I can always switch this to storing the path to a local file which seems to be the most recommended approach. For now I want to keep the project self contained for ease of sharing.

    Re. the normalisation I'm doing as much as I remember from college/uni (it was a long time ago!). Basically there are a bunch of many to many situations with the info we have in our manuals which are short bulleted lists of text. Some of these concepts go deeper which I'll represent in time. There's a fair amount of overlap. Cues is a great example because many poses will have many cues. Many cues will be relevant for many poses. So it makes sense to have a link table. Some of the others I mentioned, could be construed as a one to many, it would simplify things a bit but my initial reasoning (which I'm starting to question) is again there can be a fair amount of overlap and reductions in redundancy simplify things overall. And once I have a good solution working with this pattern I can use it as a cookie-cutter type approach to some of the other info.

    I certainly appreciate any advice on best practise. As I mentioned it's been a long time since I've done this sort of thing.


    Heres a screenshot of the relationships

    Click image for larger version. 

Name:	asana db diagram.png 
Views:	31 
Size:	57.2 KB 
ID:	42969

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Post 3 was moderated, I'm posting to trigger email notifications.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    So as to not go off the rails with this, I reviewed your first post and IMHO, it's not uncommon to have a form/subform relationship and then to facilitate adding a primary record, having a popup form to add that record, then close it and go back to the main form (which gets requeried) so as to add new related "many" records. If that's your concern about something being 'clunky' I wouldn't be concerned about it and would say that you are likely on the right track.

    As for the relationships you show, without a thorough grasp of which are entities and what their attributes are, it's impossible to say much. At this point, I don't understand too much about the situation and its jargon. What I would caution against is the use of multi value fields beyond your decision to store images. Since they are objects, and that type of field is a multivalue field, I expect to see that + sign beside the name. I wouldn't typically use those field types, but that's a personal decision. I'm just going to say that you might want to research them before using mv fields in any other fashion.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    ArthurDent is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    4
    Great thanks, yeah in this case I was looking at using the 'attatchment' type as an alternative storing path and handling the images that way. Can still change that it's just to get something visually working to help me as I go along as some things I may have to redesign. It's not a huge project so I'm happy to experiment with my design. Good to know I'm going in the right direction.

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

Similar Threads

  1. Replies: 6
    Last Post: 03-30-2017, 06:55 AM
  2. Replies: 6
    Last Post: 12-28-2014, 08:41 PM
  3. Replies: 5
    Last Post: 03-09-2014, 07:16 PM
  4. Making data entry more efficient in a form
    By Gambit17 in forum Forms
    Replies: 4
    Last Post: 08-02-2013, 10:24 AM
  5. Replies: 1
    Last Post: 03-09-2013, 07:25 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