Counting entries

  • Thread starter Thread starter Cranky
  • Start date Start date
C

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
 
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]
Click to expand...

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.
 
Allen said:
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];
Click to expand...

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
 
Cranky said:
Allen said:
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];
Click to expand...

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?
Click to expand...

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.
 
Back
Top