Appendix A

U.K. Guidelines on Monitoring Walking and CYcling ActivIty

MONITORING LOCAL CYCLE USE (U.K. DETR 1999) (immediately follows)

MONITORING WALKING (U.K. DETR 2000) (URL link to web)

Appendix B

Literature Review / Phone Surveys

Results of Literature Review / Phone Interviews: Monitoring Cycling Activity

Description	Vancouver	Calgary	Halifax	Windsor	Toronto	Waterloo	Portland	Boston
Conduct cycling survey	P (1998)	P^(1) (2000, 1992)	P (electronic survey – 1999)	· Not known	P (1999 – plan to repeat every 3 years)	P (2002 – survey is unlikely to be repeated in future)	P (2000)
Bicycle counts at selected locations (used for monitoring trends over time)	O^(4) (recently started conducting automatic cycling counts; insufficient data to establish historical trends)	O	P (bridge over harbour – annual count)	O	P (have used permanent count stations in the past but there are none currently in operation; plan to install more at key locations)		P (annual counts at main bicycle bridges)	P (periodic counts at selected locations between 1975 and 1997)
“Before and After” studies	P (required as part of cost-sharing arrangement with provincial government)	O	O	O	P
Conduct turning movement counts with cyclist volumes	P	P	O	P (just started counting cyclists in 2001)	P
Turning movement count data used to monitor cycling activity	P^(2)	O (requested data once, but data format was not user-friendly)	O	O (not enough years of data)	O (volumes not considered reliable at busy intersections)
Conduct screenline counts with cyclist volumes	P	P	O	P (count locations have little cycling activity)	P
Screenline count data used to monitor cycling activity	P^(3) (central area cordon)	P (downtown)	O (hope to begin doing this)	O	P (central area cordon – weather impacts reliability of results)
Household travel survey	P (census data, trip diary survey every 5 years – sample size too small to be statistically reliable at the sub-area level, except downtown)	P (walking and cycling treated as one mode; conducted every 3 to 4 years?)	· Information not provided	P	P (participate in the Transportation Tomorrow Survey, conducted every 5 years)	P (last participated in the Transportation Tomorrow Survey in 1996, will likely participate again in 2006)	P (1994 survey, census data)	P (1991)
Other monitoring activity	· N/A	· Pathwatch – counts on recreational paths, inc. weekend and night counts. Last counts were in 1994; currently conducting new counts. Plan to tie results into GIS. Data used to prioritize projects and justify funding.	· N/A	· Parks people occasionally conduct counts on waterfront trails (last count was approx. 2 years ago)	· N/A	· Conduct employee surveys at employers involved in TDM programs · Currently developing a TDM school curriculum which has opportunities for monitoring travel activity (school audit, family audit) · Also working with community groups such as “Active & Safe Routes to School” which may have monitoring opportunities
Mode share targets	· Downtown, University of BC, city-wide	· CBD and city-wide	· Information not provided	· Do not have cycling mode share targets	· Mode share targets exist – no details provided	· Mode share targets exist – no details provided	· Inner city and city-wide
Other comments	· Do not use traffic count data to monitor OP targets, only O-D data from household surveys	· No monitoring on a year-by-year basis · Would like to monitor cycling activity, but lack of staff continuity hinders efforts · Additional monitoring would likely focus on “generators” and high-use areas · Have looked at using automatic counters but there are problems with reliability · Identified need for winter counts to justify winter maintenance on paths	· “Don’t monitor pedestrian and cycling activity”	· Turning movement counts are “only source” of bicycle activity data that is currently collected · Just beginning to implement cycling improvements (no need to monitor activity until recently) · Will be hiring a bicycle coordinator in the near future who will be responsible for developing a program for monitoring cycling activity	· 2001 Bike Plan recommends development of a bicycle specific data collection program – “it is important that data be collected for peak cycling conditions and also account for weather and seasonal fluctuations” · “Do not do a lot of monitoring at present” (no formal monitoring program exists) · Need conversion factors to seasonally adjust count data	· “Very committed and interested” in monitoring trends · No response from traffic department regarding traffic count program	· “Accurately measuring mode split for bicycling is notoriously difficult” · Further details on Portland’s monitoring program are not available (no response to phone inquiries)	· “Few studies provide data estimating the number of bicyclists” · Did not conduct phone interview due to limited monitoring activity evident from Bicycle Plan
Source of information	Vancouver Bicycle Plan, 1999 Don Klimchuk, Transportation Monitoring Engineer (604-873-7345) Jim Hall, Neighbourhood Transportation Branch (604-873-7130)	Commuter Cyclist Survey 2000: Cycling into Downtown Calgary, 2000 Scott Mackie, Leader, Transportation Data (403-268-5750) Don Mulligan, Co-ordinator, Transportation Solutions (403-268-1652)	David McCusker, Manager of Traffic and Transportation Services (902-490-6696)	Wes Hicks, Manager of Transportation Planning (519-255-6418)	Toronto Bike Plan, 2001 Dan Egan, Manager of Pedestrian and Cycling Infrastructure (416-392-9065)	JoAnn Woodhall, TDM Planner, (519-575-4036)	Bicycle Master Plan Five-Year Update, 2001	Boston Bicycle Plan, 2001

⁽¹⁾ 1992 and 2000 surveys are not directly comparable due to the method used to administer the survey. 2000 survey included a count of cyclists entering the downtown during the AM peak period to compare with cordon counts (AM peak period gives best indication of commuter travel activity). Will likely conduct a similar survey in 5 to 10 years (will be able to compare results with 2000 survey)

