datediff for multiple records

[Ken]

Hi all
I have a group of records (200,000+)in the following
format.
EG:
IDCode AttNum DateStart DateFin
123456 334689 10/10/03 12/10/03
123456 334786 13/11/03 16/11/03
123456 335689 18/12/03 20/12/03
234679 334423 20/11/03 22/11/03
234452 233445 01/09/03 22/09/03
234452 235423 10/09/03 15/10/03

I need to know which records have had multiple attendences
and of those with multiple attendences, how many days
between each visit ie DateFin (1st vistit) and DateStart
(2nd visit), etc.

Can this be done using the SQL screen in Access97 or a
macro? The client has none to little VBA knowledge.
I understand the logic behind what I need to do, using a
counter and if/else statements, but am having trouble
putting it into practice as a query/SQL.

TIA

 

[Allen Browne]

With 200k records, if the majority of people have not attended multiple
classes, it will be worth creating a qeury to preselect those attendees, and
work with the result. (If not, you can just use the subquery to get the
matches.)

I'm assuming:
- the Table is named tblAttend;
- IDCode is the primary key;
- AttNum is a foreign key itentifying the person who attended.

1. Create a query into this table.
Depress the Total toolbar icon.
Access adds a Total row to the grid.

2. Drage AttNum into the grid.
Accept Group By in the Total row.

3. Drag IDCode into the grid.
Choose Count in the Total row.
In the Critieria row, choose:

1

4. Save the query. This preselects the people (AttNum) who have more than
one attendance.

5. Create another query that combines the table and the query you just made,
joined on AttNum. Drag the fields you want to see into the output grid.

6. In a fresh column of the Field row, enter a subquery to get the most
recent finish date for the attendee prior to the start date of the current
record:
LastFin: ( SELECT Max(DateFin)
FROM tblAttend AS Dupe
WHERE (Dupe.AttNum = tblAttend.AttNum)
AND (Dupe.DateFin < tblAttend.DateStart) )

7. Once you have that working, you can use DateDiff() to calculate the
difference.

 

[ken]

Thanks for the help, and found another way
PSB

SELECT initial.IDCode, initial.AttNum, initial.DateStar,
initial.DateFin, Min(subsq.DateStar) AS NextDateStar
FROM tblKB AS initial INNER JOIN tblKB AS subsq ON
initial.IDCode =
subsq.IDCode
WHERE (((subsq.DateStar)>[initial].[datefin]))
GROUP BY initial.IDCode, initial.AttNum, initial.DateStar,
initial.DateFin;

 

[John Vinson]

Hi all
I have a group of records (200,000+)in the following
format.
EG:
IDCode AttNum DateStart DateFin
123456 334689 10/10/03 12/10/03
123456 334786 13/11/03 16/11/03
123456 335689 18/12/03 20/12/03
234679 334423 20/11/03 22/11/03
234452 233445 01/09/03 22/09/03
234452 235423 10/09/03 15/10/03

I need to know which records have had multiple attendences
and of those with multiple attendences, how many days
between each visit ie DateFin (1st vistit) and DateStart
(2nd visit), etc.

Can this be done using the SQL screen in Access97 or a
macro? The client has none to little VBA knowledge.
I understand the logic behind what I need to do, using a
counter and if/else statements, but am having trouble
putting it into practice as a query/SQL.

Neither a counter nor if/then logic are needed nor appropriate!

Instead, create a Query by adding your table to the query design
window TWICE. Access will append _1 to the name of the table for the
second instance. Join the two instances by IDCode; and put a criterion
on the second instance of DateStart of

=(SELECT Min([DateStart]) FROM yourtable AS X WHERE X.DateStart >
tablename.DateFin)

This will, for each record retrieved from the first instance, retrieve
the next chronological visit for that ID as the second instance.