Results 1 to 15 of 15
  1. #1
    Thomasso is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250

    Storing attachments for multiple entities

    Hi all,

    I want to store attachments to more entities, for example:

    • SalesOrder - I want to store various PDF files (customer purchase order, contract...)
    • Delivery - I want to store different kind of documents (customs clearance, shipping information...)

    And so on...

    I'm using SQL Server 2019 and I want to use nvarchar(100) data type with the path to the file ("Z:\Attachments\SalesOrders\CustomerPO000101010.p df"), which is a good practice in Access in my opinion.

    But I'm thinking of the correct design here... It seems to me that I will need a table for each attachment type, for example:


    • tblSalesOrderAttachments
    • tblDeliveryAttachments

    And so on, because if I only had one table, I wouldn't be able to correctly assign foreign key to the respective entity.

    Am I correct or am I missing something?

    Thanks a lot.

    Tomas

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    No, just a field to describe attachment type.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    Thomasso is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250
    Ok but how would I reference the attachment to a primary key of a specific sales order for example?

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What is the design of tblSalesOrder?

    something like this

    tblSalesOrder
    SalesOrderID............234
    SalesOrderDate........2/27/2022
    CustomerID.............PO000101010
    ....
    AttachmentLoc........."Z:\Attachments\SalesOrders\ CustomerPO000101010.pdf" or (include SalesOrderID also)
    ............................."Z:\Attachments\Sales Orders\CustomerPO000101010S234.pdf"

  5. #5
    Thomasso is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250
    Yes, but the problem is that I need to store multiple attachments per order.

    My design:

    tblSalesOrders:

    • SalesOrderID
    • SalesOrderCode
    • SalesOrderDate
    • CustomerID
    • ...


    tblDeliveries:
    • DeliveryID
    • DeliveryCode
    • SupplierID
    • DateShipped
    • DateReceived
    • CarrierID
    • TrackingNo
    • ...


    I need to be able to store multiple files for each table. For example 3 PDF files for a sales order, 1 PDF file for another sales order, 2 ZIP files for a delivery.

    The idea is that I have a form with Sales Order Details and there will be an option to "upload" (read: link to a file) as many attachments as I like. The same with deliveries and different tables too.

  6. #6
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    So you have a TblAttachmentPath

    In that you have
    AttachPathID ' Autonumber
    ParentKey ' Foerign key for parent entry
    AttachType 'Type of attachment possibly a string or even a key to another table tblAttachmentType if you have more than a few?

    Then just use the PK from the parent. If the parents are in different tables, then the FK could be the same, but the Type of field would differentiate.

    How do you determine a particular SalesOrder has been delivered?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Tomas,
    I have used a slightly different approach in the past that did not need a foreign key, it was strictly based on the folder naming convention, which might work for you as well.
    So you currently have Z:\Attachments\SalesOrders\CustomerPO000101010.pdf. I suggest you add another subfolder level: Z:\Attachments\SalesOrders\SO_0001\CustomerPO000101010.pdf. The subfolder name corresponds to the PK of the sales orders table (or easily deducted from it). Now you don't need anything else, any file you place in that subfolder will show up in the sales orders attachments tab. Basically I have code that runs on the Current event of the form that loops through the subfolder and populates a list box or a subform with the files currently in the subfolder so it is always synchronized with the actual files in the folder.
    Click image for larger version. 

Name:	Screenshot 2022-03-01 080459.png 
Views:	19 
Size:	70.8 KB 
ID:	47353

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    @WGM, Don't you need a field for the file path in there as well?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Yes, perhaps a few more as well?. I am just trying to get the logic for the relationship. Once that is correct, any other fields would be available.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

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

    If Vlad's approach doesn't work for you, then please show us a sample(s) showing a mock up of what you need to store and exactly what it relates to.
    What is the logic for retrieving these attachments? Just following up on Welshgasman's question.

    Customer, SalesOrder......

  11. #11
    Thomasso is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250
    Vlad's approach is perfect for me, thanks a lot!

  12. #12
    Thomasso is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250
    Quote Originally Posted by Welshgasman View Post
    So you have a TblAttachmentPath

    In that you have
    AttachPathID ' Autonumber
    ParentKey ' Foerign key for parent entry
    AttachType 'Type of attachment possibly a string or even a key to another table tblAttachmentType if you have more than a few?

    Then just use the PK from the parent. If the parents are in different tables, then the FK could be the same, but the Type of field would differentiate.

    How do you determine a particular SalesOrder has been delivered?
    Thanks, I will go with Vlad's approach, however, just for clarity.

    A sales order can be delivered partially, I have tblDeliveryDetails:
    • DeliveryDetailID
    • DeliveryID
    • SalesOrderDetailID
    • Quantity


    Then I run a query to determine if the SUM of quantity in tblDeliveryDetails with that particular SalesOrderDetailID corresponds with the SUM of quantity in tblSalesOrderDetails.

  13. #13
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Glad you have a solution.
    For reference there are several draft data models at Barry Williams' site.
    #51, #52 relate to Customers Orders and Deliveries

  14. #14
    Thomasso is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250
    Quote Originally Posted by orange View Post
    Glad you have a solution.
    For reference there are several draft data models at Barry Williams' site.
    #51, #52 relate to Customers Orders and Deliveries
    That's actually really helpful, thank you!

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

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

Similar Threads

  1. Replies: 2
    Last Post: 11-28-2018, 11:05 AM
  2. Replies: 5
    Last Post: 09-29-2015, 11:08 AM
  3. Help storing Time with multiple users
    By bobrulz in forum Programming
    Replies: 1
    Last Post: 11-30-2014, 09:05 PM
  4. Need help storing multiple answers per question
    By Buakaw in forum Database Design
    Replies: 6
    Last Post: 08-03-2011, 08:18 PM
  5. Replies: 3
    Last Post: 08-01-2011, 09:18 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