Results 1 to 7 of 7
  1. #1
    ccla is offline Novice
    Windows 10 Office 365
    Join Date
    Feb 2021
    Posts
    8

    Filtering Many-to-Many relationships

    Hello everybody,

    I am trying to build a database to keep track of the pictures I take. I had experience with Access decades ago but did not use it to develop a database since then.
    I have no problem with developing the relationships among the tables and using queries to use as field lookups (I hope). What I am having problem with is limiting the choices based on a filter field.
    Just a little background to set up the example (this is a subset of the database that keeps track of the lenses, filters, and lens accessories). Generally, a lens comes in a particular mount that fits a particular brand of camera. Lens accessories may (or may not) be for a particular lens (for example an extension tube for macro photography that must have the same mount as the lens).
    Lenses similarly, have a thread diameter for filters and obviously filters come in different diameters to fit different sized lenses.


    As you can see from my relationships in the image I have a many-to-may relationships between the Lens and the LensAccessory tables and between the Lens and the Filter tables:

    Click image for larger version. 

Name:	DB Relationships.jpg 
Views:	28 
Size:	105.4 KB 
ID:	44143

    This works fine for selecting accessories and filters that work with a lens. To improve on this I would like when using the LensToAccessory and LensToFilter tables to list only the Accessories that have no specific mount or the same mount as the lens and to list only the filters that have the same thread diameter as the lens.
    I guess I can use VB in the form to set the query for the looked up field at run time, but I was wondering if there is an easier way to accomplish this.


    Bonus question: There are on the market thread adapters that step down the diameter of the thread so you can use bigger filters on smaller lenses. How would you implement this in the database?

    Any help or references that can help are appreciated.

    By the way I posted this post in the Queries forum, but if there is a better forum please feel free to move.

    claudio

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Answer to first question might be to use cascading combos if you want to see a B list that depends on what was chosen in A. The parent and child records need at least one factor in common. So if you were to pick a lens from the list, it's filter thread size data would dictate what filters you could choose because the filter list would also have thread size. I don't know if that helps with the adaptor issue or not.

    I've dabbled in photography over the years, teaching and professional wedding photography and the like but can't imagine worrying about such details in such great depth. Sure, I'd record settings and filtration in the field but can't imagine why I'd care what thread size the 85B filter was for the shot or what mount the lens was, especially since if I used a Canon, it obviously wasn't a Nikon mount.

    When you post in more than one forum please provide a link in both threads to the other.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    ccla is offline Novice
    Windows 10 Office 365
    Join Date
    Feb 2021
    Posts
    8
    Sorry about the double posting, but I think I only posted once. I do not even know how to post in two forums. Anyway if I did so by mistake, I apologize.

    Anyway, thank you for your suggestion. It really worked well.

    As for the details, I agree, overboard but what the heck it is an exercise to get back into DB design so I am going all out.

    thank you.

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I probably mixed you up with someone else, unless I saw your post in some other forum website.
    Glad I was able to help; good luck with your project!
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    A little confusing when you interchange Company with Make......
    IMHO, it would be better if every table has a PK field and use compound INDEXES instead of compound Primary Keys.
    Click image for larger version. 

Name:	Relationship.png 
Views:	13 
Size:	71.0 KB 
ID:	44221

  6. #6
    ccla is offline Novice
    Windows 10 Office 365
    Join Date
    Feb 2021
    Posts
    8
    Yeah, I will change all the field names so they match. May I ask what are the reasons to add an additional field for the PK of the linking table? Any resources that I can read on that?

    claudio

  7. #7
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    May I ask what are the reasons to add an additional field for the PK of the linking table?
    Every table should have a field that identifies a particular record. Sometimes actual data is used for this purpose, but it's generally considered that an autonumber set as the PK is better. Using real data as a PK can cause updating issues if a value has to be changed (e.g. after entering hundreds of records you realize you misspelled a value). An autonumber never has to change. A table with a compound index and no PK field has no unique identifier for any particular record so things become difficult or impossible at times. Some queries will not run because there is no unique identifier for a record. Having a pair (or more) of unique identifiers doesn't always cut it.

    Google the subject and perhaps read
    - http://www.utteraccess.com/wiki/Autonumbers
    - http://access.mvps.org/access/general/gen0025.htm
    - http://www.fmsinc.com/free/newtips/primarykey.asp
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 7
    Last Post: 08-05-2016, 11:53 AM
  2. Replies: 2
    Last Post: 07-27-2016, 10:25 AM
  3. Yes/No filtering
    By cgalvin in forum Reports
    Replies: 1
    Last Post: 05-11-2015, 05:59 PM
  4. Explicit Relationships and Implicit Relationships
    By Dazza666 in forum Database Design
    Replies: 2
    Last Post: 07-17-2013, 02:11 AM
  5. filtering
    By nashr1928 in forum Forms
    Replies: 12
    Last Post: 07-01-2010, 06:30 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