Results 1 to 5 of 5
  1. #1
    igendreau is offline Novice
    Windows 7 32bit Access 2003
    Join Date
    Jul 2011
    Posts
    6

    Grouping Similar Items

    Can I group similar items in a query? Let's say I have a table with 2 columns (Product & Quantity) like this:

    Product Qty
    Desk-Oak 3
    Desk-Maple 6
    Desk-Metal 9
    Chair-Blue 2
    Chair-Red 5

    What I need to do is run a query that shows me how many items I have that start with "Desk", regardless of what comes after. Doable?

  2. #2
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    In your criteria: Like Desk*
    and if you want Desk and Chair, then Like Desk* or Like Chair*

    Alan

  3. #3
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    If all of your product names are set up the same way and you want to retain the label (which it seems you do)

    You could have one field in your query that was this:

    ProductType: left(product, instr(product)-1)

    and have the second field sum the quantity just like you have it now.

  4. #4
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    To "properly" normalize it and make querying a bit easier, you could separate it out into two tables as well,

    tblProductType
    productTypeID (PK)
    productTypeName

    tblProductSubType
    productSubID (PK)
    productSubName
    productTypeID (FK)

    This way you can query for all productType "Desk" without having to break a string or store calculated values.

  5. #5
    igendreau is offline Novice
    Windows 7 32bit Access 2003
    Join Date
    Jul 2011
    Posts
    6
    Great... worked like a charm. Thanks!

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

Similar Threads

  1. Help with something similar to join?
    By lagunov in forum Queries
    Replies: 7
    Last Post: 10-28-2010, 07:01 AM
  2. matching similar addresses
    By TheShabz in forum Access
    Replies: 5
    Last Post: 10-05-2010, 10:38 AM
  3. Similar to countif
    By JonHFL in forum Access
    Replies: 2
    Last Post: 06-04-2010, 10:55 AM
  4. Retrieve similar records
    By dodell in forum Queries
    Replies: 3
    Last Post: 03-31-2010, 11:48 AM
  5. If columns are similar combine?
    By westcoastbmx in forum Queries
    Replies: 0
    Last Post: 09-04-2009, 12:54 PM

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