"Ed Robichaud" <edrobichaud@[EMAIL PROTECTED]
> wrote in message
news:48291462$0$11189$2ff6ac69@[EMAIL PROTECTED]
> Thanks, but I'm not explaining this well enough.
>
> I have attendance-type records that have only an individual's start and
> stop dates - such as:
> ID Start Stop
> 001; 3/2/08; 3/20/08;...
> 002; 3/1/08; 3/18/08;...
> 003; 03/15/08;3/20/08;...
>
> I'd like to be able to show how many ID's were present on any date in
that
> range; ie. from the above, there would be 1 on 3/1/08, 2 on 3/2/08, 3 on
> 3/15/08, 2 on 3/20/08, 0 on 3/21/08, etc.
>
> Is this even possible? I'm stuck with these legacy records as they are.
> Th****s in advance.
> -Ed
>
>
> "Salad" <oil@[EMAIL PROTECTED]
> wrote in message
> news:5qGdncYHU9-4FbXVnZ2dnUVZ_vjinZ2d@[EMAIL PROTECTED]
>> Ed Robichaud wrote:
>>> I'm struggling to come up with a query/re****t of daily attendance for
>>> records that have only start and stop dates. A x-tab (with parameters
>>> that records be within a selected date range) would seem to be the
way
>>> to go here, but how do I get a count for each date within that range?
>>>
>>> thanks -Ed
>> I'm not sure why you need an x-tab.
>>
>> You need to determine which date you want to filter on. The start
date?
>> The stop date?
>>
>> Here's an example of using the start date.
>>
>> SELECT StartDate, Count(StartDate) AS CountOfStartDate
>> FROM YourTableName
>> GROUP BY StartDate
>> HAVING StartDate Between [Enter Start Date] And [Enter End Date]))
>> ORDER BY StartDate;
>>
>> How to create this query
>> Open up the new query (Query/New/Design) and select your table to
query.
>> Drag the start date to two columns. Select View/Totals from the menu.
On
>> the Totals row the first column should be GroupBy, the second Count.
In
>> the criteria row for the first date column enter
>> Between [Enter Start Date] And [Enter End Date]
>> Run the query and see if it does what you want.
>>
>> The Model
>> http://www.youtube.com/watch?v=QgS252XT_Ts
>
How about a function as below.
Fred Zuckerman
'(caution aircode)
Public Function Attendance (fDate as Date) As Integer
Dim rst as DAO.Recordset
set rst = CurrentDb.OpenRecordset("tblLegacy")
rst.MoveFirst
Attendance = 0
Do Until rst.EOF
If fDate Between rst!StartDate And rst!EndDate Then
Attendance = Attendance + 1
Endif
rst.MoveNext
Loop
rst.Close
set rst = Nothing
End Function
|
