Results 1 to 10 of 10
  1. #1
    Peter3 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2017
    Posts
    12

    Query result in rows

    Hi,

    I have a quastion:

    I wrote a Query that count records in each column of my table, and give mi additional information on the percentage fill of each column.
    Something like that:

    Table:

    ID column_A column_B column_C column_D
    1 23 4 340 460
    2 430 540 4560
    3 3540 453 30 40
    4
    4320
    24320
    5
    6786
    4320
    6
    786
    432
    7




    320
    8
    2230 3420 3420
    9 34530 5434530 2342
    10 46540 546540 654650 5640


    query:

    Code:
    SELECT Count(Tabela1.ID) AS table_size, Count(Tabela1.column_A) AS count_A, Count(Tabela1.column_A)/Count(Tabela1.ID) *100 AS A_percent, Count(Tabela1.column_B) AS count_B, Count(Tabela1.column_B)/Count(Tabela1.ID) *100 AS B_percent, Count(Tabela1.column_C) AS count_C, Count(Tabela1.column_C)/Count(Tabela1.ID) *100 AS C_percent
    FROM Tabela1;
    result:
    table_size count_A A_percent count_B B_percent count_C C_percent
    10 5 50 9 90 6 60


    Please advise me how to write a query, or use a different way to get a result in rows.
    Something like that:



    ID 10

    columns_a 5, 50

    columns_b 9, 90

    columns_c 6, 60

    Many thanx in advance
    Attached Files Attached Files

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    UNION query.

    SELECT table_size, "A", count_A AS Cnt, A_percent AS Pct FROM test_query
    UNION SELECT table_size, "B", count_B, B_percent FROM test_query
    UNION SELECT table_size, "C", count_C, C_percent FROM test_query;

    or UNION the original table and use that as source for subsequent queries. UNION query rearranges data into normalized structure.

    SELECT ID, column_A AS Data, "A" AS Source FROM Tabela1 WHERE Not column_A Is Null
    UNION SELECT ID, column_B, "B" FROM Tabela1 WHERE Not column_B Is Null
    UNION SELECT ID, column_C, "C" FROM Tabela1 WHERE Not column_C Is Null
    UNION SELECT ID, column_D, "D" FROM Tabela1 WHERE Not column_D Is Null;

    There is no designer or wizard for UNION, must type in SQL view of query builder.

    Why exclude column_D?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Peter3 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2017
    Posts
    12
    Quote Originally Posted by June7 View Post

    Why exclude column_D?
    - becouse it was only example

    THANX!!!!
    You helped me a lot - I am very grateful. I have never used UNION query before. Now it works.

    I have one more quastion,if you could advise me how to do it, to add to my query columns:


    For example - still my table:

    ID column_A column_B column_C column_D
    1 23 4 340 460
    2 430 540 4560
    3 3540 453 30 40
    4
    4320
    24320
    5
    6786
    4320
    6
    786
    432
    7


    320
    8
    2230 3420 3420
    9 34530 5434530 2342
    10 46540 546540 654650 5640

    Your union query result:

    table_size Expr1001 Cnt Pct
    10 A 5 50
    10 B 9 90
    10 C 6 60


    And i want to add to your query two columns:
    - that count records >100
    - that count record Like "Cnt(Like "1[0-3][0-6][0-7]""

    table_size Expr1001 Cnt Pct Cnt(>100) Cnt(Like "1[0-3][0-6][0-7]")
    10 A 5 50
    10 B 9 90
    10 C 6 60

    How can this be done?

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Calculate 8 new fields in your original query:

    A_Greater100: Count(IIf([column_A]>100,1,Null))

    A_x: Count(IIf([column_A] Like "1[0-3][0-6][0-7]",1,Null))

    Then adjust the UNION accordingly.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Peter3 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2017
    Posts
    12
    Quote Originally Posted by June7 View Post
    Calculate 8 new fields in your original query:

    A_Greater100: Count(IIf([column_A]>100,1,Null))

    A_x: Count(IIf([column_A] Like "1[0-3][0-6][0-7]",1,Null))

    Then adjust the UNION accordingly.
    I tried this way but it still returns an error of bad using agregate function..
    I'm tired, but I can not write it correctly

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Works for me:

    SELECT Count(Tabela1.ID) AS table_size, Count(Tabela1.column_A) AS count_A, Count(Tabela1.column_A)/Count(Tabela1.ID)*100 AS A_percent, Count(Tabela1.column_B) AS count_B, Count(Tabela1.column_B)/Count(Tabela1.ID)*100 AS B_percent, Count(Tabela1.column_C) AS count_C, Count(Tabela1.column_C)/Count(Tabela1.ID)*100 AS C_percent, Count(IIf([column_A]>100,1,Null)) AS A_Greater100, Count(IIf([column_A] Like "1[0-3][0-6][0-7]",1,Null)) AS A_x
    FROM Tabela1;
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Peter3 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2017
    Posts
    12
    You are Great! I am very grateful, works fine!!!

  8. #8
    Peter3 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2017
    Posts
    12
    I would not like to abuse your patience, but I would like to ask you something more..

    Extending your yesterday's expression - condition: (Like "1[0-3][0-6][0-7]",1,Null)) AS A_x) , can we count two more cases:

    1. Fields that have specific number of characters (for example max. 30 characters (A-Z, 0-9 only))
    2. Fields that have only one character - specifiied from the list (for example Y,N,Z, or B)

    I can do it in Excel, but dosent work for me in this query..

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    IIf(Len([fieldname])<=30,1,Null) AS LTE30

    IIf([fieldname] LIKE "[y,n,z,b]",1,Null) AS Alpha1

    Keep in mind Access is not case sensitive by default. Y=y
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    Peter3 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2017
    Posts
    12
    THANX!!

    Best Regards
    P.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 11-30-2014, 08:03 AM
  2. Replies: 3
    Last Post: 04-30-2014, 11:19 AM
  3. Replies: 4
    Last Post: 04-22-2014, 08:23 AM
  4. Replies: 2
    Last Post: 06-24-2013, 12:37 PM
  5. Replies: 1
    Last Post: 12-03-2011, 01:26 AM

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