Page 1 of 3 123 LastLast
Results 1 to 15 of 38
  1. #1
    shak2 is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2010
    Posts
    23

    Question Counting across multiple fields

    Hi:

    I have a a single table with a group of fields that may have duplicate entries. I want to count each entry across all 3 fields. Ex.

    Master Database Table

    ID# App Abx1 App Abx2 App Abx3
    1 x z w
    2 u T r


    3 u x z
    and so on and so forth.

    What I need is:

    a new field that counts all instances, so:

    App Count
    r 1
    T 1
    u 2

    etc. etc.

    How do i write a code for that, or build a table, anything to get the results I need!

  2. #2
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    you can't do it with the builder. you need code. here is one way you can do it: http://www.ajenterprisesonline.com/_...me=SumFields()

  3. #3
    shak2 is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2010
    Posts
    23
    Thanks for responding. Now: What?! Which part of your link exactly am I using? I don't write codes often (self taught and all that) so can you break it down the code for me?

  4. #4
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    take a table and an identifying field:
    Code:
    Function SumFields(tblName As String, _
                       fldID As Long) As Long
    create the objects needed:
    Code:
    On Error GoTo ErrorHandler
    
    Dim fld As Field
    Dim fldSum As Long
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    open your table and find the record that the query is currently looking at:
    Code:
    Set db = CurrentDb
    Set rs = db.OpenRecordset(tblName, dbOpenDynaset)
    
       rs.FindFirst "[ID] = " & fldID
    loop the fields and sum the values in that record:
    Code:
    For Each fld In rs.Fields
        If fld.Name <> "ID" Then
            fldSum = fldSum + fld
        End If
    Next fld
    
    SumOfFields = fldSum
    cleanup the objects:
    Code:
    ErrorHandler:
       rs.Close
       db.Close
       Set rs = Nothing
       Set db = Nothing
       
    End Function
    in the query's sql, the temp field will read:
    Code:
    select SumOfFields(thisTable, [ID]
    where the identifying field is NAMED ''ID''

    and it will output:
    Code:
    SumOfFields
    make more sense?

    in your situation, you will loop the fields and check for what you need to. Surely you know how to write short conditionals "if, then" in VBA?

  5. #5
    shak2 is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2010
    Posts
    23
    Hey:

    LOL. Not really but i'm willing to try. I'll post again if it doesn't work. Thanks a lot!

  6. #6
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    no prob.

    I guess I'm saying that anyone entrusted with any sort of technology should have a basic understanding of what code is supposed to do. The lowest level of the algorythm is the statement of choice. the first statement (most likely) ever created to offer a choice was:
    Code:
    if [this]
      do [opt1]
    if not
      do [opt]
    every language has this type of statement (I think!) because computers are built on the algorythmic concept. this is basically what started the idea of programming languages. without it, computers would not exist.

  7. #7
    shak2 is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2010
    Posts
    23
    Alright now I don't even know where to begin. How do i even get to the VBA

  8. #8
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    ALT+F11 when viewing db.

    upload your file and I'll do it for you. (if you want)

  9. #9
    shak2 is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2010
    Posts
    23
    Thanks but I can't do that. All protected info. Sigh... Thanks anyway

  10. #10
    shak2 is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2010
    Posts
    23
    I guess i'm confused on what parts of this code I need to change exactly. The table name is Mdb.t and the field names are app abx 1, app abx 2, and app abx 3. Just as an FYI there are a possible 30 options for each field so am i going to have to write some extra string for each option?

  11. #11
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    what identifies each field? what's your PK field name?

  12. #12
    shak2 is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2010
    Posts
    23
    my primary key is the ID

  13. #13
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    the example table and example you gave in post 1 is not very clear Shak. Please elaborate on that.

    As is, I can't really understand what you need. EG - you have 6 fields in the example table and 3 values in the example records. please make it consistent so we can help you out...

  14. #14
    shak2 is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2010
    Posts
    23
    Ummm actually It's 3 fields. The ID field is just my primary key. format didn't quite work out right

    abx1 abx2 abx3
    T U V
    X T V
    V T X

    What I want:

    SomeNewField Count
    T 3
    U 1
    V 3
    X 3

    Does this make more sense?

  15. #15
    shak2 is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2010
    Posts
    23
    abx1 abx2 abx3
    T U V
    X T V
    V T X

    What I want:

    SomeNewField Count
    T 3
    U 1
    V 3
    X 3

    Does this make more sense?

    I should also point out that those field entries, T, U, V, X are options from 30 choices. So using the alphabet as the example (a-z right) users can choose any of those for fields abx1, abx2, and abx3

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Counting multiple items in a query
    By slaterino in forum Access
    Replies: 2
    Last Post: 10-14-2010, 08:21 AM
  2. Replies: 4
    Last Post: 09-22-2010, 01:47 AM
  3. Constract of Multiple Fields
    By KLynch0803 in forum Programming
    Replies: 6
    Last Post: 02-01-2010, 07:27 AM
  4. Replies: 1
    Last Post: 12-10-2009, 08:41 PM
  5. Multiple Fields In One Combo Box.
    By caljohn527 in forum Forms
    Replies: 1
    Last Post: 02-20-2009, 03:07 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums