Results 1 to 3 of 3
  1. #1
    steve7800 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    41

    How to sum the number of entries in a linked table?


    I am working with a database which is used to track items purchased for a business. One of the forms in the database is frmItem, which presents the user with a list of items and related information such as unit cost, purchase date, vendor, vendor part number, manufacturer, etc. frmItem’s underlying query qryItem is built around table tbItem and a number of other linked tables such as tbVendor, tbManufactuer, etc. The primary key for tbItem is item number iID, an autonumber field which is used to provide a unique item number for each item.

    Attachments (technical drawings, pictures, instructions, etc) are stored in the database using the attachment data type. I am in the process of implementing a way to store attachments external to the database. An additional table tbLinks has two fields: item number, which is linked to item number in tbItem; and ItemLinks which is a path and file name to the externally stored attachment. An item in tbItem can have none or several externally store attachments. The tbItem to tbLinks relationship is based on item number and is one-to-many, hence an item in tbItem can have none or several records in tbLinks.

    So, after all that, here’s what I’d appreciate some help with: I’d like to add a column to frmItem to show how many external attachments each item has. It seems to me that a NrOfLinks field would have to be added to qryItem. How do I sum the number of entries each item has in tbLinks and put that sum in the NrOfLinks field in qryItem? Or is there another better way to do it?

    Thanks.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    No, do not have this count field in tblItems. Calculate the count when needed, do not save aggregate data.

    DCount() aggregate function in textbox can accomplish this on form.

    Is this really important to show on form? Why not just use subform to show the associated tblLinks records?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    steve7800 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    41
    Thank you June 7. DCount does the trick.

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

Similar Threads

  1. Replies: 2
    Last Post: 08-29-2018, 04:42 AM
  2. Replies: 2
    Last Post: 03-11-2015, 10:08 PM
  3. Replies: 3
    Last Post: 05-30-2014, 11:18 AM
  4. Replies: 8
    Last Post: 02-18-2014, 09:50 PM
  5. Prevent duplicate entries in linked table
    By arothacker in forum Forms
    Replies: 5
    Last Post: 02-11-2014, 11:08 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