Returning path from 2nd record?

[Sgt_Utz]

I am building a customer database with a dedicated spot on the bottom of a customer form to display 3 pictures left to right. Some customers don't have any pictures, while most have 1-3.

Under the expression builder for each of the pictures i'm trying to create a SQL query to find the pic if it exists to string the path to the pic. Using the example below, the query sorts the "ID" field ascending (easy), but for the middle (2nd) picture in the form I need to string the "path" from record 2166 (2nd record in the results).




I'm sure this is easy but I've been looking for a while with no success.

Thanks all!

[Welshgasman]

I'd probably create a function to get all three and populate an array to hold the paths?

[Gicu]

Would be much easier if you would create a subform that would display the pictures (linked by Customer_ID), that way you could accommodate the odd customer with 4 (or 5...) pictures.

To do it the way you have it you would probably have to write a custom VBA function in which you would move through a recordset one record at the time and get the path. Or maybe create SQL queries grouping by customer_ID and use Min(ID) to get first record, Max(ID) the get the third and (<>Min(ID) AND <>Max(ID)) to get the second.

Cheers,

[ranman256]

on the form , when the user pulls the master record for cust: CUS-000-0983, (single rec)
the subform will pull the 3 photo records. (datasheet)
user can then select the one wanted , then click another button to open it.

databases don't display records left-to-right. (only top/down)

[Sgt_Utz]

Thanks for the suggestions. This is actually a database to show the current data from some software. I need to build it first to see everything, then design a database to transition from the current software. I found customers only had 3 pictures max, so that's why i just wanted to be able to see them all displayed so i could see what each customer has and go from there.