Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2008

    Unanswered: Counting records in query and populating new field with total results..

    Hi there, I'm new to MS Access...

    Can anybody please help me with the following issue:-

    I have a Query table and I need to count the instances of records "L1", "L2" and "L3" in the 'Query Table' for each field (70+) and create / display new fields called "Totals_of_L1" , "Totals_of_L1", "Totals_of_L1" (sub totals counts of L1. L2 & L3 per record/row ?

    Click this image ti view screen grab- Click image for larger version. 

Name:	1.jpg 
Views:	98 
Size:	34.3 KB 
ID:	8883

    For example:
    Field_1 __ Field_2 __ Field_3 __ Field_4 __ Field_5____Total_of_L1__ Totall_of_L2__Total_of__L3
    _L1 _________L1 ______L1_____L2_______ L3_____________3___________1___________1____
    _L2 _________L2 ______L3_____L1_______ L3_____________1___________2___________2____
    _L3 _________L3 ______L3_____L2_______ L3_____________0___________1___________4____

    ...and so on for each row etc...

  2. #2
    Join Date
    Apr 2004
    metro Detroit
    Total_of_L1:iif(field_1="L1",1,0)+iif(field_2="L1" ,1,0)...

  3. #3
    Join Date
    Nov 2007
    Adelaide, South Australia
    ▲ Agreed, but what is L1 and L2 etc supposed to mean?

    I suspect a table design flaw tbh...
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!

    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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