Exclude most recent month

Hello,
I have a query called qryReadmits based on a table called tblReadmits that 
has fields such as ReadmitMonth, AccountNumber, Readmit (1 or 0), Population 
(always 1).  Data is populated into tblReadmits on a periodic basis.  
Sometimes the data is fairly current (2 months behind) and other times it is 
up to four months behind.  I want to create a query where I group by month 
the readmission rate which is Readmit divided by Population.  But I want to 
always exclude the most receent month that is available.  I want to automate 
this since I will be reporting it each month.  Can someone give me ideas on 
how to always exclude the most recent month regardless of how old the data is?

Thanks,
0
Utf
6/4/2010 3:33:06 PM
access 16762 articles. 2 followers. Follow

3 Replies
1409 Views

Similar Articles

[PageSpeed] 44

What's stored in ReadmitMonth: a number (1 through 12), text (Jan through 
Dec), or something containing month and day (2010/01 through 2010/12)?


-- 
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
Co-author: Access 2010 Solutions, published by Wiley
(no e-mails, please!)

"Chuck W" <ChuckW@discussions.microsoft.com> wrote in message 
news:EAF168FB-3CBF-4272-81BE-50802377F2BE@microsoft.com...
> Hello,
> I have a query called qryReadmits based on a table called tblReadmits that
> has fields such as ReadmitMonth, AccountNumber, Readmit (1 or 0), 
> Population
> (always 1).  Data is populated into tblReadmits on a periodic basis.
> Sometimes the data is fairly current (2 months behind) and other times it 
> is
> up to four months behind.  I want to create a query where I group by month
> the readmission rate which is Readmit divided by Population.  But I want 
> to
> always exclude the most receent month that is available.  I want to 
> automate
> this since I will be reporting it each month.  Can someone give me ideas 
> on
> how to always exclude the most recent month regardless of how old the data 
> is?
>
> Thanks, 


0
Douglas
6/4/2010 4:12:52 PM
Douglas,
The values in ReadmitMonth are date values - all are the first day of a 
month (i.e. 2/1/10, 3/1/10).  
Chuck

"Douglas J. Steele" wrote:

> What's stored in ReadmitMonth: a number (1 through 12), text (Jan through 
> Dec), or something containing month and day (2010/01 through 2010/12)?
> 
> 
> -- 
> Doug Steele, Microsoft Access MVP
> http://www.AccessMVP.com/DJSteele
> Co-author: Access 2010 Solutions, published by Wiley
> (no e-mails, please!)
> 
> "Chuck W" <ChuckW@discussions.microsoft.com> wrote in message 
> news:EAF168FB-3CBF-4272-81BE-50802377F2BE@microsoft.com...
> > Hello,
> > I have a query called qryReadmits based on a table called tblReadmits that
> > has fields such as ReadmitMonth, AccountNumber, Readmit (1 or 0), 
> > Population
> > (always 1).  Data is populated into tblReadmits on a periodic basis.
> > Sometimes the data is fairly current (2 months behind) and other times it 
> > is
> > up to four months behind.  I want to create a query where I group by month
> > the readmission rate which is Readmit divided by Population.  But I want 
> > to
> > always exclude the most receent month that is available.  I want to 
> > automate
> > this since I will be reporting it each month.  Can someone give me ideas 
> > on
> > how to always exclude the most recent month regardless of how old the data 
> > is?
> >
> > Thanks, 
> 
> 
> .
> 
0
Utf
6/4/2010 5:03:09 PM
WHERE ReadmitMonth < DateSerial(Year(Date), Month(Date), 1)

-- 
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
Co-author: Access 2010 Solutions, published by Wiley
(no e-mails, please!)

"Chuck W" <ChuckW@discussions.microsoft.com> wrote in message 
news:B9C37EE6-4DB5-44EF-B8DD-ED7DA5773FCD@microsoft.com...
> Douglas,
> The values in ReadmitMonth are date values - all are the first day of a
> month (i.e. 2/1/10, 3/1/10).
> Chuck
>
> "Douglas J. Steele" wrote:
>
>> What's stored in ReadmitMonth: a number (1 through 12), text (Jan through
>> Dec), or something containing month and day (2010/01 through 2010/12)?
>>
>>
>> -- 
>> Doug Steele, Microsoft Access MVP
>> http://www.AccessMVP.com/DJSteele
>> Co-author: Access 2010 Solutions, published by Wiley
>> (no e-mails, please!)
>>
>> "Chuck W" <ChuckW@discussions.microsoft.com> wrote in message
>> news:EAF168FB-3CBF-4272-81BE-50802377F2BE@microsoft.com...
>> > Hello,
>> > I have a query called qryReadmits based on a table called tblReadmits 
>> > that
>> > has fields such as ReadmitMonth, AccountNumber, Readmit (1 or 0),
>> > Population
>> > (always 1).  Data is populated into tblReadmits on a periodic basis.
>> > Sometimes the data is fairly current (2 months behind) and other times 
>> > it
>> > is
>> > up to four months behind.  I want to create a query where I group by 
>> > month
>> > the readmission rate which is Readmit divided by Population.  But I 
>> > want
>> > to
>> > always exclude the most receent month that is available.  I want to
>> > automate
>> > this since I will be reporting it each month.  Can someone give me 
>> > ideas
>> > on
>> > how to always exclude the most recent month regardless of how old the 
>> > data
>> > is?
>> >
>> > Thanks,
>>
>>
>> .
>> 


