How to create an access database with photos across multiple years?

Question

I am very new to access and have tried to learn the basics of creating tables, forms, reports etc. I need to create a database with photos. My table contains coordinate points and other info about each point. I have data spanning across years, each record in the table should have 3 different photos. Let’s say I want to see the 3 photos for point A in 2023 and the 3 photos for point A in 2024 to compare them. Ideally I would like to see multiple years at the same time. I understand it’s not ideal to use attachments and have used the pathway of the photos in the table. I honestly do not know where to start, I have made a search box but that only shows the records in table form not the images.

Answer 1

I'm a little concerned by your saying that 'each record in the table should have 3 different photos'.  That suggests that you have three separate columns in the table for the paths to the image files.  That would mean that the table is not normalized to First Normal Form (1NF) which requires each row in a table to contain one value only of each attribute.  The correct way to associate multiple images with a record would be to have a related table which models the many-to-many relationship type between the table and an Images table, by resolving the relationship type into two one-to-many relationship types. 

You might like to take a look at Images.zip in my public databases folder at: 

https://1drv.ms/f/c/44cc60d7fea42912/EhIppP7XYMwggESpAAAAAAAB3aLXYo7DdARg01KnQIyoLg?e=fASl3m 

In the zip archive the basic Images.accdb file models the relationship type between Address and Images in this way, by means of an Addresses_Images table. 

One way to compare images would be by means of a report filtered to return the rows in question form the Addresses table.  You'll see that the database contains a rptAddresses report in which a rptSubImages subreport is embedded.  Currently the subreport's RecordSource query has a criterion of TRUE on a Selected column.  To do what you want this criterion would first have to be removed.  If, for instance, we wanted a report with all images of addresses in the towns of Newport and Stafford, the report could then be opened with: 

DoCmd.OpenReport "rptAddresses", View:=acViewPreview,WhereCondition:="City = ""Newport"" Or City = ""Stafford""" 

In a developed application you would not hard code the criteria in code like this of course, but would build a user interface in which one of more cities can be selected.  If you take a look at DatabaseBasics.zip in my same OneDrive folder the section on 'retrieving data from the database' in this little demo includes a form in which two methods of selecting multiple values in a multi-select list box are illustrated.  This then allows a report filtered to the two selected items to be opened via a command button.  You could of course combine such a list box with other unbound controls in which other criteria can be selected. 

You could of course open a form filtered in the same way, rather than opening a report.