Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    tregnier is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    8

    Product Catalog design, multiple ref to the same entity

    Hi Gents,

    I'm a project engineer working on some piping & steel fabrication company in Malaysia. I haven't see any presentation page but just let me know if I miss it.



    One of our biggest concern while fabricating parts is to follow up on quality and welding tracability, and I was looking for a database to manage it through a better user front end, capable of pulling reports, fab status, etc.
    Ideally the end target is to expend it and have a multi user/responsible using this, with an interface for the project dpt, one for the QC dpt, etc. Current setup is using excel sheet for each task, with a lot of redundant information and replacing it by a database sounds the best option to me.

    I designed the following database so far, an you may start to see where I am getting stuck.
    I am trying to record the data for the welding joint, with different type of properties (welding #, welder name, date, drawing & project associated). The most important ones are the 2 parts which are being welded together.

    So my first issue is to differentiate in a query, which info are from part 1, and wich info are from part 2? As you can see, I have 2 different attributes pulling info from the same table. Not sure if this is the approriate way to do it.


    [suit to follow in next post]
    Attached Files Attached Files

  2. #2
    tregnier is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    8
    My second issue so far, is to manage the product catalog that will be used. I called the following:
    Part : this is a physical object in the real world. It is a product, such as a pipe, elbow,flange, etc. with additional attributes making it unique in the context of the fabrication : serial number, material, length
    Product : this is basically the raw information from which are derived parts. it groups all common attributes that defines a group of items : a pipe, elbow, bolts, etc.


    The problem is that different type of product, does not have the same type of attribute. An elbow will have an attribute "angle" where this is not required for a pipe.Flanges are even more complex with attributes such as type of groove, classe,ID, Thickness, etc.


    I don't think it will be a great idea to have only one table, grouping all type of product together and creating a field for each type of attributes. 80% of the cells will be empty, not speaking about the possible evolution of the database and adding new product.

    As a solution, I designed the following pattern. I first create a table for each type of product, with their custom field, and then I designed a 2nd table to indicate the type of product. with this two information working together (Product ID and which table to look for) it should in theory be able to retrieve the correct product.
    However I don't know how to work with this for real, and retrieve information in a query.

    Click image for larger version. 

Name:	Annotation 2020-08-14 122211.png 
Views:	45 
Size:	21.1 KB 
ID:	42654

    So I'm reaching you to get some clue on how to proceed

    I could probably spend 2weeks on a vba code to make it work, but at the end I am not sure this is the right way to do it.
    There is definitely some turnaround that I don't know for this kind of situation. For example, I research some interesting information about EAV model, which seems appropriate for this kind of situation, however I didn't find practical info on it and howto make it work.

    I hope my explanation was clear enough, and that you have some kind of suggestion for my problem

    Thank you,
    Theo

    Link to the fulldatabase so far : https://app.lucidchart.com/invitatio...1-1572968f2b9f


  3. #3
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi Theo

    You have not attached our database only the ER Diagram.

    If you can attached a zipped copy of the database we will be able to take a look for you.
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  4. #4
    tregnier is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    8
    Hi Mike,

    Thanks for your reply. I havent done too much on the database so far, as I don't want to go too deep in a wrong direction. but see attached the database as of now.

    Cheers,
    Theo

    3CMA_DATABASE_V0.1.zip

  5. #5
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Theo

    Unable to open the zip file?

    Make sure you close the database before you zip it
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  6. #6
    tregnier is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    8
    Some issue with the compression I think, I am unable to send a .zip anymore for some reason... Anyway, here is a google drive link with the database, compressed and uncompressed

    https://drive.google.com/drive/folde...eJ?usp=sharing

    Thank you!
    Theo

  7. #7
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi Theo

    In your Joint table you have Fields Part 1 and Part 2

    Does 1 Joint always have only 2 Parts ?

    Or can a Joint have more than 2 Parts?
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  8. #8
    tregnier is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    8
    Hi,

    In 99% of the case, only and always 2 parts.

    However, the same part can be jointed multiple times, this is actually the case most of the time too, as a pipe got 2 end usually (sometimes more, like Tee connection or Cross connection)

    Thanks,
    Theo

  9. #9
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi Theo

    Not sure that your flow is correct.

    Should you not first select a Part and then Make a Number of Joints on that Part?
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  10. #10
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi TheoWould it be possible for you to upload an example Spreadsheet that you currently use?
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  11. #11
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Here is the dB from the OPs Google Drive site, in case anyone wants to see it. I did have to do a C&R to be able to upload it.
    Attached Files Attached Files

  12. #12
    tregnier is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    8
    Hi gents,

    thanks for your replies.
    I do not have a spreadsheet with me right now but I can share one tomorrow. Which format would be the best? I can share a excel example of the final reports we are issuing, however for now everything is written by hand and copy/paste...
    also attached some table I did with general attributes of the product catalog (non exhaustive)

    As for the workflow, not exactly sure which way it should be turned, part and/or welding joint.
    What's 100% sure, is that we are monitoring the welding joints, and the parts beeing welded are only a parameter of the joint. For example, What is called WPS, WQR, Electrodes batch, are welding records which exist in the context of a welding joint only (Welding procedure, Welder name, electrodes used for the welding, etc.).

    See attached the latest version of the database, not a lot of change, but I tried a quick Forms & Query showing some data I am trying to gether and present. I cheated here, as I used the attribute Part1 for the pipes, and part2 for the elbow, and simply linked the ID to the correct table. In real use, I don't know which type of part (pipe, elbow, etc.) will be linked to a joint.

    Thanks Ssanfu for updating the link. I tried again however the size is too big even compressed (6mb), I had to send it on the same dropbox link.
    https://drive.google.com/drive/folde...eJ?usp=sharing

    Thank you,
    Theo

    Catalog_List.zip

  13. #13
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    3CMA_DATABASE_V0.2

    Here is the zipped dB from the OPs Google Drive site, in case anyone wants to see it. I did have to do a C&R to be able to upload it.

    Apologies........ I had a really bad case of CRS.... maybe still have it.
    Attached Files Attached Files
    Last edited by ssanfu; 08-17-2020 at 05:03 PM.

  14. #14
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  15. #15
    tregnier is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    8
    Hi, Just to let you know that I am working a bit on other aspect of the DB, creating some forms & reports to show better what's needed. I'll then get back with for data for this design issue of part catalog

    Theo

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Word product catalog from Access 2007 data
    By todavy in forum Import/Export Data
    Replies: 3
    Last Post: 10-14-2015, 10:42 AM
  2. Replies: 6
    Last Post: 02-28-2015, 11:57 AM
  3. Replies: 1
    Last Post: 05-18-2014, 10:44 AM
  4. Catalog Design
    By manda in forum Database Design
    Replies: 3
    Last Post: 02-27-2013, 05:20 PM
  5. Replies: 3
    Last Post: 08-29-2010, 06:34 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