muli cost no same state

[slimjen]

All; Using Access 2010. I have the following table:

COST_NO	ST	TASK
1234	KY	TNT
1234a	KY	TNT
1234b	KY	AVT
1234b	KY	TNT
1234c	KY	TNT
1234c	IL	TNT
1234d	WA	TNT
2345	FL	TNT
3456	AR	TNT
3456a	AR	TNT
3456c	VT	TNT
5897	WA	TNT
6958	MO	TNT

I would like a query to return only the with the cost_no being the same(match first four) in the same state and the same task. Given the table above; I want this result:

COST_NO
ST
TASK

1234
KY
TNT

1234a
KY
TNT

1234b
KY
TNT

1234c
KY
TNT

3456
AR
TNT

3456a
AR
TNT

This is my qry in progress:

SELECT tblCostSample.[Cost_no] AS [Cost_No Field], tblCostSample.ST, tblCostSample.TASK
FROM tblCostSample
WHERE (((tblCostSample.[Cost_no])>"3") AND ((tblCostSample.ST)>"1") AND ((tblCostSample.TASK)="TNT") AND ((tblCostSample.TASK)>"1"));

Can anyone assist in what I'm doing wrong pleas

[lfpm062010]

It could be done in a single query. But it will be easier to break it up and make it easier explaination.

1) Create a query with an extra field that contain the first 4 characters of the "Cost_no".
Query qry_costsample:
SELECT tblCostSample.Cost_no, Left(tblCostSample.Cost_no, 4) AS Cost_Cd, tblCostSample.ST, tblCostSample.TASK
FROM tblCostSample;

2) Create a query to find out the count for each unique [Cost_Cd], [ST], and [TASK].
Query qry_summsample
SELECT qry_costsample.Cost_Cd, qry_costsample.ST, qry_costsample.TASK, Count(qry_costsample.Cost_Cd) AS CostCount
FROM qry_costsample
GROUP BY qry_costsample.Cost_Cd, qry_costsample.ST, qry_costsample.TASK
HAVING (((Count(qry_costsample.Cost_Cd))>1));

3) Finally, create a query to show the final result.
Query qry_FinalResult
SELECT qry_costsample.Cost_no, qry_costsample.ST, qry_costsample.TASK
FROM [Query qry_summsample] INNER JOIN qry_costsample ON ([Query qry_summsample].TASK = qry_costsample.TASK) AND ([Query qry_summsample].ST = qry_costsample.ST) AND ([Query qry_summsample].Cost_Cd = qry_costsample.Cost_Cd);

[June7]

Try:

Query1
SELECT Cost_no, Left(Cost_no,4) AS ShortCostNo, ST, TASK FROM tablename;

Query2
SELECT Left(Cost_no,4) AS ShortCostNo, ST, TASK, Count(Task) AS CountTask FROM tablename GROUP BY Left(Cost_no,4), ST, TASK HAVING Count(Task) > 1;

Query3
SELECT Query1.* FROM Query1 INNER JOIN Query2 ON Query1.ShortCostNo=Query2.ShortCostNo AND Query1.ST=Query2.ST AND Query1.Task=Query2.Task;

[slimjen]

thanks all. this got me started!