Selecting records that having count >1

[Dryder]

Hi,

I'm trying to create a query that returns records of assets that have come
in for repair more then once having the same part number and serial number,
this is what i have;
In (Select [Serial Number] from [Program Table] AS Tmp Group By [Serial
Number],[Part Number] Having Count(*)>1 and [Serial Number] =
[reports]![grptrouter]![Serial Number])

It works except it also give me results of the asset that has only come back
once, it looks like its not picking up the "Having Count(*)>1" statement.
Any ideas? Thanks.

 

[Jerry Whittle]

Try this:

Count([Serial Number])>1

You could also try removing the "and [Serial Number] =
[reports]![grptrouter]![Serial Number]" for testing purposes. If you do use
it, the report must be open.

 

[Michael Gramelspacher]

Hi,

I'm trying to create a query that returns records of assets that have come
in for repair more then once having the same part number and serial number,
this is what i have;
In (Select [Serial Number] from [Program Table] AS Tmp Group By [Serial
Number],[Part Number] Having Count(*)>1 and [Serial Number] =
[reports]![grptrouter]![Serial Number])

It works except it also give me results of the asset that has only come back
once, it looks like its not picking up the "Having Count(*)>1" statement.
Any ideas? Thanks.

how about something like this:

SELECT repairs.serial_nbr,
repairs.part_nbr,
repairs.repair_date
FROM repairs
INNER JOIN repairs AS a
ON (a.repair_date <> repairs.repair_date)
AND (a.part_nbr = repairs.part_nbr)
AND (a.serial_nbr = repairs.serial_nbr);

 

[Dryder]

Jerry, Thanks for the reply but it's giving me the same results

Try this:

Count([Serial Number])>1

You could also try removing the "and [Serial Number] =
[reports]![grptrouter]![Serial Number]" for testing purposes. If you do use
it, the report must be open.

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi,

I'm trying to create a query that returns records of assets that have come
in for repair more then once having the same part number and serial number,
this is what i have;
In (Select [Serial Number] from [Program Table] AS Tmp Group By [Serial
Number],[Part Number] Having Count(*)>1 and [Serial Number] =
[reports]![grptrouter]![Serial Number])

It works except it also give me results of the asset that has only come back
once, it looks like its not picking up the "Having Count(*)>1" statement.
Any ideas? Thanks.

 

[Michael Gramelspacher]

Jerry, Thanks for the reply but it's giving me the same results

Try this:

Count([Serial Number])>1

You could also try removing the "and [Serial Number] =
[reports]![grptrouter]![Serial Number]" for testing purposes. If you do use
it, the report must be open.

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi,

I'm trying to create a query that returns records of assets that have come
in for repair more then once having the same part number and serial number,
this is what i have;
In (Select [Serial Number] from [Program Table] AS Tmp Group By [Serial
Number],[Part Number] Having Count(*)>1 and [Serial Number] =
[reports]![grptrouter]![Serial Number])

It works except it also give me results of the asset that has only come back
once, it looks like its not picking up the "Having Count(*)>1" statement.
Any ideas? Thanks.

or:

SELECT repairs.serial_nbr,
repairs.part_nbr
FROM repairs
WHERE repairs.serial_nbr = [Enter serial number:]
AND repairs.part_nbr = [Enter part number:]
GROUP BY repairs.serial_nbr,repairs.part_nbr
HAVING (SELECT COUNT(* )
FROM repairs AS a
WHERE a.serial_nbr = repairs.serial_nbr
AND a.part_nbr = repairs.part_nbr) > 1;