Sum Function On Subform Help

[Bluezman]

On the attached database (a functional similar database to the one I'm trying to code correctly) I'm trying to get a subform to total and am having a hard time getting it to do so.

I have an original thread in the VBA & Modules area:

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=47529

that goes through what I've tried to do so far, and even Pat Hartman has tried helping, but am still unsuccessful in getting it to work properly.

I've created a textbox on the subform footer called [TotalDays] and given it the control source of:

=Sum(IIf(IsDate([txtPrev]),DateDiff("d",[txtPrev],[fldDate]),"0"))

Perhaps those in this area might have better luck at seeing what they can come up with.

Thanks!

Bluez

 

[Cosmos75]

This might work

I think it does the calculations correctly. I didn't use your user defined function but used two queries instead. You may be able to tweak it.

The one things that I was unable to do was to NOT show a previous date for first record for the second member.


But the calculations are correct, I think. You may want to double check that.


Anyway, that's all I could come up with for now.

You may want to test if it still works when you add and delete new Members and records. Let me know if it doesn't and I'll try to fix it.

Let me know if you need more help and I'll try my best to help!


Hope it helps!

 

[Bluezman]

Thanks Cosmo, I looked over what you did and it looks great. It will take some ingenuity to get it transferred to the database I'm working on, but I'll let you know how it goes.

Just wondering, but what were your thoughts concerning how it found the DaysDifference before and why it is so hard to get the total to work on that subform? I wonder if it was because of the function instead of doing it with queries??

Thanks for all the work you did, and again I'll let you know how it goes.

Bluez

 

[Rich]

Post a 97ver of your db

 

[Cosmos75]

I take it back...

I just thought of something, my query won't work properly because it assumes that fldPK will always be sequential in the same order as MemberID without any gaps!

MemberID, fldPK
1, 1
1, 2
1, 3
1, 4
2, 5
2, 6
2, 7
2, 8

So if you delete and add haphazardly and have records like this

MemberID, fldPK
1, 1
1, 8
1, 6
1, 4
2, 3
2, 1
2, 2
2, 7

It won't work. Probably need to tweak that Statement from PrevlfdID: fldID -1 to something else... (A bit brain-dead from working on my own db right now).

Probably go something

-Rank fldDate for MemberID = 1, and do this for each MemberID so that it'll start from 1 for eadh MemberID. (Assuming that the dates are what you need to look at, if you need to look at which date was entered first you can just rank the lfdPK)

- So for the records where Rank of lfdDate = 3, take (Rank of fld -1) and use a similiar method to get the previous fldDate

Make sense? I don't have time to try it out now, but maybe that'll work for you.

 

[Bluezman]

Hi Rich.. here is the 97 version of that database. It's not exactly the same as the database I'm working on, but the subform total will work exactly the same way, so any help you might be able to give would be great.

Thanks!!

Bluez

 

[Cosmos75]

Here's an version that hase two different forms.

One used aggregate functions so if you have a HUGE amount of records it'll REALLY run slowly.


Here's Pat Hartman's explanation
"Using a DSum() or the suggested function in a query is inefficient because of the requirement to re-read massive amounts of data. To understand the inefficiency you need to know how to program. To create a report or sub that calculates a running sum, you only need to sort your input file into the correct sequence before you start. You pass the recordset ONLY once. As you read each record, you add the current value to the running sum variable. If you are producing a report, you write the detail line, if you are producing a recordset, you write the current record and move on to the next record in the input recordset. You NEVER need to process the same record more than once.

The way DSum() and the sample function posted by Rich work, is that for each record of the input recordset, they re-read all the records of the recordset with a key lower than the current one and sum them. The amount of I/O required can be enormous if the recordset contains more than a few hundred rows. Here's a small example of how the processing goes:


Key Action # Reads
Record1 re-read records <1 0
Record2 re-read records <2 1
Record3 re-read records <3 2
Record4 re-read records <4 3
Record5 re-read records <5 4
Record6 re-read records <6 5
Record7 re-read records <7 6
Record8 re-read records <8 7
Record9 re-read records <9 8
Record10 re-read records <10 9
Total extra reads 45

As you can see, for a recordset of just 10 records, 45 extra reads are required. Not to mention the fact that each of these requires code to calculate 10 sums rather than just 1.

Don't get me wrong, DSum() and the other similar functions have their uses. Just NOT in a query or in the detail section of a report. Using them in a form is not such a problem since the form is working with only one record at a time. So although if you scrolled through the entire recordset one record at a time, you would effectively reproduce the effect of doing the DSum() in a query, no user is likely to page through thousands of records."

The other form should run much faster (frmSelectMembers) since it uses SELECT statements (but I do use two DLoookup() funditons but I haven't been able to show a total on the subform yet.

 

[Cosmos75]

Here it is (Access 2000), unfinished for you to play with. Maybe you'll be able to improve it! I'll try to get back to it when I have time.

 

[Cosmos75]

Access 97 Version

 

[EMP]

I think the PrevRecVal() function is causing a problem in the Sum() function. Since the sum is actually the difference between the max and min dates, you can use a workaround.


Change the subform to a continuos form.

Change the Control Source of TotalDays to:
=DMax("fldDate","tblReason","membID=" & [Forms]![Memberfrm]![membID])-DMin("fldDate","tblReason","membID=" & [Forms]![Memberfrm]![membID])

Add a line of code in the After Update event of fldDate to refresh the subform:
------------------------
Private Sub fldDate_AfterUpdate()
Me.Refresh
End Sub
------------------------

 

[Cosmos75]

EMP,

An Inspired Solution!!

Bluezman,

EMP's solution will work MUCH better than mine!

One cautinary note, If you're function uses DAO, it may be hard to upgrade to ADO or whatever programming language becomes the standard language.

 

[Bluezman]

I agreee, both solutions are worthy of being looked at. Speed is not an issue with the users of this database. Compared to what they had (old dbase III system) anything is better.

One aspect of EMP's solution that I'll have to work with is that, while this example database theoretically has the same problems I'm experiencing, my actual database has a couple of extra wrinkles that will make it more of a pain to implement. In the example, all days are just one right after each other (hence his observation that the sum is just really the difference between the min and max dates) when in reality, on my database I only want to count the days difference between certain records (other records will not affect the days difference at all depending on the typeID), but at least it's a start in the right direction.

I am kind of wondering what Rich will come up with using his PrevRecVal and subbing the subform. Being that he seems to have a great handle on the PrevRecVal usage, he might bring a slightly different slant to all this.

Thank you both for the work you've done. I'm sure this will not be the last question I'll have for either one of you, and I hope that asking more questions later will not wear you out.. LOL

Bluez

 

[Bluezman]

Over the weekend, I've tried to implement into my actual database, what Cosmo and EMP suggested, but without luck.

Cosmo & EMP, what I'd like to do is send you a stripped down version of this database (reduced to about 5 records) so you can see what I'm trying to do and what has been done so far.

If this OK with you, please contact me (via my profile and email address) and let me know where to send it.

Currently this database is in Access 2000 format. I can convert it to Access 97 if you need it, but not sure of how well it will function.

Thanks!

Bluez

 

[Rich]

You could either use the RunSum on a report or a RunSum function of the form

 

[EMP]

You can zip and attach an Access 97 DB here. Most people can open Access 97 DBs. However, if your DB contains code in ADO, it must be left in Access 2000 format.

There is a file size limit here. To make your DB as small as possible, you can compact before zipping it.

 

[Bluezman]

Here is the Access 2000 version of my db. If you have any questions please email me and I will try to explain any issues.

Bluez

 

[Bluezman]

Any luck figuring out this calculation with the supplied DB? I've been beating my brains out and still have no luck.

Any progress reports would be appreciated

Bluez

 

[Bluezman]

Anyone have any ideas yet?

This must have everyone stumped.

Please post anything you might feel is helpful.

Thanks!!

Bluez

 

[Bluezman]

Still needing some assistance if anyone is needing a challenge.

Sorry to be such a bother, but I'm truely stuck on this one

Thanks in advance!!

Bluez

 

[Cosmos75]

Bluezman, I haven't had time to look at your db file (busy with one of my own), will try to find time to look at it but I can't promise anything!