0
Douglas
6/4/2010 6:24:36 PM
Reply:

Similar Artilces:

Datedif exclude weekends
Hello newsgroup gurus and users. Your kind assistance please. This question has probably been asked a millions times but I cant find it. Does any one have a solution to this. I want the date difference between two dates but exclude weekends. Many thanks Paul Paul, The NETWORKDAYS function will return the number of days, excluding weekend and optionally holidays, between two dates. See help for NETWORKDAYS for more information. This function is part of the Analysis Tool Pack add-in so you must have that add-in loaded; otherwise, you'll get a #NAME error. -- Cordially, Chip Pe...

Not adding files to Most Recent list?
For some reason, Excel 2000 stopped adding files to the Most Recently Used list which appears under the file menu? How do I get this back? Ed There's a KB article about xl2002 that's kind of close: OFFXP: "Most Recently Used" List Missing from File Menu and "Recently Used File List" Option Unavailable (Q284896) http://support.microsoft.com/default.aspx?scid=kb;EN-US;q284896 Any chance you just turned on TweakUI? Ed wrote: > > For some reason, Excel 2000 stopped adding files to the Most Recently Used > list which appears under the file menu? How d...

1.2 million hits a month
Hi My name is Victor Lockyer I'm with a team of Stephan Ducharme,The Freeadguru He is a best-selling author and Internet self-made Multi-Millionaire- Getting up to 1.2 miillion hits a month. I can help you get a boost of visitors and sales THIS WEEK PLease respond QUICKLY Victor Lockyer --- MAF Anti-Spam ID: 20070304070211J0f2JxZ1 ----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==---- http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups ----= East and West-Coast Server Farms - Total Privacy via Encryption =---- ...

gci and -include and -exclude
I am trying get a limited set of files from a directory tree. The following is what i want but it doesn't work. I get everything. get-childitem -LiteralPath \rm5_0_code_h\source -include *.frm,*.ctl,*.bas,*.cls,*.cs,*.cpp,*.hpp,*.sql,*.java -Recurse I have even tried to exclude what i don't want but it doesn't seem to matter. get-childitem -LiteralPath $path -include *.frm,*.ctl,*.bas,*.cls,*.cs,*.cpp,*.hpp,*.sql,*.java -recurse -exclude *alter*.sql,*.dll,*.pdb,*.ncb,*.tlb,*.scc,*.bmp,*.ico i still get them all. what am i doing wrong? dan you have to a...

Cell formulas that expire after a month
I have a spreadsheet that I'm using to track progress over the period of a year. One sheet is raw data that is manually entered daily and one sheet is monthly statistics based on that data. In the statistics worksheet are cell formulas calculating statistics relating to each month based on the raw data. If I change the data in March, I don't want January's or February's statistics to be changed. Is there a way to do this? Thanks, PaulH On Mar 7, 11:52=A0am, PaulH <paul.h...@gmail.com> wrote: > I have a spreadsheet that I'm using to track progress over the perio...

