Results 1 to 12 of 12
  1. #1
    kerriff is offline Novice
    Windows XP Access 2021
    Join Date
    Nov 2022
    Posts
    7

    Query for Lookup List Word Counts

    I have several tables with unique (text) values that serve as lookup wizard lists for my main tables. Inside my main tables, several columns draw from these lists, some duplicating.



    What I need is a query which tells me how many times each unique value (text) appears in my main tables. I haven't been able to find an answer on how to do this yet. Ideally, I would like the query to count each term for each "list" and provide each count as a percentage if possible.

    I am new to Access so any specific advice (exact how-to) would be greatly appreciated.

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Sounds like you need a "Totals" query.

    Can you post a copy of your db
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    We need more info. Table design(s), sample of your expected output showing some values and format.

  4. #4
    kerriff is offline Novice
    Windows XP Access 2021
    Join Date
    Nov 2022
    Posts
    7
    Let me know if this helps clarify. I'm attaching some screenshots.

    Click image for larger version. 

Name:	db list.png 
Views:	16 
Size:	63.5 KB 
ID:	491711: one of my unique value lookup "lists"

    Click image for larger version. 

Name:	main table.png 
Views:	16 
Size:	197.0 KB 
ID:	491722: a main table using the first list in a column ("Action Taken")

    So for this example, I need a query that counts how many times the various "action" terms (banned, retained, relocated, etc) appear in the main table.



    Click image for larger version. 

Name:	subject headings list.png 
Views:	17 
Size:	93.5 KB 
ID:	491733: another unique value "lookup" list

    Click image for larger version. 

