Full Version: Computing monthly values from YTD values
UtterAccess Forums > Microsoft® Access > Access Date + Time
I have a table that contains data on admissions to hospitals over time. Each record represents a specific month and there is a column that shows the year-to-date admissions. I want to compute the monthly admissions from the year-to-date totals.
etting the number for January is easy. After that, it requires comparing the current record's YTD admissions to the YTD admissions of the previous record. I know how to do this in SPSS, SAS or Excel, but how do you do that in Access?
Hi, and welcome to utter access!!
This is one of those cases where the answer depends on your data structure and the use you intend to put the data through. If you just need the number to print on a report, it's fairly easy. If you need it to show up in a query, it's doable, but a little more complex.
What fields/datatypes are in your table, and how do you want to use the results?
I have a field with an ID number (text), a reference date field for the month of the data (date), and the YTD admissions (number). I was hoping to find a function that would make it work (after sorting the data appropriately first) without needing to JOIN the table on itself in some strange way. I guess I've been spoiled by SPSS and SAS which have a lag function that would make this a piece of cake in those languages.
The eventual use of the results is to generate a web report that shows the trend in monthly admissions by hospital over the period January 2004 to present. This time frame will most likely continue into 2006.
Okay, create a new query in design view. From the "Sqho table" dialog that opens by default, select your table and click the add button twice. This will add two copies of the table to the record source. One of these copies will be given and alias like "YourTable_1" (I will refer to these two copies as YourTable and YourTable_1 from here out.
ow, the challenging part is to get the dates "lined up correctly." Add the date field from YourTable_1 to the query. Uncheck the "Show" box because you don't need to see it. In the criteria row, type =DateAdd(-1, "m", YourTable.DateField) change DateField to the appropriate name. This should make is so that for each record in the first copy, you get the previous month's record in the second copy.
Add the date field from YourTable, the YTD from YourTable. Now, in a new column, this expression should get you the monthly values:
Monthly: IIf(Month(YourTable.DateField) = 1, YourTable.YTD, YourTable.YTD - YourTable_1.YTD)
that should do it. If you need any more help, don't hesitate to ask.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.