I have 2 tables, simplified as follows:

Table 1 Fields: Program Name, Time Activated
Table 2 Field: Time Snapshot

Every time a user changes the active program, it logs the new program name and activation time to Table 1.

The program running at the time of "Time Snapshot" is therefore the greatest time in Table 1 less than "Time Snapshot"

I'm trying to write a query that lists my Time Snapshots and the Program that was running at the time they were taken, but have failed...repeatedly

Oh darn. I was hoping someone would post a reply with a good example.

This approach should work but is pretty crude.
1) Make a query with the fields from Table 1 and Table 2. Do not join the table. This produces the "product" of the rows; if Table 1 contains 10 rows and Table 2 contains 4 rows, then the query will produce 40 rows. Save as "qryOne".

2) Make a second query using qryOne as the source. Select all rows where Time Activated is less than or equal Time Snapshot. Order by Time Activated descending. Save as "qryTwo".

3) Make a third query using qryTwo as the source. Group by Time Snapshot. Select First([TimeActivated]) and First([ProgramName]).

I have not tried this but I think it would word. If you are lucky, you will get a better response than this http://www.access-programmers.co.uk/ubb/smile.gif

RichM

Let's see if I can beat RichM...

Try this statement:

SELECT SnapShotTime, [Program Name]
FROM tblProgram, tblSnapshot
WHERE ActivationTime=
(SELECT Max(ActivationTime)
FROM tblProgram AS tblProgram1
WHERE ActivationTime<=SnapShotTime)
;

Greetings,

RV

Thanks RV.

I just tried this and it works.

RichM

You're welcome!

RV