A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

date gaps



 
 
Thread Tools Display Modes
  #1  
Old August 23rd, 2006, 07:42 PM posted to microsoft.public.access.queries
buckpeace
external usenet poster
 
Posts: 18
Default date gaps

I am trying to count members that may have had a gap in enrollment of 30
days. How can I write a query to show me which members had gaps in
enrollment. Here are the fileds I am working with. (see below for
example....1 member has diff lines..."spans" of enrollment if you
will.......) I only want the query to be able to place the number of days of
the gap in a field to see if they were enrolled.

member ID effective date end date
12345678 10/1/1999 10/31/2000
12345678 11/1/2000 11/30/2000
12345678 05/1/2002 09/1/2002


With much appreciation,

--
Buck
  #2  
Old August 23rd, 2006, 10:14 PM posted to microsoft.public.access.queries
Wayne-I-M
external usenet poster
 
Posts: 3,674
Default date gaps

Hi Buck

Use something like this

GapateDiff("d", [effective date], [end date])

Gap is the number of days between the 2 dates.

Hope this helps. You can use the criteria lines to filter this ie. 29
would give you 30 days or more, etc.

Hope this helps

--
Wayne
Manchester, England.
Not an expert
Enjoy whatever it is you do


"buckpeace" wrote:

I am trying to count members that may have had a gap in enrollment of 30
days. How can I write a query to show me which members had gaps in
enrollment. Here are the fileds I am working with. (see below for
example....1 member has diff lines..."spans" of enrollment if you
will.......) I only want the query to be able to place the number of days of
the gap in a field to see if they were enrolled.

member ID effective date end date
12345678 10/1/1999 10/31/2000
12345678 11/1/2000 11/30/2000
12345678 05/1/2002 09/1/2002


With much appreciation,

--
Buck

  #3  
Old August 23rd, 2006, 10:32 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default date gaps

Try this using two queries - first named [buckpeace-1].

[buckpeace-1] ---
SELECT [member ID], [effective date], [end date], (SELECT COUNT(*)
FROM [buckpeace] T1
WHERE T1.[member ID] = T.[member ID]
AND T1.[effective date] = T.[effective date]) AS Rank
FROM buckpeace AS T
ORDER BY [member ID], [effective date], [end date];

Second query --
SELECT [buckpeace-1].Rank, [buckpeace-1].[member ID],
[buckpeace-1].[effective date], [buckpeace-1].[end date],
Min(DateDiff("d",[buckpeace-1].[end date],[buckpeace-1_1].[effective date]))
AS [Days end to start], Max([buckpeace-1_1].[effective date]) AS [Last
Effective Date]
FROM [buckpeace-1] INNER JOIN [buckpeace-1] AS [buckpeace-1_1] ON
[buckpeace-1].[member ID] = [buckpeace-1_1].[member ID]
WHERE ((([buckpeace-1_1].Rank)[buckpeace-1].[Rank]))
GROUP BY [buckpeace-1].Rank, [buckpeace-1].[member ID],
[buckpeace-1].[effective date], [buckpeace-1].[end date];


"buckpeace" wrote:

I am trying to count members that may have had a gap in enrollment of 30
days. How can I write a query to show me which members had gaps in
enrollment. Here are the fileds I am working with. (see below for
example....1 member has diff lines..."spans" of enrollment if you
will.......) I only want the query to be able to place the number of days of
the gap in a field to see if they were enrolled.

member ID effective date end date
12345678 10/1/1999 10/31/2000
12345678 11/1/2000 11/30/2000
12345678 05/1/2002 09/1/2002


With much appreciation,

--
Buck

  #4  
Old August 24th, 2006, 01:11 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default date gaps

I might try something like the following to get the list of member id's

SELECT [MemberID]
FROM [YourTable] as YT
WHERE DateDiff("d"
,(SELECT Max([EndDate])
FROM [YourTable] as Temp
WHERE Temp.[MemberID] = YT.[MemberID]
AND Temp.[EndDate] YT.[EffectiveDate])
,[EffectiveDate]) 30


"buckpeace" wrote in message
...
I am trying to count members that may have had a gap in enrollment of 30
days. How can I write a query to show me which members had gaps in
enrollment. Here are the fileds I am working with. (see below for
example....1 member has diff lines..."spans" of enrollment if you
will.......) I only want the query to be able to place the number of days
of
the gap in a field to see if they were enrolled.

member ID effective date end date
12345678 10/1/1999 10/31/2000
12345678 11/1/2000 11/30/2000
12345678 05/1/2002 09/1/2002


With much appreciation,

--
Buck



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:07 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.