⁽²⁾ Data used to examine trends on major bikeways (which generally cross at least one signalized intersection). Data used to justify routes and track usage over time on a route-by-route basis. Do not look at cycling trends on a sub-area basis. Recently started to produce reports showing intersections with the highest cycling volumes. Produced a map illustrating intersection cycling volumes for the 1999 Bicycle Plan. The map identifies preferred cycling routes, but is of limited value for establishing growth rates over time.

⁽³⁾ Plan to establish a new cordon-based program to monitor cycling activity into the downtown on a regular basis “to accurately measure the modal split for bicycles and the effectiveness of cycling programs and initiatives”

⁽⁴⁾ Note that some of the “Before and After” studies of bikeways using manual traffic counts have extended over several years

Results of Literature Review / Phone Interviews: Monitoring Pedestrian Activity

Description	Vancouver	Calgary	Halifax	Windsor	Toronto	Waterloo
Conduct pedestrian survey	P (street-level survey)	P (street-level survey)	O	O	O
Pedestrian counts at selected locations (used for monitoring trends over time)	P ^(2)	P ^(1)	P (Bridge over harbour – annual count)	O	O (occasionally conduct special counts to meet specific project needs)
Conduct turning movement counts with pedestrian volumes	P	P	O	P	O
Turning movement count data used to monitor pedestrian activity	O (plan to start doing so shortly using new software)	O	O	O	O
Conduct screenline counts with pedestrian volumes	· Not known	P	O	· Not known	O
Screenline count data used to monitor pedestrian activity	O	O	O (hope to begin doing this)	O	O
Household travel survey	P (census data, trip diary survey every 5 years – sample size too small to be statistically reliable at the sub-area level)	P (walking and cycling treated as one mode; conducted every 3 to 4 years?)	· Information not provided	P	P (participate in the Transportation Tomorrow Survey, conducted every 5 years)	P (last participated in the Transportation Tomorrow Survey in 1996, will likely participate again in 2006)
Other monitoring activity	· N/A	· Pathwatch (1994) – counts on recreational paths (not clear if pedestrians included)	· N/A	· Parks people occasionally conduct counts on waterfront trails (last count was approx. 2 years ago)	· N/A	· See comments under cycling
Mode share targets	· Downtown, University of BC, city-wide	· CBD and city-wide	· Information not provided	· Do not have pedestrian mode share targets	· Mode share targets exist – no details provided	· Mode share targets exist – no details provided
Other comments	· Have not attempted to monitor OP targets · Pedestrian activity is typically higher on the sunny side of the street · Do not use traffic count data to monitor OP targets, only O-D data from household surveys	· No monitoring on a year-by-year basis · No monitoring done outside the CBD · Accessibility of data is the key issue. People don’t know what data is available and as a result, the data is underutilized	· Occasionally count pedestrians as part of specific studies (i.e. to assess the need for a crosswalk)	· None	· None	· See comments under cycling
Sources of information	Don Klimchuk, Transportation Monitoring Engineer (604-873-7345) Jim Hall, Neighbourhood Transportation Branch (604-873-7130)	Scott Mackie, Leader, Transportation Data (403-268-5750) Don Mulligan, Co-ordinator, Transportation Solutions (403-268-1652)	David McCusker, Manager of Traffic and Transportation Services (902-490-6696)	Wes Hicks, Manager of Transportation Planning (519-255-6418)	Dan Egan, Manager of Pedestrian and Cycling Infrastructure (416-392-9065)	JoAnn Woodhall, TDM Planner, (519-575-4036)

⁽¹⁾ Recently surveyed 30-40 locations in the CBD. Recorded gender & age info. Data to be used in CBD planning study and by downtown BIA. Will likely repeat study in future with smaller sample size to keep database up-to-date. Will likely compare results with a less comprehensive study completed 8 years ago, however, monitoring trends over time is NOT the primary objective.

⁽²⁾ Recently re-started (and expanded) a program to count pedestrians at midblock locations in commercial areas (mostly downtown). Locations will be counted every 5 years (during May and June to avoid peak tourist times). 350 “block faces” included in program. Control locations monitored to establish seasonal and daily variations. As part of this program, the City has conducted short opinion surveys on pedestrian travel, including OD information. Previous counts were conducted in 1977 and 1991. Will likely compare recent data with older data to establish trends on a street-by-street basis, aggregating data to determine trends for the downtown area as whole

Monitoring Pedestrian and Cycling Activity in Helsinki, Finland

E-mail Extract, October 9, 2002

We have no continuos monitoring system for pedestrians. In the city center we arranged 1989 large counting by using video cameras from the air( by helicopter). We made two flights over the areas so that every part of all the streets in the center area could be counted. In the center we had about 40 manual counting points to get the day's hourly variation for different types of streets. We used coefficients for each part for every street.

If any traffic planner needs information of pedestrian traffic in intersections, we count manually.

Our yearly calculation system of modal split in the border of city center does not contain at all trips made by foot or bicycle. We count the modal split of motor vehicle trips for the border of city center every September.

The Metropolitan Area Council makes origin-destination surveys about every 10 year to get the modal split for the different cities and for trips between different areas in the metropolitan area. In that system they take account all types of trips.

Ms. Irene Lilleberg

Helsinki City Planning Department

Traffic Planning Division

