Cumulative query – but there’s a gap in the data

[Big Pat]

Hi,

I have attached a stripped down version of my database which holds the dates that patients were enrolled in clinical studies. The requirement is to show the cumulative monthly total in a chart, also including a cumulative target line. Take a look at the report and you'll see I'm 99% of the way there, but with a gap in the results.

The cumulative query is one I have used before in other projects and I think I got it from this forum originally (can't be certain, it was years ago). It works fine most of the time, but a problem arises when there is a gap in the data.

For example, in the attached data, there were no enrollments in November. The query returns the right cumulative totals up to October, leaves November blank and then returns the correct total for December. Therefore, the chart also leaves a gap for November.

Even though the figures shown are correct, I need to get November to show that the cumulative total was the same as in October. The IIf statement in query 3 seems to be the place to change it, but if I’m honest I don’t really follow what it’s doing, so I can’t figure out how to change it.

By the way, I know the format of the "month" field isn't ideal, but I'm pretty much stuck with that as it's imported from elsewhere. I'm actually puzzled as to how Format([Month],"mmm") can convert 2013-01 (which is text) into "Jan", but I'm happy that part works!!


I’d be grateful for any help.

 

[namliam]

To ensure this works with all months/years possible...
First create a query: qryUniqueMonth

SELECT Format([Month],"mmm") AS M, THE_DATA.Abbrev, 0 AS DummyCount
FROM THE_DATA
GROUP BY Format([Month],"mmm"), THE_DATA.Abbrev;

Then a query for the years: qryUniqueYear

SELECT THE_DATA.Year
FROM THE_DATA
GROUP BY THE_DATA.Year;

Now make a new query: qryDummyMonths

SELECT qryUniqueMonth.M, qryUniqueMonth.DummyCount, qryUniqueMonth.Abbrev, qryUniqueYear.Year
FROM qryUniqueMonth, qryUniqueYear;

Now make a union query to dummy it up: qry 11 Totals Union

SELECT * FROM [1 Totals by Month Name]
UNION 
Select * from qryDummyMonths;

Now finaly sum this to make: 111Totals Summed

SELECT [11 Totals Union].MonthName, Sum([11 Totals Union].Recruits) AS Recruits, [11 Totals Union].Abbrev, [11 Totals Union].Year
FROM [11 Totals Union]
GROUP BY [11 Totals Union].MonthName, [11 Totals Union].Abbrev, [11 Totals Union].Year
HAVING ((([11 Totals Union].Year)="2013-14"));

EDIT: NOTE THAT I MOVED THE 2013-14 CRITERIA TO HERE, NOT IN YOUR 1 QUERY ANYMORE
use the 111 query as input to your 2 query instead of 1
2 by Month number

SELECT [111 Totals Summed].Abbrev
     , MonthNamesAndNumbers.MonthName
     , MonthNamesAndNumbers.MonthNumber, [111 Totals Summed].Recruits
FROM [111 Totals Summed] 
LEFT JOIN MonthNamesAndNumbers ON [111 Totals Summed].MonthName = MonthNamesAndNumbers.MonthName
ORDER BY MonthNamesAndNumbers.MonthNumber;

This should result in your desired result, I hope you can work out what goes on, if you have any questions post back

Meanwhile I must give myself the speech of "You shouldnt use spaces in your query/table names and columns" *slaps self on wrist*

 

[Big Pat]

Hi namliam,

Thanks so much for getting back to me so quickly. You have solved the problem of the gap in the data, but now there is another problem. I realise I didn't explain this requirement properly the first time!

Your solution means that the cumulative total is carried forwards until the end of the year, but I don't want to report that yet, because our data currently ends in December. So I don't want to say that the totals for Jan, Feb and Mar remain at 41 because I don't know that yet. In fact it is very likely that the total will increase so it would be misleading to report 41 for those months.

I need the cumulative total to stop at the most recent month for which is there is data, in this case December. Is that possible?

I've attached a screenshot showing both versions, with some notes.

Thank you, I really appreciate your help.

 

[namliam]

Simply stick a (very nice) where clause into your 2 query...
Which then should look like:

SELECT [111 Totals Summed].Abbrev
     , MonthNamesAndNumbers.MonthName
     , MonthNamesAndNumbers.MonthNumber
     , [111 Totals Summed].Recruits
FROM [111 Totals Summed] 
LEFT JOIN MonthNamesAndNumbers ON [111 Totals Summed].MonthName = MonthNamesAndNumbers.MonthName
WHERE (((CInt(Left([Year],4))*100+CInt([MonthNumber]))<[COLOR="Red"]=[/COLOR]Year(DateAdd("m",-3,Date()))*100+Month(DateAdd("m",-3,Date()))))
ORDER BY MonthNamesAndNumbers.MonthNumber;

Also, using an ORDER BY clause for queries that only serve to feed other queries is kind off pointless, this is obviously there to help you see the steps... but for the final product is quite useless.

If you do not want to include Januari as data, you simply remove the = from the <= in the where clause

 

[Big Pat]

Wow, that's very clever!!

I don't want to report January yet (not until approx mid-Feb) so I have changed <= to < and it works perfectly.

But can you explain what the -3 is for? Does it mean that there are three months where there is no data? So next month I would change it to -2? (assuming I had more data)

I will need to run this report for over 40 "Abbrevs", each of which could have up to 20 clinical specialties. This results in many reports, which are used as sub-reports in a main report. Therefore, I would like to attempt to use a formula to replace the -3.

I can work out that month 9 is the latest month I have data for and use that to generate -3, but I want to be sure I have not completely misunderstood what the -3 is for.

Thank you.

 

[namliam]

The -3 is to "move the date" back three month, meaning any date in January will "act" like a date in October, any date in August will act as a date in May, any date in april will act as any date in Januari. Effectively "Moving" April 1 to Januari 1, or effectively adjusting your Fiscal Year to a calander Year.

The -3 needs to remain -3, for now, for next month and for the next 10 years or the life time that you are going to use this.

The current setup should work for any abrev you want since the abrev is picked up from yoru THE_DATA Table.

Not to rain on your parade, but if you have "many" abrevs why run the reporting many times, instead just load all abrevs and run it only once?

When at 9/12 of your FY you are at 33% of the target, having that "Red" is very much an understatement, perhaps Blood red?
Also to have the targets at 13.3333 per month, I would have probably made it a simple target of 160 per year, which it in reality actually is... entering one record instead of 12...
But I guess it works while it works

 

[namliam]

If your company is looking to hire more people... I am looking for a new place to hang my hat

 

[Big Pat]

Hi - Sorry for taking so long to come back to you. Lots of other stuff intervened over the past week!

OK, so I DID completely misunderstand the -3. Asking that question was probably my smartest move so far I'll see how this report looks in a few weeks when it's time to include January data.

As to why I don't run all 40 Abbrevs in one go...it's because these Abbrevs are kind of like customers and they need their reports at different times and frequencies. I know what you mean about Blood red but it's rare that we have one this far behind target.

We're midway through a reorganisation/merger at the moment and there aren't any MORE jobs. I just hope there won't be FEWER! And you'd have to hang your hat in the UK midlands. You up for that???

 

[namliam]

I would, just my wife probably wouldnt be :/

Not sure where my job hunting comment came from though, I may have misposted it in the wrong thread :/

 

[Big Pat]

Ha ha....someone as busy as you are...that's gonna happen!