display a picture from 1-table on a form/report

[wmsmith.it]

I have two tables - one named [main] the other named [KPI]

main:
ID = primary key
KPI = this is a name (green, red, amber)
KPI_ID = child
KPI_pic (currently just storing an image to make things work - not good use of a database)

KPI:
KPI_ID = primary key
main_ID = child
KPI_color = this is the same as KPI from the main table
KPI_Color_Pic = is a round picture of the colors (green, red, and amber)

what I am trying to do is keep the few KPI (key preformance indicator) pictures in the KPI table and display the results on a form

basically - select from KPI table where main.kpi = kpi.kpi_color

the results then will be the picture on the form and or reports - therefore storing the image(s) in once place (the KPI table) and calling on the image automatically based on what data is stored in the [main] table

I use this database to track the status of taskers sent out to multiple divisions within my organization - the reports are scorecards whereas the Division Chiefs can see "via a picture" the status of assigned tasks (of course there are many more fields - but the picture is the only thing I am having difficulties with)

Q: do I build an UPDATE query?
Q2: do I perform a SELECT FROM... WHERE.. (then how do I annotate what I want the program to perform)?

please assist - thank you

 

[Big Pat]

Do you really NEED pictures? My approach to this is:
Use a calculated field (in the report or - my preferred option - in the underlying query.)
This calculated field would return the values Green, Amber or Red.
Bring that field onto your report and use conditional formatting, colouring it to match the word.

See attached example. It's the only example i can find just now and has only one such field in the heading. I do have reports that have red/green/amber on records all the way down, but the fields show a number (i.e. the actual KPI) rather than the word.

I think this approach would be far simpler than linking to pictures.