Sum of count over date range (1 Viewer)

[siperiea]

Hi all,

I've achieved this previously in Excel via VBA, however, I've been tasked with setting the same up in Access. Being 3 weeks new to Access and still learning I've hit my first major hurdle! Have been searching for about a week, as it must be possible to do this, I'm obviously not approaching it correctly.

Data below is just sample data, actual volumes involved are into 00's and 000's each month.

I have a table of dates with the first of each month recorded, along with total counts of members (MemberType1 and MemberType2), so I know how many members were present historically:

(MemberType is a sum of all Members within that type - we have multiple types.)

Datenum # MemberType ...
01/01/2008 # 2
01/02/2008 # 4
01/03/2008 # 4
...

This table updates / appends new data every month (if no date is present, it appends totals, if date is present, it updates).

I have a second table which is a record of when each Member has had an issue (IssueType is so I can exclude certain types later on):

Date # MemberID # IssueType
01/01/2008 # Member1 # Issue1
05/01/2008 # Member2 # Issue1
16/01/2008 # Member1 # Issue1
03/02/2008 # Member1 # Issue2
04/02/2008 # Member3 # Issue2
22/02/2008 # Member3 # Issue3
22/02/2008 # Member4 # Issue1
05/03/2008 # Member2 # Issue4
15/03/2008 # Member2 # Issue1
01/04/2008 # Member3 # Issue2
09/04/2008 # Member2 # Issue2
21/04/2008 # Member2 # Issue1
04/05/2008 # Member2 # Issue3
17/05/2008 # Member3 # Issue2
...

I have a query that counts the total number of issues each member has each month, returning results as follows:

DateNum # MemberID # Count of MemberID
01/01/2008 # Member1 # 2
01/01/2008 # Member2 # 1
01/02/2008 # Member1 # 1
01/02/2008 # Member3 # 2
01/02/2008 # Member4 # 1
01/03/2008 # Member2 # 1
01/04/2008 # Member3 # 1
01/04/2008 # Member2 # 2
01/05/2008 # Member2 # 1
01/05/2008 # Member3 # 1
...

################# STUCK HERE #################
vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv

What I need to do is get the total number of issues each member has had over the previous three months for that month:

DateNum # MemberID # Sum of Count of MemberID(previous 3 months)
01/01/2008 # Member1 # 0
01/01/2008 # Member2 # 0
01/02/2008 # Member1 # 2
01/02/2008 # Member3 # 0
01/02/2008 # Member4 # 0
01/03/2008 # Member2 # 1
01/04/2008 # Member3 # 2
01/04/2008 # Member2 # 2
01/05/2008 # Member2 # 3
01/05/2008 # Member3 # 2
...

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
################# STUCK HERE #################

The idea is to then count the number of 0's, 1's, 2's and 3+'s each month (I can do this bit), apply a weighting, 0 = 100%, 1 = 66%, 2 = 33%, 3+ = 0%, and calculate a weighted percentage - hence the requirement for the totals to be recorded each month in the first table.

To give a query:
DateNum # 100% # 66% # 33% # 0%
01/01/2008 # 2 # 0 # 0 # 0
01/02/2008 # 3 # 0 # 1 # 0
01/03/2008 # 3 # 1 # 0 # 0
01/04/2008 # 2 # 2 # 0 # 0

100% actually calculated by (MemberType)-(sum of all issues in that month).

To give a final percentage for each month:
DateNum # % # Calculation
01/01/2008 # 100% # (2/2)
01/02/2008 # 83.25% # (3.33/4)
01/03/2008 # 91.5% # (3.66/4)
01/04/2008 # 83% # (3.32/4)

I've tried cross tab queries, but I can't sum the previous three months, as this impacts the next months calculation. Totally self taught, first post on any forum. All advice greatly appreciated.

I'm stuck on the bit highlighted above between vvvv and ^^^^

Apologies for length of post, I wasn't sure how much info to provide.

Cheers

 

[duluter]

What I need to do is get the total number of issues each member has had over the previous three months for that month:

I'm not sure what you mean by this. Could you clarify?


Also, I'm not sure I understand your example:

DateNum # MemberID # Sum of Count of MemberID(previous 3 months)
01/01/2008 # Member1 # 0
01/01/2008 # Member2 # 0
01/02/2008 # Member1 # 2
01/02/2008 # Member3 # 0
01/02/2008 # Member4 # 0
01/03/2008 # Member2 # 1
01/04/2008 # Member3 # 2
01/04/2008 # Member2 # 2
01/05/2008 # Member2 # 3
01/05/2008 # Member3 # 2


If this is a sum of the previous three months, then for 1/3/2008, wouldn't Member1 have a sum of at least 2?


Duluter

 

[siperiea]

Cheers for taking a look at this Duluter.

I can't believe I spent so much time trying to get the example data right, then getting it wrong anyway! You are totally correct, Member1 should have at least 2. Correct results should read:

DateNum # MemberID # Sum of Count (over previous 3 months)
01/02/2008 # Member1 # 2
01/02/2008 # Member2 # 1
01/03/2008 # Member1 # 3
01/03/2008 # Member2 # 1
01/03/2008 # Member3 # 2
01/03/2008 # Member4 # 1
01/04/2008 # Member1 # 3
01/04/2008 # Member2 # 2
01/04/2008 # Member3 # 2
01/04/2008 # Member4 # 1
01/05/2008 # Member1 # 1
01/05/2008 # Member2 # 3
01/05/2008 # Member3 # 2
01/05/2008 # Member4 # 1

with knock on affect to the rest of the data I posted, although I can handle that, this is the part I'm stuck.

Basically, in May 2008, I'd like the total number of Issues each member has had over the previous three months (Feb 2008 - April 2008), repeated for each month there is data.

