Calculate between 2 dates in the same column

[cooh23]

I have a database that keeps track of each visit with the following columns
- ID
- ClientID
- VisitDate
- Type
- Value

So what I am trying to do is find out the following:
- Client's first visit
- Client's last visit
- The number of weeks between the first and last visit
- The number of weeks between the last visit and the prior visit to the last (if client had multiple visits)

The reason i'm having a problem is because it is on the same column. I want this in a report so I would group it by Client and Type.

Any help is greatly appreciated.

Thank you,

John

 

[spikepl]

http://allenbrowne.com/subquery-01.html

 

[cooh23]

http://allenbrowne.com/subquery-01.html

Thank you! It looks like that's what I was looking for.

I will try it as soon as I wake up later. It's past 4am here. I just wanted to post my question and get input. Thanks again!

John

 

[cooh23]

I started playing around with the queries and I can't seem to calculate the difference between dates in a single column.

I am able to do it in the report by grouping.

Here are my fields again:
- ID
- ClientID
- VisitDate
- Type
- Value

in the report, I grouped it by ClientID and Type.
In the Type footer. I was able to get the First Date and the Last date by using Min and Max functions. Then I got the week difference by using datediff on the first and last dates.

Here's my problem, I want to have this in a query so that I can only show those clients that have a week difference of more than X weeks.

I looked at the provided link but I can't seem to get it to work

Thanks in advance