Address: P.O. Box 2101

FIN-00099 THE CITY OF HELSINKI

FINLAND

Monitoring Pedestrian and Cycling Activity in Helsinki, Finland

E-mail Extract, October 9, 2002

In Helsinki we count bicycles with machine and manually. We have 11 counting points with machine. Four of them we count around a year. We get data 0 am – 12 pm. Our counting system based on Marksman. Over the bicycle road have dug loop and beside the road is counting box. When bicyclist is driving over the loop machine registered him. We collect data once a month and we print out results hourly even every fifteen minutes.

In summer we also count bicycles manually on working days from 7.00 am to 7.00 pm. Then we also count utilization rate of bicycle helmet. Every third year we count whole Centre boundary and Inner City boundary.

Ms. Tuija Hellman

Helsinki City Planning Department

Traffic Planning Division

Address: P.O. Box 2101

FIN-00099 THE CITY OF HELSINKI

FINLAND

Monitoring Pedestrian and Cycling Activity in Helsinki, Finland

E-mail Extract, October 16, 2002

… we use the [bicycle] counts to determine what roads require bicycle facilities. For example Planers use the counts for planning.

… we don't calculate collision rates based on these counts, but we compare bicycle counts to bicycle accidents.

… we compare machine and also manually counting changes in different areas of the city and for one year to the next, especially changes in summertime. Our best cycling season is from April to September.

Our machine count stations give us you a good idea of what's happening in the downtown area, but also quite good idea across the entire city. Manual counts give us good idea of what's happening across the entire city in summertime. Our machine count stations located in downtown, beside the roads and bridges coming to downtown, in suburban areas and in recreation areas. The machine count stations located only on separate bicycle paths beside the roadways. Manually we sometimes also count on roadways used by both vehicles and bicycles.

We use the machine counts to calculate seasonal / day of week / hourly/ time

period adjustment factors that can be applied to other count data.

Ms. Tuija Hellman

Helsinki City Planning Department

Traffic Planning Division

Address: P.O. Box 2101

FIN-00099 THE CITY OF HELSINKI

FINLAND

Monitoring Pedestrian and Cycling Activity in Helsinki, Finland

E-mail Extract, October 17, 2002

Mainly we use the machine and manual counts to compare changes at individual count locations from one year to the next. But every year we count bicycles in seven places at the Centre boundary. So we know how many bicyclists are crossing the boundary during one day and how it is changing from one year to the next. Every third year we count more exactly Centre boundary (then 13 places) and Inner City boundary (25 places). Then we know how many bicyclists are crossing Centre boundary and Inner city boundary.

Ms. Tuija Hellman

Helsinki City Planning Department

Traffic Planning Division

Address: P.O. Box 2101

FIN-00099 THE CITY OF HELSINKI

FINLAND

E-mail Extract from the Chair of the Transportation Research Board Bicycling Committee, October 2, 2002

I am not aware of anyone who does bicycle counts other than specifically for bicycle planning issues. Including bicycles in automated traffic counts is complicated by many factors. The specially designed bicycle counters really only work well on paths. I think some people use the 360 degree cameras to include bicycle counts but even then a person must manually sit in front of the video and count. I have talked with the Connecticut Bicycle Coalition about doing random counts at random intersections in order to measure mode share (both the motor vehicles and bicycles would be counted). Because the intersections are randomly selected, given a big enough sample then some defensible estimates could be made. Usually bicycle counts are taken along heavily used routes which skews mode share estimates.

Lisa Aultman-Hall, Ph.D.

Associate Professor

Department of Civil and Environmental Engineering

261 Glenbrook Road, Unit 2037

University of Connecticut

Storrs, CT

Results of Literature Review - General Comments

· Although there is a general desire to monitor pedestrian and cycling activity, in practice, few cities do so due to a lack of data (Bicycle and Pedestrian Data: Sources, Needs, & Gaps, U.S. DOT, 2000)

· Monitoring is often limited to before and after counts on a particular facility (limited usefulness for monitoring region-wide trends, as an increase in activity on one route may not reflect an increase in the number of cycling trips)

· Some cities use counts on existing cycling facilities to estimate demand on other facilities (Guidebook on Methods to Estimate Non-motorized Travel, Schwartz et al., 1999)

· U.K. Guidelines for Monitoring Cycling Activity (DETR 1999)

- Importance of sample size

- Screenline approach “most practical way to monitor cycling trends from one year to the next”

- Random sample of bicycle counts to estimate absolute level of cycling (can be translated into bicycle kilometers of travel)

· U.K. Guidelines for Monitoring Pedestrian Activity (DETR 2000)

- Choose sites with high levels of walking activity, near major generators

Appendix C

Summary of Screenline Traffic Count Data

City of Ottawa Screenline and Cordon System

Summary of Screenline Traffic Count Data

Screenline Number	Number of Stations	Number of Stations Counted in…
		1994-1995	1996-1997	1998-1999	2000-2001
