Top 50% Grouped by Month

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,
0
Utf
12/28/2009 5:03:01 PM
access 16762 articles. 3 followers. Follow

5 Replies
1009 Views

Similar Articles

[PageSpeed] 20

You'll need a unique key column in the table to do this.  If you don't
already have one add an autonumber column.  Then use a subquery to identify
the top 50 percent per month:

SELECT *
FROM YourTable AS T1
WHERE T1.YourKey IN
    (SELECT TOP 50 PERCENT YourKey
      FROM YourTable AS T2
      WHERE T2.Month = T1.Month
      ORDER BY MedTime DESC);

BTW using Month as a column name could cause confusion.  It’s the name of a
built in function in Access, so is best avoided.

Ken Sheridan
Stafford, England

Chuck W wrote:
>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,

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/200912/1

0
KenSheridan
12/28/2009 7:09:42 PM
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.


"Chuck W" wrote:

> 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,
0
Utf
12/28/2009 7:27:01 PM
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,

"KARL DEWEY" wrote:

> 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.
> 
> 
> "Chuck W" wrote:
> 
> > 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,
0
Utf
12/28/2009 7:53:01 PM
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.


"Chuck W" wrote:

> 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,
> 
> "KARL DEWEY" wrote:
> 
> > 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.
> > 
> > 
> > "Chuck W" wrote:
> > 
> > > 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,
0
Utf
12/28/2009 8:20:02 PM
I don't think that's going to work.  A subquery can only be included in a
SELECT clause if it returns a single value.  The subquery needs to be in the
outer query's WHERE clause and correlated on the Month column so that it
restricts the outer query to the top 50% of rows per Month value.  In this
case it should be possible to do this by returning the MedTime column in the
subquery if there is no suitable key column on which the IN operator can do
its stuff:

SELECT *
FROM qryOutpatient_AMI1a AS Q1
WHERE MedTime IN
    (SELECT TOP 50 PERCENT MedTime 
      FROM qryOutpatient_AMI1a AS Q2
      WHERE Q2.Month = Q1.Month
       ORDER BY MedTime DESC)
ORDER BY Q1.Month;

Ken Sheridan
Stafford, England

KARL DEWEY wrote:
>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];
>
>> Karl,
>> Thanks for your help. My query that this is based on is called 
>[quoted text clipped - 33 lines]
>> > > 
>> > > Thanks,

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/200912/1

0
KenSheridan
12/28/2009 9:19:18 PM
Reply:

Similar Artilces:

Control Access to groups of contacts
I have a client who wants to have a master contact list so he can see the entire list. He has a handful of clients who will need to be able to view overlapping subsets of those contacts, but should not be able to view the entire list. Can access control be applied at this granularity? User Attributes or other Security groups are acceptible components of this access control. For instance, UserA needs to see contacts 1, 3 and 5 UserB needs to see contacts 1, 2, and 3 UserC needs to see contacts 2, 4, and 5 UserD needs to see contacts 1, 3, 4, and 5 ...

How to copy block of cells and keep grouping?
I have about a 150 by 30 block of cells. About every 5 cells are grouped together, making it significantly smaller. when you click the "-" to have them contract. I will take me days to go through and make all the groups again if I want to make the same block on the same worksheet. Is there a way to copy a block of cells and include the grouping? Thanks -- dstock ------------------------------------------------------------------------ dstock's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24225 View this thread: http://www.excelforum.com/showthre...

Is it OK to assign permissions to a group during ForestPrep of an E2K3 upgrade?
Hello, We're about to start an upgrade from E2K to E2K3. During the E2K3 ForestPrep, we want to select a newly-created group to assign Exchange full admin permissions to instead of an individual user; however, during our previous E2K ForestPrep a few years ago, we only selected an individual user. Could there be any conflicts in now giving Exchange admin permissions to a new group instead of the same individual user as before? Sorry if it appears to be a silly question but I'm paranoid. Thanks, - Alan. On 27 Oct 2006 05:35:52 -0700, "Alan" <bruguy@gmail.com> wro...

Cannot access Outlook on my desk top from my laptop
I have a Roadrunner address with Outlook as my primary, on my desktop. But I cannot get to my Outlook using a laptop. Note: I can get to Hotmail, my secondary, using the laptop or another computer. Obivously, the primary is important. Can you detail the steps you're taking? "DCW" <DCW@discussions.microsoft.com> wrote in message news:EF21BBB7-2104-4C27-813F-8073234259F7@microsoft.com... > I have a Roadrunner address with Outlook as my primary, on my desktop. But I > cannot get to my Outlook using a laptop. Note: I can get to Hotmail, my > secondar...

Change receiving email for microsoft.public.access.gettingstarted group
I am retiring 2/26/10 and I need to change the receiving email for this and other groups to my home email address. I went to two places that I thought would take care of it but the one place wouldn't let me put in another email address. I believe I could only change the sign- on name. THe other place said I couldn't add a gmail address because the group couldn't go to a gmail address. Please help!! Thanks ...

