0

Data Model Question. Multiple table reference entries?

I've created a paint database for paints applied in or on my house. I made two tables; one for the paint info, and one for the paint location. Some paint colors are used in more than one place. Is there a way to set up the table reference so I can apply the paint to multiple locations?

Screen Shot 2019-07-13 at 9.08.21 AM

Screen Shot 2019-07-13 at 9.08.21 AM

11 replies

null
    • Mr_K
    • 4 yrs ago
    • Reported - view

    This should be the second image...

    Screen Shot 2019-07-13 at 9.08.36 AM

    • Mr_K
    • 4 yrs ago
    • Reported - view

    You can see that I have tried to resolve this by adding a second table reference of Where Applied/Used.

    • Mconneen
    • 4 yrs ago
    • Reported - view

    @Mr K... First.. Thank you for using Benjamin Moore paints.   My wife's Uncle managed a manfuacturing plant outside of Chicago, IL.. :)   He has since retired, so continued support helps his pension.. :)   

    You could create a Many to Many relationship (A Paint may be used in many rooms.. and a room may have many paints) ...  So I created a table called Paint Location ..  

    Many To Many

    And from Paint.. it looks like.. 

    paint

    You could also add a "constraint" to prevent you from adding the same paint color to the same location more than once.. :) 

    Happy Ninox-ing!

    • Mr_K
    • 4 yrs ago
    • Reported - view

    Thanks Mconneen. I have some other things to do today, but I will work on this later.

    • Mr_K
    • 4 yrs ago
    • Reported - view

    Mconneen, please email me the file you made. bruce at kieffer.us

    • Mr_K
    • 4 yrs ago
    • Reported - view

    Update: I set up a "Linked From" field rather than a "Table Reference" field, and so far that is working.

    • Mr_K
    • 4 yrs ago
    • Reported - view

    Screen Shot 2019-07-13 at 7.08.01 PM

    • Mr_K
    • 4 yrs ago
    • Reported - view

    Nope, not working. I can only link the "Where Used" once.

    • Ivan_Savochenko
    • 4 yrs ago
    • Reported - view

    Mr. K.,

    You need to create a new one table inbetween Paint table and Where Applied table. So delete the reference you have just created and create a new table, let's say Paint Appliance, where you should add at least two fields (Create table reference): ➞ Paint and ➞ Where Applied. You may add there a few fields about your painting conditions like Painting Date, Painting Method, Paintig Person, Painting Memos etc.

    • Mr_K
    • 4 yrs ago
    • Reported - view

    Thanks. I got it working, but in doing so I realized I'm going about this relationship wrong. It's the location that should be the master table, and the other tables of Paint, Fixture, Tile, Windows and Doors all relate to a location. That makes more sense to me. I'm rebuilding the database now. Also watching the video Andy has from NINOXUS. It is good.

    https://www.youtube.com/watch?v=Iw18BMWSEyY

    • Mconneen
    • 4 yrs ago
    • Reported - view

    "give a man a fish and you feed him for a day; teach aman to fish and you feed him for a lifetime." .... Laozi

Content aside

  • 4 yrs agoLast active
  • 11Replies
  • 2899Views