Name:	books table.png 
Views:	16 
Size:	231.9 KB 
ID:	491744: a main table using the lookup (image 3) in columns "Subject 1-10"

    Again, I'd like to know how many times each subject heading term from the list appears in the multiple columns using that lookup in the table.

    There are multiple lists I would like to run this same query for and again, am curious if there is a way to include a percentage calculated as well.
    Attached Thumbnails Attached Thumbnails 3.jpg  

  5. #5
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    The images are not clear enough for me.

    Are you able to attach the database
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  6. #6
    kerriff is offline Novice
    Windows XP Access 2021
    Join Date
    Nov 2022
    Posts
    7

  7. #7
    kerriff is offline Novice
    Windows XP Access 2021
    Join Date
    Nov 2022
    Posts
    7
    I posted a link, which apparently has to be approved by a moderator before it's viewable.

    https://drive.google.com/file/d/10e0...ew?usp=sharing

  8. #8
    kerriff is offline Novice
    Windows XP Access 2021
    Join Date
    Nov 2022
    Posts
    7
    ID School Types
    1 Elementary
    2 Middle
    3 Junior High
    4 High

    ID Reasons Cited
    1 Profanity/language
    2 Innapropriate/mature content
    3 Violence
    4 Politically, racially, or socially offensive
    5 None specificed
    6 School shooting
    7 Sexual content
    8 Nudity
    9 PTSD
    10 Drug and alcohol used
    11 LGBTQ+
    12 Religious sensibilities
    13


    ID Actions Taken
    1 Banned
    2 Alternate book/assignment provided
    3 Decision pending/under review
    4 Relocated
    5 Censored
    6 Age/use restricted
    7 Removed from lower libraries
    8 Retained
    9 Removed from reading list

    ID Title Author (Last) Author (First) Series Challenge DCC LCC Subject 1 Subject 2 Subject 3 Subject 4 Subject 5 Subject 6 Subject 7 Subject 8 Subject 9 Subject 10
    1 The conformity Jacobs John Hornor No FIC
    Paranormal fiction Maryland







    2 Two white rabbits Buitrago Jairo No 863.77
    Rabbits Immigrants Illegal immigration Noncitizens Emigration and immigration Fathers and daughters Counting


    3 Popularity papers : research for the social improvement and general betterment of Lyrida Goldblatt & Julie Graham-Chang Ignatow Amy Yes FIC PZ7.I248 Popularity Scrapbooks Best friends Schools Friendship Children's stories, American Middle school students


    4 This one summer Tamaki Mariko No 741.5 PZ7.7.T355 Vacations Friendship Summer Interpersonal relations Teenage girls Teenagers--Conduct of life Adolescence Love in adolescence

    5 Just another hero Draper Sharom M. No FIC 813.6 African Americans High schools Interpersonal relations School violence High school seniors Schools



    6 How to read literature like a professor : a lively and entertaining guide to reading between the lines Foster Thomas C. No 808 PN45 Books and reading Literature Criticism Literature--Explication





    7 The cat in the hat Seuss Dr. Yes E PZ8.3.G276 Cats Cat in the Hat (Fictitious character) Children's stories, American Nonsense verses Stories in rhyme Brothers and sisters Wit and humor, Juvenlie Play

    8 George Gino Alex No FIC PZ7.1.G576 Gender identity Middle school students Bullying Friendship Transgender children Transgender youth Gender nonconformity Transsexuals

    9 The toritilla curtain Boyle T. Coraghessan No 813.54 PS3552.O932 Mexicans California--Los Angeles Married people Ethnic relations Noncitizens Illegal immigration American fiction Domestic fiction

    10 Tales from a not-so fabulous life Russell Rachel Renee Yes FIC PZ73 Friendship Popularity Diaries Artists Schools Maxwell, Nikki J Children's stories Middle schools

    11 Poisoned apples : poems for you, my pretty Heppermann Christine No 811.6 PS3608.E66 Teenage girls Children's poetry, American Fairy tales Poetry Self-acceptance Self-realization Conduct of life


    12 Beauty queens Bray Libba No FIC PZ7.B7386 Survival Beauty contests Castaways Pirates Contests Humorous stories Friendship Islands

    13 Reluctantly Alice Naylor Phyllis Reynolds No FIC PZ7.N24 Single-parent families Brothers and sisters Fathers and daughters Children's stories, American Families Schools McKinley, Alice (Fictitious character) Junior high schools

    14 In the middle of the night Cormier Robert No FIC PS3568.O243 Revenge Detective and mystery stories







    15 Drama Telgemeier Raina No 741.5 PZ7.7.T45 Interpersonal relations Theater Teenage boys Teenage girls Middle school students Middle schools Schools Teenagers

    16 The hate u give Thomas Angie No FIC PZ7.1.T448 Race relations Police shootings Witnesses Police-community relations United States African American teenagers African American families African American teenage girls

    17 Killer : a pretty little liars novel Shepard Sara No FIC PZ7.S5432 Pennsylvania Conduct of life Friendship Secrecy Young adult fiction Detective and mystery stories Family secrets Teenage girls

    18 Mistakes were made Pastis Stephan Yes FIC PZ7.P269 Private investigators Polar bear Friendship Humorous stories Detective and mystery stories Schools Boys Girls

    19 Push : a novel Sapphire
    No 813.54 PS3569.A63 New York (State)--New York--Harlem Child abuse Literacy African American girls African Americans Incest Teenage mothers Domestic fiction

    20 Death benefits Harvey Sarah N. No 813.60 PS80000 Caregivers Grandparent and child Grandfathers--Care Families





    21 SP4RX McDonald Wren No 741.5973 PN6727.M3828 Technology Hackers Young adult fiction






    22 What my mother doesn't know Sones Sonya No FIC PZ7.5.S66 Dating (social customs) Romance fiction Free verse Novels in verse Young adult poetry, American First loves



    23 Ghost story Thompson Julian F. No FIC
    Models (Persons) Vermont Ghost stories Ghosts Hotels




    24 The graveyard book : Volume 1 Russell P. Craig No 741.5973 PZ7.7.R87 Cemetaries Dead Orphans Paranormal fiction Ghost stories Graphic novels Ghosts Comic books, strips, etc

    25 The graveyard book : Volume 2 Russell P. Craig No 741.5973 PZ7.7.R87 Cemetaries Dead Orphans Good and evil United States Comic books, strips, etc Graphic novels Paranormal fiction


    ID Year Title School School Type Series Multiple Schools ISD Reason 1 Reason 2 Reason 3 Action Taken
    1 2019 The conformity Roosevelt Elementary Elementary No No McAllen Profanity/language Innapropriate/mature content
    Banned
    2 2019 This one summer Oak Run Middle School Middle No No New Baunfels Innapropriate/mature content

    Banned
    3 2019 Two white rabbits Liberty Elementary Elementary No No Azle Violence Politically, racially, or socially offensive
    Banned
    4 2019 Popularity papers : research for the social improvement and general betterment of Lyrida Goldblatt & Julie Graham-Chang Herndon Intermediate Middle Yes No Royse City None specificed

    Banned
    5 2019 Just another hero Sunnyvale High School High No No Sunnyvale Violence School shooting
    Banned
    6 2019 How to read literature like a professor : a lively and entertaining guide to reading between the lines Aubrey High School High No No Aubrey Sexual content

    Alternate book/assignment provided
    7 2019 The cat in the hat Sam Houston State University Charter School High Yes No Sam Houston State University Charter School Politically, racially, or socially offensive

    Decision pending/under review
    8 2019 George Garfield Elementary Elementary No No San Felipe Del Rio Innapropriate/mature content

    Relocated
    9 2019 George Lamar Elementary Elementary No No San Felipe Del Rio Innapropriate/mature content

    Relocated
    10 2019 The toritilla curtain Little Elm High School High No No Little Elm Innapropriate/mature content

    Censored
    11 2019 Tales from a not-so fabulous life Vernon Elementary Elementary Yes No Royse City Innapropriate/mature content

    Relocated
    12 2019 Poisoned apples : poems for you, my pretty Scott Elementary Elementary No No Temple Innapropriate/mature content

    Relocated
    13 2019 Beauty queens Conroe ISD High School High No Yes Conroe Profanity/language Sexual content
    Age/use restricted
    14 2019 Reluctantly Alice Stockdick Junior High Junior High No No Katy Nudity

    Age/use restricted
    15 2019 In the middle of the night Kerr Middle School Middle No No Burleson Violence PTSD
    Alternate book/assignment provided

  9. #9
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Because your db is not properly normalized (you have Reason1, Reason2,....,Subject1,Subject2,...) this won't be an easy task, so if it is not too late I would recommend you change your design to create separate tables for those.

    A small db sample would help us see if there is an easy solution for the current design.

    I would start by creating a query that concatenates all Reasons in a calculated field:
    Combined_Reason:"|" & Nz([Reason1],"N\A") & "|" & Nz([Reason2],"N\A") & Nz([Reason3],"N\A")

    I am attaching a module that has some very useful string manipulation functions, the one you want is SF_count(), you would use that in another query based on your lookup table to count the occurance of each entry in the combined field (don't forget to add the pipe characters before and after to distinguish between entries that could be part of another entry like "Banned" and "Banned for life" or similar).

    Please post back if you get stuck.

    Cheers,
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    3 moderated posts approved.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    kerriff is offline Novice
    Windows XP Access 2021
    Join Date
    Nov 2022
    Posts
    7
    The multiple Reason and Subject columns draw on created unique item lookup lists as well. The reason for this is because each record (ex: line litem 1 "The conformity") includes multiple "Reasons" for inclusion, which draw from the following list:

    ID Reasons Cited
    1 Profanity/language
    2 Innapropriate/mature content
    3 Violence
    4 Politically, racially, or socially offensive
    5 None specificed
    6 School shooting
    7 Sexual content
    8 Nudity
    9 PTSD
    10 Drug and alcohol used
    11 LGBTQ+
    12 Religious sensibilities
    13

    As we add more records of individual incidents, each row will include multiple reasons for inclusion. So the goal for me is to know how many times the various Reasons (ex. "Violence") appears in the records tables.

    I'm not including the unique Subject list, as it's too long for the forum limits, but its' the same basic idea. Each record in that Books table includes multiple Subjects, drawing from that lookup list.

  12. #12
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    But my point is that that is not the proper way to design a relational database. What are you going to do when you encounter a record that need 4 reasons instead of the included 3? or a book with 12 subjects instead of 10. You will need to modify all the affected queries, forms, reports to add those extra fields. If you create a table where you store the primary key of the main table (Books) as a foreign key and you add the subject ID it becomes trivial to add a new subject as it is just another record.

    You also use lookup fields in the table design which will give you trouble in future development: http://access.mvps.org/access/lookupfields.htm

    Please review the update file to see a couple of examples for the counts, one for the action and one for reason.

    Cheers,
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Word recipient list empty with Access query.
    By wackywoo105 in forum Queries
    Replies: 7
    Last Post: 09-02-2014, 09:58 AM
  2. Query to get unique counts
    By jakeman in forum Access
    Replies: 3
    Last Post: 02-15-2013, 11:34 PM
  3. Query - Counts
    By maxx102 in forum Access
    Replies: 1
    Last Post: 12-14-2012, 12:49 PM
  4. Query to list only records containing a word
    By esh112288 in forum Queries
    Replies: 4
    Last Post: 11-01-2012, 05:24 PM
  5. list lookup query
    By gully300 in forum Queries
    Replies: 7
    Last Post: 08-11-2011, 09:47 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