Formula For Monthly Date
Hello, I have a worksheet that has products listed that are on sale at different times of the year. My question is what formula to use to get all Jan products,codes,prices etc on to new worksheet, Feb on another worksheet etc. Thanks Mare Use Autofilter. See: http://www.contextures.com/xlautofilter01.html -- Gary's Student "Mare" wrote: > Hello, > I have a worksheet that has products listed that are on sale at different > times of the year. My question is what formula to use to get all Jan > products,codes,prices etc on to new worksheet, Feb on another works...

Date range in months from month and year fields
In our database we track consultants work dates in four seperate fields, starting month, starting year, ending month, ending year. Is there anyway to calculate the range of months they have worked with this set up? So if someone started March 2004 and ended July 2007 is there a formula to calculate the 40 months they worked? DateDiff("m",[starting month] & "/" & "01" & "/" & [starting year],[ending month] & "/" & "01" & "/" & [ending year]) "monkeycr84" wrote: > In our da...

Fixed Assets Import (6 months ago)
Hello: It really is a moot point. The situation is over, my clients are happy, and I am able to import fixed assets just fine. But, six months ago and right before service pack 1 for GP 9.0 was released, for the first time ever I was unable to import fixed assets. The FA Import Tool would show--on the "surface"--that all of my assets imported. But, the fact of the matter was that the assets did NOT import. And, there were no error messages givens to why! I had opened up a case with Microsoft, at that time. They admitted that they could not solve the mystery. And, they s...

