Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Running Sum on Form... (2003)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Running Sum on Form... (2003)

    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.

  3. #3
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Sum on Form... (2003)

    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.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Running Sum on Form... (2003)

    What would you want to accomplish by using code?

  5. #5
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Sum on Form... (2003)

    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.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Running Sum on Form... (2003)

    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;

  7. #7
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Sum on Form... (2003)

    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?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Running Sum on Form... (2003)

    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.

  9. #9
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Sum on Form... (2003)

    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

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Running Sum on Form... (2003)

    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])

  11. #11
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Sum on Form... (2003)

    That should do it. Thanks alot hans!

    Dennis

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •