Results 1 to 2 of 2
  1. #1
    Beachbum808 is offline Novice
    Windows 10 Office 365
    Join Date
    May 2020
    Posts
    4

    Design / relationship issues with small database

    Aloha,

    It has been over 20 years since I have done much with Access, but in the past I was able to create a fairly complex, and complete, Property Management (split and multi-user) application...with only the built in tools, wizards, and a couple macros...no code. (Used it very successfully and trouble free for over 5 years before leaving the company.) Now I find myself going in circles without success, and would like some help/direction. Too many years...or too much Rum!

    This project is a relatively small, personal, home inventory system. The purpose is to document details, including family history, of each Object. I do not plan on including cost or values. I am dealing with something less than 1000 Objects, with 2 - 4 images max for each. MOST will only have 1 image. I don't believe this will cause issues due to size, but I can reduce the size of images if necessary. Alternatively, I need to figure out how to have all the image files in a folder, separate from the database, that the database could find and link to for just the images. Part of the problem here is that I am creating the database app to be used by other family members after my demise, so there would need to be a foolproof way to keep the database and the image files connected when copied to another computer. The project really needs to be free-standing.

    The attached file has only six tables, with what I believe are the correct relationships established (yes, no?). I would like to have a single form for the entry of new Objects, with editable lists (via separate forms?) for entry of Category, Circa, Family, and Source. The tables for these do not have ID field, as each list would have strictly unique entries (correct?). If possible, I am thinking of a subform on this main form to add image attachments, but I am not sure if I could add more than one image for a specific Object. I definitely want to see, or be able to scroll through, all images for the specific Object on this main form. I am open to suggestions on best way to handle this important aspect. ALL object info (for each individual object) needs to be able to be seen, edited, or added on this main form.

    To clarify, there will be many Objects, but each Object will have only one Category, one Circa (approximate year acquired or created), one Family, and one Source (specific family member). Each Category will have many Objects; each Circa will have one or more Objects; each Family will have one or more Objects; and each Source will have one or more Objects. Objects may have none, 1, or many images.

    The problems I keep running into are that the lists cannot be added to or edited. I was sure that in the past I used combo boxes which would show the drop down list, but still allow you to add a new entry if needed. I do not know if I am trying to put the control (or the fields) in the wrong place (table vs form) or not getting the precise relationship that is required for some reason. Or perhaps it is something simple that I need to change in control properties? (I actually would like to do initial bulk data entry directly into the TblObjects, but the main form only is acceptable.)

    I previously had great success utilizing the switchboard manager to create the main navigation form and opening page for the completed app. In this case, I would expect to have add/edit buttons for each of the lists mentioned, as well as for the main data entry form. There would also be a choice of Reports including Objects by Family; Objects by Source; Objects by Category; and All Objects. I would LIKE to see all images for each Object in the Reports. Obviously I would set this up only once the tables, forms, and reports are fully functional.



    Typically I initially set up forms using the form wizard, then go into design and/or layout to pretty them up...
    Same goes for reports, I have simple needs, but do spend some time fine tuning the output. I just need the database structure to work properly...

    Any help/guidance greatly appreciated. I have had several false starts with this project over the past few years, but am at a point that I NEED to get it done.

    Mahalo,

    Richard
    Attached Files Attached Files

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    some general comments

    1000 Objects, with 2 - 4 images max for each. MOST will only have 1 image. I don't believe this will cause issues due to size
    maybe, maybe not, but the accepted design principle is to store the image in a folder and have the path stored in the db.

    so there would need to be a foolproof way to keep the database and the image files connected when copied to another computer
    put your db in a folder called say 'family history' and then have a subfolder called 'images'. You can get the path to database (currentdb.name) then modify to get to the images folder. So providing a family member copies the family history folder, they will copy the images folder as well.

    for entry of Category, Circa, Family, and Source. The tables for these do not have ID field, as each list would have strictly unique entries (correct?).
    possibly, providing names don't change in which case you need to set relationships to update on change. Probably not an issue since data volumes would appear to be quite low, but indexed text will be slower and take more space than numbers.

    am not sure if I could add more than one image for a specific Object.
    use a child (foreign) table

    The problems I keep running into are that the lists cannot be added to or edited.
    check out the limit to list property for combos and list boxes

    utilizing the switchboard manager to create the main navigation form
    personally I would build my own - check out here and other forums but we see lots of people struggling using the navigation form when they want something a bit different

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

Similar Threads

  1. Best way to design this small database
    By Angiec in forum Database Design
    Replies: 6
    Last Post: 01-08-2016, 03:14 PM
  2. Replies: 4
    Last Post: 11-03-2014, 08:36 AM
  3. Small Database Design Problem
    By PalmerR in forum Access
    Replies: 4
    Last Post: 07-30-2014, 11:53 AM
  4. Help to design a small database for 30 Employees
    By awan-trainings in forum Access
    Replies: 2
    Last Post: 05-19-2013, 11:13 AM
  5. Form Design / Relationship Issues
    By brharrii in forum Forms
    Replies: 5
    Last Post: 06-23-2012, 11:37 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