Running Sum on Form... (2003)

[moontear]

Hello there,

take a look at the attached database. I want to create a running sum for CommitteeID from [tbl]joinPersonnelPosition. As you can see in [frm]problem I didn't get very far...
So what I want is this: Output how many CommitteeID's there are for a certain criteria (see form) BUT within a certain year (that is what I didn't know how to implement yet).

Another thing I want to create too is text fields with the labels for the Committees taken from tblCommittees.

So what I finally want to have is frmFinal < take a look at that to see what I'M talking about.

Greetings
Dennis

[HansV]

From your database, I conclude that you don't want a running sum, but a count of records grouped by committee. You can create a Totals query for this:

SELECT tblCommittees.Committee, Count(*) AS NumberOfMembers
FROM tblCommittees INNER JOIN joinSchoolUNMemberCommittee ON tblCommittees.CommitteeID = joinSchoolUNMemberCommittee.CommitteeID
WHERE joinSchoolUNMemberCommittee.Year=2004
GROUP BY tblCommittees.Committee;

and use this as record source for a continuous form. You can replace the value 2004 by a reference to a control on a form.

[moontear]

Awesome that works.

Can you think of another possiblity to simulate the same behaviour via code? I was trying to find a solution via For Each Loops or something similar but just couldnt get any results right.

[HansV]

What would you want to accomplish by using code?

[moontear]

Well it would be nice to have one single field per Committee - that way I could implement functions such as alarm if some committee reaches a certain number.

[HansV]

Does this crosstab query do what you want?

TRANSFORM Count(*) AS TheValue
SELECT "Number" AS [Number]
FROM tblCommittees INNER JOIN joinSchoolUNMemberCommittee ON tblCommittees.CommitteeID = joinSchoolUNMemberCommittee.CommitteeID
WHERE joinSchoolUNMemberCommittee.Year=2004
GROUP BY "Number"
PIVOT tblCommittees.Committee;

[moontear]

I've never worked with crosstabs before, besides looking at them.

It does look different than the other query, but how do I work with it and what is the "Number" for?

[HansV]

You wrote "it would be nice to have one single field per Committee", so I thought you wanted to see the committees next to each other. That is what a crosstab query does. There always has to be a row header, the "Number" field acts as a dummy row header here - you don't really need it, but you can't omit it.

If I misinterpreted your post, please explain.

[moontear]

U are right, but what I meant by one single field is one single field I can do calculations with.

e.g. one textfield holding the actual number of members in Committee 1 (txtCommittee1). Now I could create a function like

IF txtCommittee1<30 Then
Me.lblTest.Caption="Everything is OK"
Else
Me.lblTest.Caption="Committee 1 too big, please lower the number of Members"
End If

[HansV]

A form based on the crosstab query would have a text box for each committee.

Alternatively, you could use one text box, and (for example) combo boxes for committee and year. The text box would have control source

=DCount("*","joinSchoolUNMemberCommittee","Committ eeID=" & [cboCommittee] & " AND Year=" & [cboYear])

[moontear]

That should do it. Thanks alot hans!

Dennis