Daily totals from a monthly log
I am working on a stat log that needs to provide daily, weekly and monthly totals for each staff member. I need excel to sum the dollar amouts in P6:P1000 (for each worksheet) if the date in A6:A1000 is a particular date (ex: 4/1/10). I have tried several different functions/formulas and I have not received the response I am looking for. Thanks in advance for your help! Desireé Assuming you have sheet name as: Sheet1, Sheet2, and Sheet3 =SUMPRODUCT(SUMIF(INDIRECT("Sheet"&ROW(1:3)&"!A6:A1000"),"4/1/10",INDIRECT("Sheet"...

Can't see categories in Budget Planner/Won't load Monthly Budget report
In the last couple of days, Money stopped showing the categories under the budget details. In the "Review your current budget status" screen, under budget details, it usually shows each income and expense category and how I'm doing so far in the month. Now none of that shows. I have made sure to check View all budget items and show expense and income in the view menu, but this does not help. In addition, Money will no longer load the monthly budget report when I click "Other Tasks, view reports, monthly budget." I have this problem no matter what budget I...

Exclude a cell
Excel 2002 Any one know how I can sum all the cell values in a column except the value of the last cell to be entered which could be say half-way down the column? Is there a function to exclude a particular cell from "sum" ? I know a lot of ppl are far more advanced than I am but we all start somewhere lol Thanks for your help Dale To exclude cell A16: =SUM(A:A)-A16 Not very elegant, but effcient! Cheers, -- AP "DF2507" <nospamthx@blueyonder.co.uk> a �crit dans le message de news: xB6sg.51172$181.12831@fe3.news.blueyonder.co.uk... > Excel 2002 > &...

update sheet name in conditional sum every month
I have files with monthly data. This data has different categorie (columns). I am summarizing this data in a sheet using conditional sum Now when new data comes in every month I have to go into each cell wit these formulae to update the formula with new sheet name with new data Is there any easier/faster/better way to do this? Here is an example of formula in one cell (obviously when I copy th formula excel gets rid of { brackets but those are there in th formula)......and I have formulae like this in about 40 cells that need to update every month manually. =SUM(IF(Sheet1_Dec!$J$2:$J$2859=...

Monthly Report
Dear Freinds I am new to this world but i hope that i could get some help I Have data listed in sheet called Data as follows: DateSales ItemSold Dtl1 Dtl2 Dtl3 T-Price Quantity 01.01.2010 Item1 60 6 01.01.2010 Item2 80 4 01.01.2010 Item3 90 2 02.01.2010 Item4 100 50 02.01.2010 Item5 150 6 09.01.2010 Item6 70 2 09.01.2010 Item7 ...

Excluding zeros
I have stock data in excel which I'd like to chart using the high low close option. Problem is that the data contains zeros on some days. Can I get excel to ignore the zeros instead of plotting them. If not what's the best way of converting these zeros to blank cells so that Excel does not chart them. Thank you Hi Ben, Two ways: 1) replace zeros (or missing data) by =NA() 2) Tools/Options/Chart and specify what is to be done with missing values. Bernard "Ben" <anonymous@discussions.microsoft.com> wrote in message news:075901c3bff5$94a57530$a001280a@phx.gbl... &...

Script giving the most recent receipt info for each item
We are trying to create a script that will pull together our most recent purchasing information only. The results should give all inventory items with a qty on hand > 0, the current cost (we are average perpetual), the most recent receipt number, the unit cost for that receipted item, the receipt date, and the vendor name. I have tried to put this together, but have been unsuccessful. Thank you. Shaun This script will only work on SQL Server 2005 and above: SELECT A.ITEMNMBR , B.ITEMDESC , B.CURRCOST , ISNULL(R.receiptdate, '01/01/1900') AS LastRecei...

excluding repeating values
I have a column wuth date and some entries are repeating. I want to copy that data to another column, but do not want to copy duplicate values ( I want to exclude repeating values when copying and pasting). Is there an option to do that? Thanks. Neda Take a look at http://contextures.com/xladvfilter01.html#FilterUR In article <CE251167-21F0-470D-9641-508FD96D54FD@microsoft.com>, "neda5" <neda5@discussions.microsoft.com> wrote: > I have a column wuth date and some entries are repeating. I want to copy that > data to another column, but do not want to c...

Monthly budget report not showing investment income account
For some strange reason, my monthly budget report is not picking up the reinvested interest from a taxable money money fund I have. It picks it up from 2 other similar accounts, but not this one. I've looked at the account details over and over and it is the same as the other 2 accounts - any idea why this might be happening or where else I might check to make sure this is included? Thanks, Mark Have you perhaps designated the account a "retirement account"? "ATHiker95" <mholmes@gmail.com> wrote in message news:9cbbcf83-96e7-41c7-ad78-3c456498380b@i29g2000pr...

Convert Month, Week, Year to date
What is the easiest way to convert three values - Month, Week, Year to a date? Date would be the Monday of the week specified. Pete You'll need to specify how you define the week number within a month? Is week 1 always 1st to 7th? Presumably the Monday of week 5 can actually fall in the next month? Perhaps: =DATE(C2,A2,B2*7-6)+MOD(7-WEEKDAY(DATE(C2,A2,B2*7-6),3),7) -- David Biddulph Pete Hay wrote: > What is the easiest way to convert three values - Month, Week, Year > to a date? Date would be the Monday of the week specified. > Pete I'm trying to conv...

show month number as month name in Excel?
I'm trying to format a field in an Excel Spreadsheet in such a way that it displays a month name rather than a month number. For Example, if a cell contains 8, I want the cell to show as August. Conditional formatting only allows me 3 levels of condtions and isn't therefore suitable. I don't think you're gonna be able to use formatting for this. But you could use a helper cell: =date(2005,a1,1) if A1 contained the month number. Format this cell as MMMM and you can keep the value as a real date. or =text(date(2005,a1,1),"MMMM") If you want the text string fo...

Excluding Rows During Printout
Hi, Is there a way to automatically exclude rows when printing an Excel sheet? I have a list of items (in two colums) in a work sheet similar to this: Task Time ----------- ------- Task A: 1 Task B: 0 Task C: 2 Task D: 0 Task E: 0 Task F: 0 Task G: 4 Task H: 1 When I print the worksheet I only want to print lines where the time cell is not zero. So the printout from the previous data would look like this: Task Time ----------- ------- Task A: 1 Task C: 2 Task G: 4 Task H: 1 Is there a...

Outlook Rules for excluding & including email groups
Does any 1 know how to only send an out of office message to a specific group, for example any one with email ending with @iam.edu, I tried to creat a rule to inlcude certain business groups and also need to exclude certain server list too ...

Guaranteed 1,000,000 visitors every month and one website. Absolutely Free
Yes! Guaranteed 1,000,000 Visitors and one website, it's absolutely Free! This system is FREE http://www.clickaudit.com/goto/?51447 Quick and easy sign-up... get your own viral webpage in under 5 minutes! This "Viral Marketing" system works for you around-the-clock to bring you traffic! The viral nature of this system ensures that your traffic will exponentially increase! In less than 5 Minutes you can have your own Free Viral Marketing mini site.... and then watch your traffic EXPLODE !! Visit the Link below, http://www.clickaudit.com/goto/?51447 ...

XslTransform not excluding default namespace despite exclude-result-prefixes attribute?
I'm writing an xslt in vs.net 2003 and in order to get intellisense on the html content I added the default namespace declaration xmlns="http://schemas.microsoft.com/intellisense/ie5". However, even though I also have exclude-result-prefixes="#default" declared the default namespace is still outputted on the resulting document. <?xml version="1.0" encoding="UTF-8" ?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns="http://schemas.microsoft.com/intellisense/ie5" xmlns:...

How do I do a parameter query on =month([Form])
I need to pull the month out of a birth date (01-Jan-85) and change it to read either Jan or January. Once this is done I than need to do a parameter query to show only those birthdays listed in January. When I get this complete I get an error message or no data at all. How can this be fixed. ...

Clearing recently used list?
Hi, I created a test money file just so I can play around with it. I deleted the file now but it still shows up in the recently used list under the File menu. How do I go about removing it? TIA. In microsoft.public.money, KcN wrote: > >I created a test money file just so I can play around with it. I deleted >the file now but it still shows up in the recently used list under the File >menu. How do I go about removing it? You would have to search it out in the registry. What am I looking for? I searched for "test" since that's what I named my file... also ...

Budget
I want to exclude all bills I enter from automatically being added to my Budget categories. It's very frustrating for Money 2006 (& earlier) to add them to my budget when I have my budget setup seperately from my bills and I want to kepp it that way. Is there anyway to do this, other than leaving the categories blank in the bills? Rick I don't think so. The integration between bills and budget is one of Money's features. I guess if you truly want a standalone budget, you could create a new file. -- "rs" <rsumral_NoSpam_today_l@gmail.com> wrote in...

Month end report ? expansion...
Posted this yesterday, got a few responses that didn't really help, fro what I could tell. I have attached a very basic sheet showing what I a looking for...... I am looking to pull data from the 'data' tab based on the mont selected. In 'month report' cell f4, I want to put a pull down box with all 1 months. When a certain month is selected, lets say May, pre programme forumlas in the 'month report' sheet pull from the correct cells on th 'data sheet'. Ie.... May 'Month Report' cell pulls from 'Data' cell c7 b9 c15 c25 d23 k41 Any help...

Standard deviation to exclude 0
In our data, 0 means not applicable. I'm using STDEVP but calculate value 0 too. Can I exclude values with 0? Maybe I should do search and replace! Thanks, Enter this =STEVE(IF(E1:E7<>0,E1:E7,"")) as an array function. That means use CTRL+SHIFT+ENTER when you have done typing; Excel will wrap the formula in curly braces { ... } You need to use the same committal it you edit the formula best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "cpliu" <spamfreeliu@yahoo.com> wrote in message news:b8bc0f29-c141-4f62-b1c5-c48d8cb...