Suppliers with Top Items List
Can anyone let me know how to show suppliers on Top Item List? I badly need this. I tried my way but not successful. -- Many Thanks Install the RMS Toolkit trial download you requested and check off Custom Reports/Templates during installation. Now run Manager, Reports, Custom, Top Items Report. This report includes Suppliers, Dept, Category, and Sub-Descriptions as columns and filter options. There are over 100 free reports and templates included with our RMS Toolkit trial. Visit: www.digitalretailer.com/trial Greg Digital Retail Solutions www.digitalretailer.com/RMSaddins (v1.x and ...

cannot delete recovery storage group
I get the following error when I try to delete a database in the recovery storage group. One or more users currently use this mailbox store. These users must be moved to a different mailbox store or be mail disabled before deleting this store. ID no: c1034a7f Exchange System Manager Why would I get this message? All the mailboxes in this database are in the disconnected state and they are not the real mailboxes that the users are using. thanks for the help Kevin hmmm...that's weird...have you done a search in AD for any user who might have the store in the RSG listed as their ...

how do i semi-lock a group of cells to allow lookups formula...
how do i semi-lock a group of cells to allow lookups formulas without allowing user changes Do you mean you want to be able to reference these cells in lookup formulas contained in other cells? If so, leaving these cells locked (Format | Cells | Protection), and then Protecting the Sheet (from teh Tools Menu) should be enough. You may want to make sure you have Select Locked and Select Unlocked Cells Checked, if a list of Protection Options pops up (Excel 2002 and later). tj "quander" wrote: > how do i semi-lock a group of cells to allow lookups formulas without > allo...

2008 group MIA?
I'm seeing what looks like corruption in the SBS 2008 group, and a message I posted to it asking for confirmation has not appeared... (I.e. message headers, when opened, result in entirely unrelated messages appearing.) No other problems with other groups in the same client and mailstore. I just made a new test post to see if shows up.. In the mean time are you having an SBS problem or just wanting raise the alert of possible problems with sbs2008? Bill Sanderson wrote: > I'm seeing what looks like corruption in the SBS 2008 group, and a > message I posted ...

Recovery Storage Group: Invalid DN Syntax Error on names with comm
I have sucessfully created and mounted an RSG on my Exchange 2003 SP1 server. My problem is when i right click any mailbox in the RSG from the ESM that's name contains a comma (Kleinpeter, Rob) i get error: An Invalid dn syntax has been specified. Facility: LDAP Providor ID no: 80072032 Exchange System Manager This does not happen in the live database or in the RGS with names that do not contain commas. Any help with this would be greatly appreciated. RK "Rob Kleinpeter" <RobKleinpeter@discussions.microsoft.com> wrote in message news:<BD474C10-309D-423A-8736-1759...

Row highlight based on the 1-31 day of the month
I've got a table with dates displayed in the first column (ascending order). I would like to highlight each row based on the day of the month with one of 5 chosen colours (red, yellow, green, blue and orange). rows with the 1st of the month: red rows with the 2nd of the month: yellow ....... rows with the 6th of the month: red ....... Julian. You would use conditional formatting to display different colors based upon the day value. Your conditional formatting formula would look at the day value of the date. You will have multiple conditions for each row. Condition 1 Formula is: ...

WPF
Are there any news groups specific to WPF ? "Mr. Magic" <Mufasa@NoWhere.Com> wrote in message news:uiUfLnX3KHA.4028@TK2MSFTNGP05.phx.gbl... > Are there any news groups specific to WPF ? http://social.msdn.microsoft.com/forums/en/wpf/threads/ -- Peace and Grace, Greg Twitter: @gbworld Blog: http://gregorybeamer.spaces.live.com ************************************************ | Think outside the box! | ************************************************ On 16-04-2010 11:31, Mr. Magic wrote: > Are there any news groups spe...

Grouped index
I've been looking for a (very) long moment but cann't find out how to make a grouped index (index with more than 1 field) in Access 2003. -- Regards, Georges "Geo55" <geen@rommel.he> wrote in message news:853it3pbu31b8ppctiqg16n682k0sr5sql@4ax.com... > I've been looking for a (very) long moment but cann't find out how to > make a grouped index (index with more than 1 field) in Access 2003. > > -- > Regards, > Georges Hello Georges, Open the table in design view. Choose View | Indexes or click the Indexes button. Enter the Index Name in...

How to set top-margin of CEdit control?
CEdit::SetMargins( UINT nLeft, UINT nRight ); sets the left and right margins of this edit control. However, I need to set top-margin of CEdit control. Is it possible? try the function CEdit::SetRect. "Animesh Rawal" <animesh.rawalREMOVETHIS@patni.com> wrote in message news:A24E1FB8-C5CD-4F1F-97CC-2EFBECE5A0B6@microsoft.com... > try the function CEdit::SetRect. Note that this function will only work for multiple line edit controls (MSDN). Tom. ...

query based distribution group
Hello all running Exchange 2007 sp2, I need to modify an existing query based DL so it finds mailboxes and if the account has two different email address stamped on it. For example if the mailbox has test1.com and test2.com and it's a mailbox then make it a member of the query based DL Thanks for any help If it has those two e-mail addresses? That should return just one recipient. Or do you mean a recipient with any two proxy addresses? I don't believe that's possible with LDAP. Or do you mean a mailbox-enabled user with proxy addresses in two domai...

Error in Unit Groups Page
When I go to view the Unit Groups page I get this error in the Event Viewer. I was in the process of adding a new unit group and after finishing, I started getting an error anytime I went to view the unit groups. Nothing fancy in the setup, Windows Server 2003 Ent., SQL Server Dev., Clean systems with just CRM installed Event Type: Erro Event Source: Microsoft CR Event Category: Non Event ID: Date: 2/6/200 Time: 10:11:20 A User: N/ Computer: SERVER Description MSCRM Application Error Report ------------------------------------------------------------------------------------------------...

Grouping Nubmers
Hello - I am attempting to group numbers into a certain range i.e. 0-90 days, 91-180 days and 180+ days, however the grouping option only allows a certain interval of days. Is there a way to customize my days so I can do the way I would like to? Thanks for the help ...

New Administrative Group or new mail/pf store?
We have a domain with a single AG that contains a mailbox store and a public folder store that uses addresses like company.com. Now my boss wants me to put a public folder for company.ca for our Canadian customers. We do have a web presence for our Canadian customers, but no employees in Canada. What I'm trying to decide now is if I need to setup an entire new AG, or just a new mail/pf store in the existing AG. Is the latter possible? Thanks to all that take the time to respond. sax I think you're trying to setup a different PF tree for your Canadian customers. You can accompli...

Simple way to count by month?
Access2000 Hi I have a table that is just a list of serial numbers and dates that they were sold. Is there a simple way to build a query that will just list the total serials numbers by month, i.e. Dec-07 - 566 Nov-07- 453 Oct-07- 343 Each serial is on its own row. Getting the number sold by day is simple, but grouping and suming these to months has me stumped. Thanks - Steve Solved it. I used this if anybody else is looking SELECT Format([SQL_DELIVERY_DATE],"yyyy mm") AS MonthSold, Count(*) AS Total FROM tbl_sold GROUP BY Format([SQL_DELIVERY_DATE],"yyyy mm") ...

"exchange domain servers" group query
Currently I have a forest with one parent domain and 4 child domains. We have recently added a second DC to all the child domains. Their ability to do exchange stuff is broken, they just come up with RPC errors. I think I fixed that by installing the System Manager on them. Looking at the Exchange Domain Servers group on the parent domain, it has the Exchange server as a member, looking at the same groups on the child domains, it just has the one original domain controller that each child domain had. Now that we have a second DC in each child domain, should they be in that Exchange Dom...

Change Default Auto-Fill-In month
If I format a cell as a date mm/dd/yy; If I only type in the month and date, "4/15" ,excel will automatically fill in the the current year, so the cell will end up displaying 4/15/10. But if I only type in the date, "15", then excel will fill in the January 2000 as the default month and year, so the cell will end up displaying 1/15/00. Is there a way to tell excel to auto fill in the current month and current year when I do this? (example: If I type in the date, "15", then I want excel to fill in today's month and year, so the cell will displ...

Kamasutra Full B-Grade Movie With Nudity Must See (1 Hour 50 Min )
Kamasutra Full B-Grade Movie With Nudity Must See (1 Hour 50 Min ) At http://webcamgirlsuk.Co.CC Due to GOOGLE security risks, i have hidden the videos(15) in an image. in that website on Right side below search box click on image and watch videos in all angles. ...