Counting entries

[Cranky]

Hello

Please could someone advise me on the best way to go about this.

I have a table listing phone calls to my office (imaginitively called
"tblCallers") which has 4 fields: Time Called; Time Finished; Reson;
Referred?

I have a second table, "tblTimes" which contains time bands (0830 -
1000; 1001 -1230, etc) and total calls during each band. Currently this
is updated by hand, and I want to automate this.

If I change tblTimes so that it only contains the start time of each
band, (0830; 1001; 1231), for example, how would I go about making the
'total' fields look at tblCallers and count the calls in each band.

I hope this makes sense. It has been an amazingly long day.

Thanks

Steve

 

[Allen Browne]

1. Create a query to get the complete time range, and save as (say)
qryTimes:
SELECT [StartTime],
CDate(Nz((SELECT Min([StartTime]) FROM tblTimes AS Dupe
WHERE Dupe.[StartTime] > tblTimes.[StartTime]), #23:59:59#)) AS NextTime
FROM tblTimes
ORDER BY [StartTime];

2. Create another query using qryTimes and tblCallers as the source tables.
Make sure there is no line joining the 2 tables in the upper pane of the
query design window.

3. Depress the Total button on the Toolbar.
Access adds a Total row the grid.

4. Drag [StartTime] from qryTimes into the grid.
Accept Group By under this field.

5. Drag the primary key of tblCallers into the grid.
Choose Count in the Total row under this field.

6. Drag the [Time Called] field from tblCallers into the grid.
Choose Where in the Total row under this field.
In the Criteria row:

= qryTimes.[StartTime] And < qryTimes.[NextTime]

This query should give you the count of records in each time range. It will
take some time to calculate if you have bucket loads of data.

 

[Cranky]

Allen, thanks for the reply. will try this later.

Steve

 

[Cranky]

1. Create a query to get the complete time range, and save as (say)
qryTimes:
SELECT [StartTime],
CDate(Nz((SELECT Min([StartTime]) FROM tblTimes AS Dupe
WHERE Dupe.[StartTime] > tblTimes.[StartTime]), #23:59:59#)) AS NextTime
FROM tblTimes
ORDER BY [StartTime];

Allen

Everything else made perfect sense, but at the risk of sounding a bit
daft, I'm not sure where to put this.. expression? Code?

Steve

 

[Douglas J. Steele]

1. Create a query to get the complete time range, and save as (say)
qryTimes:
SELECT [StartTime],
CDate(Nz((SELECT Min([StartTime]) FROM tblTimes AS Dupe
WHERE Dupe.[StartTime] > tblTimes.[StartTime]), #23:59:59#)) AS
NextTime
FROM tblTimes
ORDER BY [StartTime];

Allen

Everything else made perfect sense, but at the risk of sounding a bit
daft, I'm not sure where to put this.. expression? Code?

Allen's given you the SQL associated with a query (it's easier to post SQL
than to try and describe how to build the query using the graphical query
interface).

Open a new query, don't select any tables, and select SQL View on the View
menu. Type what Allen's given you into the box that appears. When you switch
back to Design View (again, it's on the View menu), you can see what you
would have had to do in the graphic designer.