Creating a query

[aubtiger14]

Field1	Field2	Field3	Field4	Field5	Field6	Field7	Field8	Field9	Field10
The Honorable	Robert	B.	Aderholt		2369 Rayburn House Office Building	Washington	DC	20515-0104	AL04
The Honorable	Robert	E.	Andrews		2265 Rayburn House Office Building	Washington	DC	20515-3001	NJ01
The Honorable	Justin		Amash		114 Cannon House Office Building	Washington	DC	20515-2203	MI03
The Honorable	Mark	E.	Amodei		222 Cannon House Office Building	Washington	DC	20515-2802	NV02
The Honorable	Spencer		Bachus		2246 Rayburn House Office Building	Washington	DC	20515-0106	AL06
The Honorable	Joe		Barton		2107 Rayburn House Office Building	Washington	DC	20515-4306	TX06
The Honorable	Xavier		Becerra		1226 Longworth House Office Building	Washington	DC	20515-0531	CA34
The Honorable	Sanford	D.	Bishop	Jr.	2429 Rayburn House Office Building	Washington	DC	20515-1002	GA02
The Honorable	Earl		Blumenauer		1111 Longworth House Office Building	Washington	DC	20515-3703	OR03
The Honorable	John	A.	Boehner		1011 Longworth House Office Building	Washington	DC	20515-3508	OH08
The Honorable	Kevin		Brady		301 Cannon House Office Building	Washington	DC	20515-4308	TX08
The Honorable	Corrine		Brown		2111 Rayburn House Office Building	Washington	DC	20515-0903	FL05
The Honorable	Robert	A.	Brady		102 Cannon House Office Building	Washington	DC	20515-3801	PA01
The Honorable	Timothy	H.	Bishop		306 Cannon House Office Building	Washington	DC	20515-3201	NY01
The Honorable	Marsha		Blackburn		217 Cannon House Office Building	Washington	DC	20515-4207	TN07
The Honorable	Madeleine	Z.	Bordallo		2441 Rayburn House Office Building	Washington	DC	20515-5301	GU00
The Honorable	Michael	C.	Burgess		2336 Rayburn House Office Building	Washington	DC	20515-4326	TX26
The Honorable	Rob		Bishop		123 Cannon House Office Building	Washington	DC	20515-4401	UT01
The Honorable	G.	K.	Butterfield		2305 Rayburn House Office Building	Washington	DC	20515-3301	NC01
The Honorable	John		Barrow		2202 Rayburn House Office Building	Washington	DC	20515-1012	GA12
The Honorable	Charles	W.	Boustany	Jr.	1431 Longworth House Office Building	Washington	DC	20515-1807	LA03
The Honorable	Michele		Bachmann		2417 Rayburn House Office Building	Washington	DC	20515-2306	MN06
The Honorable	Gus	M.	Bilirakis		2313 Rayburn House Office Building	Washington	DC	20515-0909	FL12
The Honorable	Bruce	L.	Braley		2263 Rayburn House Office Building	Washington	DC	20515-1501	IA01
The Honorable	Vern		Buchanan		2104 Rayburn House Office Building	Washington	DC	20515-0913	FL16
The Honorable	Paul	C.	Broun		2437 Rayburn House Office Building	Washington	DC	20515-1009	GA10
The Honorable	Lou		Barletta		115 Cannon House Office Building	Washington	DC	20515-3811	PA11
The Honorable	Karen		Bass		408 Cannon House Office Building	Washington	DC	20515-0533	CA37
The Honorable	Dan		Benishek		514 Cannon House Office Building	Washington	DC	20515-2201	MI01
The Honorable	Diane		Black		1531 Longworth House Office Building	Washington	DC	20515-4206	TN06

[aubtiger14]

1. Create a query that displays just the representatives from Alabama, does NOT show the “Prefix”, but shows a count of them. Name the query “Alabama Query”.

[aubtiger14]

how do i do this?

[June7]

Use Access query builder to build an aggregate Totals (GROUP BY) query. Access Help has guidelines. Basic Access functionality. The resulting sql statement will be like:

SELECT Count(*) AS CountReps FROM table WHERE Left(Field10,2)="AL";

If you want to show other info as well, that gets complicated:

SELECT *, (SELECT Count(*) AS CR FROM table WHERE Left(Field10,2)="AL") AS CountReps FROM table WHERE Left(Field10,2)="AL";

or

SELECT *, DCount("*", "table", "Left(Field10,2)='AL'") AS CountReps FROM table WHERE Left(Field10,2)="AL";