2	1	1	1	1	1
3^2	2	2	2	2	2
4^2	2	2	2	2	2
6	5		4
7	4		4
8	5		4	4	4
9	7	6	7	7	7
10	7	6	7	7	7
11	5	5	5	5	5
12	7	6	7	7	7
13	8	6	7	7	7
14	3	3	2	2	2
16	5	5	5	5	5
17	9		9		1
18	3		3		3
19^2	3	3	3	3	3
20	3	3	3	3	3
21	4	1	4	1	4
22	7		7		7
23	5		5		5
24	6	6	6		3
25	7	6	6
26	15		15		13
27^2	5	5	5	5	5
28^2	1	1	1	1	1
29^2	6	6	6	6	6
30	16		16		13
31	8		7		6
32^2	2	2	2	2	2
33^2	4	3	4	4	4
34	4		4		3
35^1	7	7	7	7	7
36^1	15	15	15	15	15
37^1	9	9	9	9	9
38^1	7	7	7	7	7
39	5		4		3
40	13		13		13
41	10		10		10
42	1	1	1	1	1
43	3		3		3
44	6		6		4
45	8	1	8
46	7	4	5	5	5
47	5		5
48	4		4
49	8		7	8	8
50	6		6		6
51	7		7
52	4		4
53	5		5	5	3
54	9		9		5

Screenlines that were fully counted at least once every two years between 1995 and 2001

¹ Screenlines included in the Central Area cordon (as defined in this report)

² Screenlines included in the Inner Area cordon (as defined in this report)

Appendix D

Summary of Traffic Count Data

Screenline and Cordon Volumes

Data Summary: Central and Inner Area Cordons

Central Sector – 8-Hour Bicycle Volumes

Central Sector – 8-Hour Vehicle Volumes

Southeast Sector – 8-Hour Bicycle Volumes

Southeast Sector – 8-Hour Vehicle Volumes

Appendix E

Database Queries

Summary of Tables in Database:

Summary of Queries in Database:

