Ed Robichaud wrote:
> 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.
Yes it is. I noticed that my response was not what you sought after
reading your response to another responder. You might not like my idea
but I see no way you can do what you want without "normalizing" the data
somewhat...or calling a query and in the query calling a function.
What I'd do is something like this. Create a new table. Call it
Attendance. 2 fields. ID and AttendDate. Air code follows
Sub AttendTable
Dim rst As Recordset
Dim rstA As Recordset
Dim datFor As Date
set rst = Currentdb.openrecordset("Attendance",dbopendynaset)
set rst = Currentdb.openrecordset("CurrentTable",dbopensnapshot)
Do while not rst.Eof
For datFor = rst!DateStart to rst!DateEnd
rstA.Addnew
rstA!ID = rst!ID
rstA!AttendDate = datFor
rstA.Update
Next
rst.MoveNext
Loop
rst.close
rstA.close
set rst = Nothing
set rstA = Nothing
msgbox "Done"
End Sub
Now create a query using table Attendance. Drop the Attenddate into two
columns. Make it a Totals query; first column GroupBy, second column
Count. Put in the criteria
AttendDate Between [Enter Start Date] And [Enter End Date]))
You might want to put the above routine, with edit checks, into the form
when adding/modifying records.
You could modify the above Sub and make it a function, and do something
like
Select ID, AttendTable([ID],[StartDate],[EndDate) As Flag _
From CurrentTable
if you so desired and then call the Totals query. I have no idea if the
data is static, dynamic, or what...so you need to plan how you want to
do it.
> Th****s in advance.
You're welcome.
Say It Right
http://www.youtube.com/watch?v=5IPyoW0o-Gs
> -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
>
>
>
|
