Top 50% Grouped by Month

[Chuck W]

Hi,
I have two fields in a query which are Month and MedTime. I want to get the
top 50% of the values or top half by month. The query returns about 200
records with about 15-20 records per month. So if October 09 has 20 records,
I want to return the highest ten MedTime values. If November has 15 records,
I want to return the top 7-8 records. So I would want the top half values
for MedTime grouped by month. Can someone help?

Thanks,

 

[KARL DEWEY]

Try this --
SELECT [YourTable].[Month], (SELECT TOP 50% [XX].[MedTime] FROM YourTable AS
[XX] WHERE [XX].[Month] = YourTable.Month [XX].[MedTime] DESC) AS High_MedTime
FROM YourTable
ORDER BY [YourTable].[Month]

 

[Chuck W]

Karl,
Thanks for your help. My query that this is based on is called
qryOutpatient_AMI1a. I am getting a syntax error. Here is my sql:

SELECT qryOutpatient_AMI1a.Month, (Select top 50% [XX].[MedTime]
FROM qryOutpatient_AMI1a as [XX]
Where [XX].Month = qryOutpatient_AMI1a.Month [xx].[MedTime] DESC) as
High_MedTime
from qryOutpatient_AMI1a
ORDER BY qryOutpatient_AMI1a.Month;

The syntax error is highlighting:

Select top 50% [XX].[MedTime]
FROM qryOutpatient_AMI1a as [XX]
Where [XX].Month = qryOutpatient_AMI1a.Month [xx].[MedTime] DESC)

Do you think the problem is qryOutpatient_AMI1a.Month [xx].[MedTime] ? I
tried a comma between .Month and [xx] but the syntax error remained.

Thanks,

Try this --
SELECT [YourTable].[Month], (SELECT TOP 50% [XX].[MedTime] FROM YourTable AS
[XX] WHERE [XX].[Month] = YourTable.Month [XX].[MedTime] DESC) AS High_MedTime
FROM YourTable
ORDER BY [YourTable].[Month]

--
Build a little, test a little.

Hi,
I have two fields in a query which are Month and MedTime. I want to get the
top 50% of the values or top half by month. The query returns about 200
records with about 15-20 records per month. So if October 09 has 20 records,
I want to return the highest ten MedTime values. If November has 15 records,
I want to return the top 7-8 records. So I would want the top half values
for MedTime grouped by month. Can someone help?

Thanks,

 

[KARL DEWEY]

I omitted ORDER BY ---
SELECT [YourTable].[Month], (SELECT TOP 50% [XX].[MedTime] FROM YourTable AS
[XX] WHERE [XX].[Month] = YourTable.Month ORDER BY [XX].[MedTime] DESC) AS
High_MedTime
FROM YourTable
ORDER BY [YourTable].[Month];

--
Build a little, test a little.

Karl,
Thanks for your help. My query that this is based on is called
qryOutpatient_AMI1a. I am getting a syntax error. Here is my sql:

SELECT qryOutpatient_AMI1a.Month, (Select top 50% [XX].[MedTime]
FROM qryOutpatient_AMI1a as [XX]
Where [XX].Month = qryOutpatient_AMI1a.Month [xx].[MedTime] DESC) as
High_MedTime
from qryOutpatient_AMI1a
ORDER BY qryOutpatient_AMI1a.Month;

The syntax error is highlighting:

Select top 50% [XX].[MedTime]
FROM qryOutpatient_AMI1a as [XX]
Where [XX].Month = qryOutpatient_AMI1a.Month [xx].[MedTime] DESC)

Do you think the problem is qryOutpatient_AMI1a.Month [xx].[MedTime] ? I
tried a comma between .Month and [xx] but the syntax error remained.

Thanks,

Try this --
SELECT [YourTable].[Month], (SELECT TOP 50% [XX].[MedTime] FROM YourTable AS
[XX] WHERE [XX].[Month] = YourTable.Month [XX].[MedTime] DESC) AS High_MedTime
FROM YourTable
ORDER BY [YourTable].[Month]

--
Build a little, test a little.

Hi,
I have two fields in a query which are Month and MedTime. I want to get the
top 50% of the values or top half by month. The query returns about 200
records with about 15-20 records per month. So if October 09 has 20 records,
I want to return the highest ten MedTime values. If November has 15 records,
I want to return the top 7-8 records. So I would want the top half values
for MedTime grouped by month. Can someone help?

Thanks,