In VBA in Excel I'd have looped through the data, summing the previous three months, then move 'back' a month. In access it seems a little harder, though as I originally stated, I have next to no experience with it, so am missing something pretty obvious.

 

[duluter]

OK. I'll take a stab at this, but I'm no SQL master. I'm sure others will jump in and flesh this out.

Let's call the query you already have working qry1. I think you need to do a self join on qry1. Something like this:


SELECT a.DateNum, a.MemberID, SUM(b.SumOfCount)
FROM qry1 as a, qry1 as b
WHERE (a.MemberID = b.MemberID)
AND
(b.DateNum < a.DateNum)
AND
(DateAdd("m", -3, b.DateNum) >= a.DateNum)
GROUP BY a.DateNum, a.MemberID


I don't have a database in front of me, so I could be way off. Hope this is on the right track.


Duluter

 

[siperiea]

Cheers once again Duluter,

It's returning 0 results, but I'll keep playing around. I think this is the right direction. If anyone has any other suggestions, please feel free to add to the mix. The SQL looks right, I'll post back if I can get it working with my data.

 

[siperiea]

Aha! I think we're getting somewhere. I've reversed the date ranges parameters in the SQL and I think we're nearly there:

SELECT a.DateNum, a.MemberID, SUM(b.SumOfCount)
FROM qry1 as a, qry1 as b
WHERE (a.MemberID = b.MemberID)
AND
(b.DateNum < a.DateNum)
AND
(DateAdd("m", -3, b.DateNum) <= a.DateNum)
GROUP BY a.DateNum, a.MemberID;

It currently returns nearly correct data, but appears to only include Members if they appear that month. For instance if a Member has had an issue in March, April and May, they should appear in June with the total number of issues for the preceeding month. I have a couple of othe tables with the list of members, so I'll throw that in and see if it helps.

 

[duluter]

Yes, I see the problem.

What you could do is bring all these members into qry1 somehow, like this:

DateNum # MemberID # Count of MemberID
01/01/2008 # Member1 # 2
01/01/2008 # Member2 # 1
01/01/2008 # Member3 # 0
01/01/2008 # Member4 # 0
01/02/2008 # Member1 # 1
01/02/2008 # Member2 # 0
01/02/2008 # Member3 # 2
01/02/2008 # Member4 # 1
etc.

If the output from qry1 looked like this, then I think my above query on qry1 might work correctly.


Duluter

 

[siperiea]

I think you could be right duluter, however (as usual) the powers that be have decided they want a different report by COP, which is taking priority. I'll post back once I've been able to try the above.

Cheers for all assistance so far.

 

[duluter]

OK. If you get stuck, try the following:

Assume that you have a table or a query that has a complete listing of all the dates you are interested in reporting on. Depending on how your system is currently set up, you could generate this by querying for the distinct date values in one of your existing tables. But beware that if no members have any issues in a given month, then that month won't get pulled in and you will have to come up with a different way to generate a list of all months. Call this table or query with the complete listing of months AllDates.

Assume you have another table or query that has a complete list of all your members. We'll call this table or query Members.

You can generate what's called a cartesian product on these two tables, which basically gives you a long list of all possible date/member combinations. Generate this list like this:

SELECT AllDates.DateField, Members.MemberID FROM AllDates, Members

Once you have this, you want to left join it to qry1. When you do this, some records will come in with null values in the Count of Member ID field (because some date/member combos won't have a matching record in qry1). You want to test for null values and change them into zeros. Put the above two things together and you have this:

SELECT x.DateField, x.MemberID, nz([qry1.CountOfMemberID],0) AS CountOfMemberID
FROM (SELECT AllDates.DateField, Members.MemberID FROM AllDates, Members) AS x LEFT JOIN qry1 ON (x.MemberID = qry1.MemberID) AND (x.DateField = qry1.DateNum);

Call this qry2. Then use the SQL listed above in post #6, but instead of querying qry1, query qry2. Like this:


SELECT a.DateField, a.MemberID, SUM(b.CountOfMemberID)
FROM qry2 as a, qry2 as b
WHERE (a.MemberID = b.MemberID)
AND
(b.DateField< a.DateField)
AND
(DateAdd("m", -3, b.DateField) <= a.DateField)
GROUP BY a.DateField, a.MemberID;


I'm pretty sure that will get you where you need to be.


Duluter

 

[siperiea]

Right, think we're almost there. I have the query running up and running to get the totals, with the null values. When using this in the query to sum the last three months, it appears to be performing a running sum, rather than just totalling over the last three months. This is turning into a real headscratcher - for me anyway!

The SQL looks right, but isn't working: I think what is happening is that in "table a" it sums the last 3 months. The following month, it includes the sum in it's calculations, therefore producing a running sum. I think I need to introduce a final table to dump the results into, so that it's not including the calculated sum of the last three months in any future calculations?

For each Member:
Jan 08 = 0
Feb 08 = 1
Mar 08 = 0
Apr 08 = 2
May 08 = 0
Jun 08 = 1

Should give:
Jan 08 = 0 ## not full 3 months of data preceeding
Feb 08 = 0 ## not full 3 months of data preceeding
Mar 08 = 1 ## not full 3 months of data preceeding
Apr 08 = 1
May 08 = 3
Jun 08 = 2

But is actually giving:
Jan 08 = 0 ## not full 3 months of data preceeding
Feb 08 = 0 ## not full 3 months of data preceeding
Mar 08 = 1 ## not full 3 months of data preceeding
Apr 08 = 2
May 08 = 5
Jun 08 = 7

Not 100% sure how to proceed with this now, although I can feel we're so close to the answer!

Cheers for all your help so far Duluter.