Urgent VBA Help!

[laurajayne.cozens]

Hey, hope one of you can help wiht this!

I have a table that contains several different clients, each with
their own client_id. Each client has a start and end date as to when
their course starts with us and when it ends. Some clients have
multiple records with different dates where they have done different
courses. Example:

Client_Id Start_Date End Date
1234 01/09/2006 31/10/2006
1234 12/12/2006 01/02/2007
1234 09/02/2007 30/04/2007
1345 01/01/2007 01/03/2007
1289 01/09/2006 31/11/2006
1289 01/09/2006 31/10/2006

For those clients with multiple records I need to count the number of
day bewteen courses. So for client_id 1234 above, I need to know how
many days are between End_date of the first record and the start_date
of the second reocrd. Then I need to know how many days are betweent
he end_date of the second record and start_date of the third record
and so on and on...

This code needs to then repeat for all clients with multiple records -
BUT there is no limit on how many records a client may have.

I think I may need some kind of loop that assigns an auto number to
records but that resets itself on a change of client_id but i have no
idea how to do this! Help!

Laura

[Jason Lepack]

This doesn't outline the courses that these people are taking. Based
upon your description of your problem client_id = 1289 doesn't fit the
bill.

Start course on January 9, 2006
End course on November 31 (30?), 2006
Start course January 9, 2006 (time between courses is negative)
End course on October 31, 2006

Cheers,
Jason Lepack

On May 29, 6:06 am, [e-mail] wrote:

Hey, hope one of you can help wiht this!

I have a table that contains several different clients, each with
their own client_id. Each client has a start and end date as to when
their course starts with us and when it ends. Some clients have
multiple records with different dates where they have done different
courses. Example:

Client_Id Start_Date End Date
1234 01/09/2006 31/10/2006
1234 12/12/2006 01/02/2007
1234 09/02/2007 30/04/2007
1345 01/01/2007 01/03/2007
1289 01/09/2006 31/11/2006
1289 01/09/2006 31/10/2006

For those clients with multiple records I need to count the number of
day bewteen courses. So for client_id 1234 above, I need to know how
many days are between End_date of the first record and the start_date
of the second reocrd. Then I need to know how many days are betweent
he end_date of the second record and start_date of the third record
and so on and on...

This code needs to then repeat for all clients with multiple records -
BUT there is no limit on how many records a client may have.

I think I may need some kind of loop that assigns an auto number to
records but that resets itself on a change of client_id but i have no
idea how to do this! Help!

Laura

[Jason Lepack]

A possible solution given that course dates do not overlap...

SELECT A.CLIENT_ID,
A.START_DATE,
A.END_DATE,
NZ((SELECT MIN(START_DATE)
FROM LAURAJAYNE AS B
WHERE B.START_DATE A.END_DATE
AND A.CLIENT_ID = B.CLIENT_ID),DATE()) AS
NEXT_START,
DATEDIFF("d",[END_DATE],[NEXT_START]) AS DAYS_BETWEEN
FROM LAURAJAYNE AS A;
On May 29, 6:06 am, [e-mail] wrote:

Hey, hope one of you can help wiht this!

I have a table that contains several different clients, each with
their own client_id. Each client has a start and end date as to when
their course starts with us and when it ends. Some clients have
multiple records with different dates where they have done different
courses. Example:

Client_Id Start_Date End Date
1234 01/09/2006 31/10/2006
1234 12/12/2006 01/02/2007
1234 09/02/2007 30/04/2007
1345 01/01/2007 01/03/2007
1289 01/09/2006 31/11/2006
1289 01/09/2006 31/10/2006

For those clients with multiple records I need to count the number of
day bewteen courses. So for client_id 1234 above, I need to know how
many days are between End_date of the first record and the start_date
of the second reocrd. Then I need to know how many days are betweent
he end_date of the second record and start_date of the third record
and so on and on...

This code needs to then repeat for all clients with multiple records -
BUT there is no limit on how many records a client may have.

I think I may need some kind of loop that assigns an auto number to
records but that resets itself on a change of client_id but i have no
idea how to do this! Help!

Laura

[Laura]

Hi Jason

Ignore 1289 - these are just examples and i havent checked the dates.
None of the dates will ever overlap.

Could you explain the code above? Especially the A. parts - i havent
seen this before when using VBA...

Thanks for your help!

Laura

[Laura]

Hi Jason

Forget it - I copied your code into an access query rather than a VBa
module and it works 100% spot on! Thanks so much for saving me and my
collegaues so much time!

Laura

[Jason Lepack]

On May 29, 9:02 am, Laura <[e-mail]>
wrote:

Hi Jason

Forget it - I copied your code into an access query rather than a VBa
module and it works 100% spot on! Thanks so much for saving me and my
collegaues so much time!

Laura

You got it. It's SQL - the code that's used in queries.

SELECT A.CLIENT_ID,
A.START_DATE,
A.END_DATE,

DATEDIFF("d",[END_DATE],[NEXT_START]) AS DAYS_BETWEEN
FROM LAURAJAYNE AS A;

It selects the client_id, start_date, and end_date from a table called
"LAURAJAYNE". That table is given an alias of "A" (because it's
easier to type and read).

This section is a little more complicated:
NZ((SELECT MIN(START_DATE)
FROM LAURAJAYNE AS B
WHERE B.START_DATE A.END_DATE
AND A.CLIENT_ID = B.CLIENT_ID),DATE()) AS
NEXT_START,

NZ is a function that selects the first of a list of values that is
not null. The query selects the first start date from LAURAJAYNE
(aliased as B) that has a matching clientid and is greater than the
current end_date. Because there may be no record that has a greater
start date, the nz function will then pick DATE(), which returns
todays date.

Cheers,
Jason Lepack