Query Name	SQL Code
TM_Bikes_Query1	SELECT Index_Intersections.Zone, Index_Intersections.Location_ID, Index_Intersections.Street1, Index_Intersections.Street2, TM_HEADER_jma.ROAD_COND, TM_HEADER_jma.STREET1_RESTRICT, TM_HEADER_jma.STREET2_RESTRICT, TM_HEADER_jma.APPROVAL_COMMENT, TM_HEADER_jma.Year, Count(TM_Summary2.[8HR_VOLUME]) AS CountOf8HR_VOLUME, Avg(TM_Summary2.[8HR_VOLUME]) AS AvgOf8HR_VOLUME, TM_HEADER_jma.BIKE_COUNT_IND FROM TM_Summary2 INNER JOIN (Index_Intersections INNER JOIN (TM_HEADER_jma INNER JOIN TSIPS_R_TM_LOCATION_copy ON TM_HEADER_jma.LOCATION_ID = TSIPS_R_TM_LOCATION_copy.LOCATION_ID) ON Index_Intersections.Location_ID = TM_HEADER_jma.LOCATION_ID) ON TM_Summary2.COUNT_ID = TM_HEADER_jma.COUNT_ID WHERE (((TM_HEADER_jma.Year)<>"02") AND ((Index_Intersections.Include)="Yes" Or (Index_Intersections.Include)="Test") AND ((TM_Summary2.TM_ID)=4) AND ((TM_Summary2.[8HR_VOLUME])>0)) GROUP BY Index_Intersections.Zone, Index_Intersections.Location_ID, Index_Intersections.Street1, Index_Intersections.Street2, TM_HEADER_jma.ROAD_COND, TM_HEADER_jma.STREET1_RESTRICT, TM_HEADER_jma.STREET2_RESTRICT, TM_HEADER_jma.APPROVAL_COMMENT, TM_HEADER_jma.Year, TM_HEADER_jma.BIKE_COUNT_IND HAVING (((TM_HEADER_jma.BIKE_COUNT_IND)="Y")) ORDER BY Index_Intersections.Zone, Index_Intersections.Street1, Index_Intersections.Street2, TM_HEADER_jma.Year;
TM_Bikes_Query2	TRANSFORM Avg(TM_Bikes_Query1.AvgOf8HR_VOLUME) AS AvgOfAvgOf8HR_VOLUME SELECT TM_Bikes_Query1.Zone, TM_Bikes_Query1.Location_ID, TM_Bikes_Query1.Street1, TM_Bikes_Query1.Street2, TM_Bikes_Query1.ROAD_COND, TM_Bikes_Query1.STREET1_RESTRICT, TM_Bikes_Query1.STREET2_RESTRICT, TM_Bikes_Query1.APPROVAL_COMMENT FROM TM_Bikes_Query1 INNER JOIN Year_Categories ON TM_Bikes_Query1.Year = Year_Categories.Year WHERE (((TM_Bikes_Query1.AvgOf8HR_VOLUME)>0)) GROUP BY TM_Bikes_Query1.Zone, TM_Bikes_Query1.Location_ID, TM_Bikes_Query1.Street1, TM_Bikes_Query1.Street2, TM_Bikes_Query1.ROAD_COND, TM_Bikes_Query1.STREET1_RESTRICT, TM_Bikes_Query1.STREET2_RESTRICT, TM_Bikes_Query1.APPROVAL_COMMENT ORDER BY TM_Bikes_Query1.Zone, TM_Bikes_Query1.Street1, TM_Bikes_Query1.Street2 PIVOT Year_Categories.Year_Range;
TM_Bikes_Query3a	SELECT TM_Bikes_Query2.Zone, TM_Bikes_Query2.Location_ID, TM_Bikes_Query2.Street1, TM_Bikes_Query2.Street2, TM_Bikes_Query2.[1994-1995], TM_Bikes_Query2.[1996-1997], TM_Bikes_Query2.[1998-1999], TM_Bikes_Query2.[2000-2001] FROM TM_Bikes_Query2 WHERE (((TM_Bikes_Query2.ROAD_COND)="Dry") AND ((TM_Bikes_Query2.STREET1_RESTRICT)="N") AND ((TM_Bikes_Query2.STREET2_RESTRICT)="N") AND ((TM_Bikes_Query2.APPROVAL_COMMENT) Is Null)) GROUP BY TM_Bikes_Query2.Zone, TM_Bikes_Query2.Location_ID, TM_Bikes_Query2.Street1, TM_Bikes_Query2.Street2, TM_Bikes_Query2.[1994-1995], TM_Bikes_Query2.[1996-1997], TM_Bikes_Query2.[1998-1999], TM_Bikes_Query2.[2000-2001];
TM_Bikes_Query3b	SELECT TM_Bikes_Query2.Zone, TM_Bikes_Query2.Location_ID, TM_Bikes_Query2.Street1, TM_Bikes_Query2.Street2, Avg([TM_Bikes_Query2]![1994-1995]) AS [1994-1995], Avg([TM_Bikes_Query2]![1996-1997]) AS [1996-1997], Avg([TM_Bikes_Query2]![1998-1999]) AS [1998-1999], Avg([TM_Bikes_Query2]![2000-2001]) AS [2000-2001] FROM TM_Bikes_Query2 WHERE (((TM_Bikes_Query2.ROAD_COND)<>"Dry")) OR (((TM_Bikes_Query2.STREET1_RESTRICT)<>"N")) OR (((TM_Bikes_Query2.STREET2_RESTRICT)<>"N")) OR (((TM_Bikes_Query2.APPROVAL_COMMENT) Is Not Null)) GROUP BY TM_Bikes_Query2.Zone, TM_Bikes_Query2.Location_ID, TM_Bikes_Query2.Street1, TM_Bikes_Query2.Street2;
TM_Bikes_Query3c	SELECT Index_Intersections.Zone, Index_Intersections.Location_ID, Index_Intersections.Street1, Index_Intersections.Street2, IIf([TM_Bikes_Query3a]![1994-1995]>0,[TM_Bikes_Query3a]![1994-1995],[TM_Bikes_Query3b]![1994-1995]) AS [1994-1995], IIf(IsNull([TM_Bikes_Query3a]![1996-1997]),[TM_Bikes_Query3b]![1996-1997],[TM_Bikes_Query3a]![1996-1997]) AS [1996-1997], IIf(IsNull([TM_Bikes_Query3a]![1998-1999]),[TM_Bikes_Query3b]![1998-1999],[TM_Bikes_Query3a]![1998-1999]) AS [1998-1999], IIf(IsNull([TM_Bikes_Query3a]![2000-2001]),[TM_Bikes_Query3b]![2000-2001],[TM_Bikes_Query3a]![2000-2001]) AS [2000-2001] FROM (TM_Bikes_Query3b RIGHT JOIN Index_Intersections ON TM_Bikes_Query3b.Location_ID = Index_Intersections.Location_ID) LEFT JOIN TM_Bikes_Query3a ON Index_Intersections.Location_ID = TM_Bikes_Query3a.Location_ID WHERE (((Index_Intersections.Type)="tm")) GROUP BY Index_Intersections.Zone, Index_Intersections.Location_ID, Index_Intersections.Street1, Index_Intersections.Street2, IIf([TM_Bikes_Query3a]![1994-1995]>0,[TM_Bikes_Query3a]![1994-1995],[TM_Bikes_Query3b]![1994-1995]), IIf(IsNull([TM_Bikes_Query3a]![1996-1997]),[TM_Bikes_Query3b]![1996-1997],[TM_Bikes_Query3a]![1996-1997]), IIf(IsNull([TM_Bikes_Query3a]![1998-1999]),[TM_Bikes_Query3b]![1998-1999],[TM_Bikes_Query3a]![1998-1999]), IIf(IsNull([TM_Bikes_Query3a]![2000-2001]),[TM_Bikes_Query3b]![2000-2001],[TM_Bikes_Query3a]![2000-2001]);
Create_TMSummary2	SELECT TM_Summary1.COUNT_ID, TM_Summary1.TM_ID, Sum(TM_Summary1.Volume) AS SumOfVolume INTO TM_Summary2 FROM TM_Summary1 INNER JOIN TM_HEADER_jma ON TM_Summary1.COUNT_ID = TM_HEADER_jma.COUNT_ID WHERE (((TM_Summary1.Time)=" 7:00:00 AM" Or (TM_Summary1.Time)=" 7:15:00 AM" Or (TM_Summary1.Time)=" 7:30:00 AM" Or (TM_Summary1.Time)=" 7:45:00 AM" Or (TM_Summary1.Time)=" 8:00:00 AM" Or (TM_Summary1.Time)=" 8:15:00 AM" Or (TM_Summary1.Time)=" 8:30:00 AM" Or (TM_Summary1.Time)=" 8:45:00 AM" Or (TM_Summary1.Time)=" 9:00:00 AM" Or (TM_Summary1.Time)=" 9:15:00 AM" Or (TM_Summary1.Time)=" 9:30:00 AM" Or (TM_Summary1.Time)=" 9:45:00 AM" Or (TM_Summary1.Time)="11:30:00 AM" Or (TM_Summary1.Time)="11:45:00 AM" Or (TM_Summary1.Time)="12:00:00 PM" Or (TM_Summary1.Time)="12:15:00 PM" Or (TM_Summary1.Time)="12:30:00 PM" Or (TM_Summary1.Time)="12:45:00 PM" Or (TM_Summary1.Time)=" 1:00:00 PM" Or (TM_Summary1.Time)=" 1:15:00 PM" Or (TM_Summary1.Time)=" 3:00:00 PM" Or (TM_Summary1.Time)=" 3:15:00 PM" Or (TM_Summary1.Time)=" 3:30:00 PM" Or (TM_Summary1.Time)=" 3:45:00 PM" Or (TM_Summary1.Time)=" 4:00:00 PM" Or (TM_Summary1.Time)=" 4:15:00 PM" Or (TM_Summary1.Time)=" 4:30:00 PM" Or (TM_Summary1.Time)=" 4:45:00 PM" Or (TM_Summary1.Time)=" 5:00:00 PM" Or (TM_Summary1.Time)=" 5:15:00 PM" Or (TM_Summary1.Time)=" 5:30:00 PM" Or (TM_Summary1.Time)=" 5:45:00 PM") AND ((TM_HEADER_jma.COUNT_LENGTH)=8 Or (TM_HEADER_jma.COUNT_LENGTH)=11 Or (TM_HEADER_jma.COUNT_LENGTH)=12) AND ((Right([TM_HEADER_jma]![START_TIME],10))="7:00:00 AM") AND ((TM_HEADER_jma.BIKE_COUNT_IND)="Y")) GROUP BY TM_Summary1.COUNT_ID, TM_Summary1.TM_ID;
Create_TMSummary1	SELECT TSIPS_A_TM_DETAILS_copy.COUNT_ID, TSIPS_A_TM_DETAILS_copy.TM_ID, Right([TSIPS_A_TM_DETAILS_copy]![INTERVAL_TIME],11) AS Expr2, [TSIPS_A_TM_DETAILS_copy]![N_LEFT_COUNT]+[TSIPS_A_TM_DETAILS_copy]![N_STRAIGHT_COUNT]+[TSIPS_A_TM_DETAILS_copy]![N_RIGHT_COUNT]+[TSIPS_A_TM_DETAILS_copy]![E_LEFT_COUNT]+[TSIPS_A_TM_DETAILS_copy]![E_STRAIGHT_COUNT]+[TSIPS_A_TM_DETAILS_copy]![E_RIGHT_COUNT]+[TSIPS_A_TM_DETAILS_copy]![S_LEFT_COUNT]+[TSIPS_A_TM_DETAILS_copy]![S_STRAIGHT_COUNT]+[TSIPS_A_TM_DETAILS_copy]![S_RIGHT_COUNT]+[TSIPS_A_TM_DETAILS_copy]![W_LEFT_COUNT]+[TSIPS_A_TM_DETAILS_copy]![W_STRAIGHT_COUNT]+[TSIPS_A_TM_DETAILS_copy]![W_RIGHT_COUNT] AS Expr1 INTO TM_Summary1 FROM TSIPS_A_TM_DETAILS_copy;
Screenline_Bike_1	TRANSFORM Avg(CL_Summary2.[8HR_BIKES]) AS AvgOf8HR_BIKES SELECT TSIPS_R_CL_LOCATION_copy.SCREEN_LINE, TSIPS_R_CL_LOCATION_copy.STATION_NUMBER, Year_Categories.Year_Range FROM (CL_Summary2 INNER JOIN (TSIPS_R_CL_LOCATION_copy INNER JOIN CL_HEADER_jma ON TSIPS_R_CL_LOCATION_copy.STATION_NUMBER = CL_HEADER_jma.STATION_NUMBER) ON CL_Summary2.COUNT_ID = CL_HEADER_jma.COUNT_ID) INNER JOIN Year_Categories ON CL_HEADER_jma.Year = Year_Categories.Year WHERE (((TSIPS_R_CL_LOCATION_copy.SCREEN_LINE)>0)) GROUP BY TSIPS_R_CL_LOCATION_copy.SCREEN_LINE, TSIPS_R_CL_LOCATION_copy.STATION_NUMBER, Year_Categories.Year_Range ORDER BY TSIPS_R_CL_LOCATION_copy.SCREEN_LINE PIVOT CL_HEADER_jma.ANOMALLY_IND;
Screenline_Bikes_ByGrouping	TRANSFORM Sum((IIf(IsNull([Screenline_Bike_1]![N]),[Screenline_Bike_1]![Y],[Screenline_Bike_1]![N]))) AS Expr1 SELECT Screenline_Groupings.Grouping FROM Screenline_Groupings INNER JOIN Screenline_Bike_1 ON Screenline_Groupings.Screenline = Screenline_Bike_1.SCREEN_LINE GROUP BY Screenline_Groupings.Grouping PIVOT Screenline_Bike_1.Year_Range;
Screenline_Bikes_Details	TRANSFORM Sum((IIf(IsNull([Screenline_Bike_1]![N]),[Screenline_Bike_1]![Y],[Screenline_Bike_1]![N]))) AS Expr1 SELECT Screenline_Bike_1.SCREEN_LINE, Screenline_Bike_1.STATION_NUMBER FROM Screenline_Bike_1 GROUP BY Screenline_Bike_1.SCREEN_LINE, Screenline_Bike_1.STATION_NUMBER PIVOT Screenline_Bike_1.Year_Range;
Screenline_StationCount	SELECT TSIPS_R_CL_LOCATION_copy.SCREEN_LINE, Count(TSIPS_R_CL_LOCATION_copy.STATION_NUMBER) AS CountOfSTATION_NUMBER FROM TSIPS_R_CL_LOCATION_copy GROUP BY TSIPS_R_CL_LOCATION_copy.SCREEN_LINE;
Screenline_VolumeCheck	TRANSFORM Count(Screenline_Bike_1.Year_Range) AS CountOfYear_Range SELECT Screenline_Bike_1.SCREEN_LINE, Screenline_StationCount.CountOfSTATION_NUMBER FROM Screenline_StationCount INNER JOIN Screenline_Bike_1 ON Screenline_StationCount.SCREEN_LINE = Screenline_Bike_1.SCREEN_LINE GROUP BY Screenline_Bike_1.SCREEN_LINE, Screenline_StationCount.CountOfSTATION_NUMBER ORDER BY Screenline_Bike_1.SCREEN_LINE PIVOT Screenline_Bike_1.Year_Range;
Create_CLSummary1	SELECT TSIPS_A_CL_DETAILS_copy.COUNT_ID, TSIPS_A_CL_DETAILS_copy.DIR_ID, Right([TSIPS_A_CL_DETAILS_copy]![INTERVAL_TIME],11) AS Expr1, [TSIPS_A_CL_DETAILS_copy]![BIKE_1]+[TSIPS_A_CL_DETAILS_copy]![BIKE_2] AS Expr2, TSIPS_A_CL_DETAILS_copy.PEDESTRIAN, [TSIPS_A_CL_DETAILS_copy]![AUTO_1]+[TSIPS_A_CL_DETAILS_copy]![AUTO_2]+[TSIPS_A_CL_DETAILS_copy]![AUTO_3]+[TSIPS_A_CL_DETAILS_copy]![TAXI_1]+[TSIPS_A_CL_DETAILS_copy]![TAXI_2]+[TSIPS_A_CL_DETAILS_copy]![TAXI_3]+[TSIPS_A_CL_DETAILS_copy]![P_TRANS_OC]+[TSIPS_A_CL_DETAILS_copy]![P_TRANS_OU]+[TSIPS_A_CL_DETAILS_copy]![SCHOOL_BUS]+[TSIPS_A_CL_DETAILS_copy]![INTER_CITY_BUS]+[TSIPS_A_CL_DETAILS_copy]![OTHER_BUS]+[TSIPS_A_CL_DETAILS_copy]![L_TRUCKS_1]+[TSIPS_A_CL_DETAILS_copy]![L_TRUCKS_2]+[TSIPS_A_CL_DETAILS_copy]![L_TRUCKS_3]+[TSIPS_A_CL_DETAILS_copy]![H_TRUCKS_1]+[TSIPS_A_CL_DETAILS_copy]![H_TRUCKS_2]+[TSIPS_A_CL_DETAILS_copy]![H_TRUCKS_3]+[TSIPS_A_CL_DETAILS_copy]![OTHER_VEH] AS Expr3 INTO CL_Summary1 FROM TSIPS_A_CL_DETAILS_copy;
Create_CLSummary2	SELECT CL_Summary1.COUNT_ID, Sum(CL_Summary1.Bikes) AS SumOfBikes, Sum(CL_Summary1.Pedestrians) AS SumOfPedestrians, Sum(CL_Summary1.Vehicles) AS SumOfVehicles INTO CL_Summary2 FROM CL_HEADER_jma INNER JOIN CL_Summary1 ON CL_HEADER_jma.COUNT_ID = CL_Summary1.COUNT_ID WHERE (((CL_Summary1.Time)=" 7:00:00 AM" Or (CL_Summary1.Time)=" 7:15:00 AM" Or (CL_Summary1.Time)=" 7:30:00 AM" Or (CL_Summary1.Time)=" 7:45:00 AM" Or (CL_Summary1.Time)=" 8:00:00 AM" Or (CL_Summary1.Time)=" 8:15:00 AM" Or (CL_Summary1.Time)=" 8:30:00 AM" Or (CL_Summary1.Time)=" 8:45:00 AM" Or (CL_Summary1.Time)=" 9:00:00 AM" Or (CL_Summary1.Time)=" 9:15:00 AM" Or (CL_Summary1.Time)=" 9:30:00 AM" Or (CL_Summary1.Time)=" 9:45:00 AM" Or (CL_Summary1.Time)="11:30:00 AM" Or (CL_Summary1.Time)="11:45:00 AM" Or (CL_Summary1.Time)="12:00:00 PM" Or (CL_Summary1.Time)="12:15:00 PM" Or (CL_Summary1.Time)="12:30:00 PM" Or (CL_Summary1.Time)="12:45:00 PM" Or (CL_Summary1.Time)=" 1:00:00 PM" Or (CL_Summary1.Time)=" 1:15:00 PM" Or (CL_Summary1.Time)=" 3:00:00 PM" Or (CL_Summary1.Time)=" 3:15:00 PM" Or (CL_Summary1.Time)=" 3:30:00 PM" Or (CL_Summary1.Time)=" 3:45:00 PM" Or (CL_Summary1.Time)=" 4:00:00 PM" Or (CL_Summary1.Time)=" 4:15:00 PM" Or (CL_Summary1.Time)=" 4:30:00 PM" Or (CL_Summary1.Time)=" 4:45:00 PM" Or (CL_Summary1.Time)=" 5:00:00 PM" Or (CL_Summary1.Time)=" 5:15:00 PM" Or (CL_Summary1.Time)=" 5:30:00 PM" Or (CL_Summary1.Time)=" 5:45:00 PM") AND ((CL_HEADER_jma.COUNT_LENGTH)=8 Or (CL_HEADER_jma.COUNT_LENGTH)=11 Or (CL_HEADER_jma.COUNT_LENGTH)=12)) GROUP BY CL_Summary1.COUNT_ID;
Screenline_Anomalies	SELECT Screenline_Groupings.Grouping, Screenline_Groupings.Screenline, Screenline_Bike_1.STATION_NUMBER, CL_Summary2.COUNT_ID, CL_HEADER_jma.Year, CL_HEADER_jma.ANOMALLY_IND, CL_HEADER_jma.ANOMALLY_COMMENT, IIf(IsNull([Screenline_Bike_1]![N]),"Used","Did not use") AS Anomaly FROM Year_Categories INNER JOIN ((CL_Summary2 INNER JOIN CL_HEADER_jma ON CL_Summary2.COUNT_ID = CL_HEADER_jma.COUNT_ID) INNER JOIN (Screenline_Groupings INNER JOIN Screenline_Bike_1 ON Screenline_Groupings.Screenline = Screenline_Bike_1.SCREEN_LINE) ON CL_HEADER_jma.STATION_NUMBER = Screenline_Bike_1.STATION_NUMBER) ON (Year_Categories.Year_Range = Screenline_Bike_1.Year_Range) AND (Year_Categories.Year = CL_HEADER_jma.Year) GROUP BY Screenline_Groupings.Grouping, Screenline_Groupings.Screenline, Screenline_Bike_1.STATION_NUMBER, CL_Summary2.COUNT_ID, CL_HEADER_jma.Year, CL_HEADER_jma.ANOMALLY_IND, CL_HEADER_jma.ANOMALLY_COMMENT, IIf(IsNull([Screenline_Bike_1]![N]),"Used","Did not use") HAVING (((CL_HEADER_jma.ANOMALLY_IND)="Y")) ORDER BY Screenline_Groupings.Grouping, Screenline_Groupings.Screenline, Screenline_Bike_1.STATION_NUMBER, CL_Summary2.COUNT_ID;
CL_Bikes_Query1	SELECT Index_Intersections_1.Zone, Index_Intersections_1.Station_ID, Index_Intersections_1.Street1, Index_Intersections_1.Street2, CL_HEADER_jma.WEATHER, CL_HEADER_jma.ANOMALLY_IND, CL_HEADER_jma.ANOMALLY_COMMENT, CL_HEADER_jma.Year, Count(CL_Summary2.[8HR_BIKES]) AS CountOf8HR_BIKES, Avg(CL_Summary2.[8HR_BIKES]) AS AvgOf8HR_BIKES, Avg(CL_Summary2.[8HR_VEHS]) AS AvgOf8HR_VEHS FROM ((TSIPS_R_CL_LOCATION_copy INNER JOIN CL_HEADER_jma ON TSIPS_R_CL_LOCATION_copy.STATION_NUMBER = CL_HEADER_jma.STATION_NUMBER) INNER JOIN Index_Intersections AS Index_Intersections_1 ON CL_HEADER_jma.STATION_NUMBER = Index_Intersections_1.Station_ID) INNER JOIN CL_Summary2 ON CL_HEADER_jma.COUNT_ID = CL_Summary2.COUNT_ID WHERE (((CL_HEADER_jma.Year)<>"02") AND ((Index_Intersections_1.Include)="Yes") AND ((CL_Summary2.[8HR_BIKES])>0)) GROUP BY Index_Intersections_1.Zone, Index_Intersections_1.Station_ID, Index_Intersections_1.Street1, Index_Intersections_1.Street2, CL_HEADER_jma.WEATHER, CL_HEADER_jma.ANOMALLY_IND, CL_HEADER_jma.ANOMALLY_COMMENT, CL_HEADER_jma.Year ORDER BY Index_Intersections_1.Zone, Index_Intersections_1.Street1, Index_Intersections_1.Street2, CL_HEADER_jma.Year;
CL_Bikes_Query2	TRANSFORM Avg(CL_Bikes_Query1.AvgOf8HR_BIKES) AS AvgOfAvgOf8HR_BIKES SELECT CL_Bikes_Query1.Zone, CL_Bikes_Query1.Station_ID, CL_Bikes_Query1.Street1, CL_Bikes_Query1.Street2, CL_Bikes_Query1.WEATHER, CL_Bikes_Query1.ANOMALLY_IND, CL_Bikes_Query1.ANOMALLY_COMMENT FROM Year_Categories INNER JOIN CL_Bikes_Query1 ON Year_Categories.Year = CL_Bikes_Query1.Year WHERE (((CL_Bikes_Query1.AvgOf8HR_BIKES)>0)) GROUP BY CL_Bikes_Query1.Zone, CL_Bikes_Query1.Station_ID, CL_Bikes_Query1.Street1, CL_Bikes_Query1.Street2, CL_Bikes_Query1.WEATHER, CL_Bikes_Query1.ANOMALLY_IND, CL_Bikes_Query1.ANOMALLY_COMMENT ORDER BY CL_Bikes_Query1.Zone, CL_Bikes_Query1.Street1, CL_Bikes_Query1.Street2 PIVOT Year_Categories.Year_Range;

Sample Query Design Form in MS Access: