Forward Join? (1 Viewer)

CJBIRKIN

Drink!
Local time
Today, 07:31
Joined
May 10, 2002
Messages
256
Hello
I'm trying to find a way of looking at data in a following record in order i.e if the name "JIM" appeard in field_name record 1 and record 2 then i want to be able to calculate the difference in times that these two records were created from the dates in filed_created.

Any help would be really appreciated!!

Chris
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 02:31
Joined
Feb 28, 2001
Messages
27,001
It is not clear from your question what structure you use. I am going to discuss how you would do it for a structure I will assume to be present.

If your records are in a single table and the [Person] and [TimeStamp] fields are present (substitute your own names as needed)

Open a query grid. Place the table in the upper part of the grid twice. (Add the same table twice.)

Access should automatically build a JOIN based on primary keys. But you don't necessarily want that. So IN THE TABLE AREA ONLY, click on the existing relationships and DELETE them. Then add a relationship between the [Person] entries in the two instances of the table. Make it a one-to-many relationship with the "one" side as the first entry and the "many" side as the second entry.

Drag the [Person] field from the first instance of the table. Drag the [TimeStamp] entries from BOTH instances.

Now, look up the Help text for DateDiff to see how to specify the units you want. As a new field in the query grid, include the DateDiff function of the two fields in the proper order.

There IS a down side to this.

If you have more than TWO entries for JIM in this table, you will get a LOT of elements in the query. One for each possible combination of records for JIM, with dates all over the landscape.

In order to limit this query, you need to further restrict the way the query is defined, which might include adding a field to your table based on the [Person] field. Add an integer or long integer field to the table, call it [PersPrev].

Separately write an update query that updates [PersPrev] with

DCount( "[Person]", "MyTableName", "[Person] = """ & [Person] & """ [TimeStamp] < #" & [TimeStamp] & "#")

(At least, something like this. The syntax you actually use might depend heavily on the [TimeStamp] field's format and data type.)

Now modify that first query to include the [PersPrev] fields from both tables, plus an expression that is equal to the difference between the [PersPrev] from the first instance of the table and the [PersPrev] from the second instance of the table. Now set a criterion on this computed field =1

That way you will only get adjacent records in the queries that compute the differences between JIM's records.

I know that sounds complex, but it is really only a couple of ugly queries - an UPDATE that should be re-runnable at any time and a SELECT that can be run immediately after the UPDATE was run.

Now, if you can't add the [PersPrev] field to your table, this gets hard enough that I wouldn't try it with anything less than VBA code and a lot of coffee.
 
Last edited:

CJBIRKIN

Drink!
Local time
Today, 07:31
Joined
May 10, 2002
Messages
256
Hello
Thank you for the information. I've tried it on my Tbl and it works well. I had heard this sort of query used a self join but i assumed that meant it was joined to itself i.e a join within 1 table rather than across 2 instances of the same table, problem solved.
Cheers
Chris
 

Users who are viewing this thread

Top Bottom