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.