Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2020
    Posts
    2

    Query to Count Records of One Table Where a Field Matches a Field in Records of Another Table

    I have two tables. Table A has about 20 records, and Table B has about 800 records. I'd like to build a query, and all I care about are these two fields: "Location" in Table A and "Item" in Table B. I'd like to know how many "Items" in Table B begin with each "Location" in Table A. (Every "Item" in Table B begins with a "Location" from Table A.) Looks simple as I write this, but I can't figure out how to do it. TIA...

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Tell us about all the fields in your tables. Do you have you tables "joined" in a query??
    It might be helpful if you showed us some of the records from each of the tables.

  3. #3
    Join Date
    Jun 2020
    Posts
    2
    Here's a simplified illustration: F= Field
    Table A (Actually contains ~20 records. Every value for "Location" is unique.)
    F 1 | F 2 | F 3 | Location
    ...... ...... ...... Africa
    ...... ...... ...... America
    ...... ...... ...... Asia
    ...... ...... ...... Europe
    ...... ...... ...... Worldwide

    Table B (Actually contains ~800 records. Every value for "Item" begins with one of the values in Table A's "Location" field.)
    F 1 | F 2 | F 3 | Item
    ...... ...... ...... Asia: Global Warming
    ...... ...... ...... Asia: Cyber
    ...... ...... ...... America: COVID
    ...... ...... ...... Africa: Poverty
    ...... ...... ...... Worldwide: Pollution

    Resultant Query: (Actual query would consist of the same number of rows as Table A. In other words, the "Location" columns would like identical.) How do I build a query to do this? TIA...
    Location | Count
    Africa 1
    America 1
    Asia 2
    Europe 0
    Worldwide 1
    Last edited by Jiminy Cricket; 06-25-2020 at 06:00 PM. Reason: Changed to fixed-width font

  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
    So there is not actually a primary/foreign key link between these two tables?

    Need to extract the Location prefix from Item value.

    SELECT Count(*) AS CountItem, Left([Item], InStr([Item], ":")-1) AS Loc FROM TableB GROUP BY Left([Item], InStr([Item], ":")-1);

    Now LEFT JOIN that query to TableA.



    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.

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

Similar Threads

  1. Replies: 4
    Last Post: 04-06-2016, 01:29 PM
  2. Replies: 6
    Last Post: 03-23-2015, 08:16 AM
  3. Replies: 2
    Last Post: 02-26-2015, 09:33 AM
  4. Replies: 6
    Last Post: 06-24-2014, 10:09 PM
  5. Replies: 3
    Last Post: 02-19-2013, 08:15 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