Results 1 to 2 of 2
  1. #1
    samos1023 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    18

    get a count of items in a query

    I have a table that is called QuoteItems. My users add individual items to a quote and an entry is added to the QuoteItems table. I want to write a query that will grad all items for an individual quote ID and calculate the quantity and extended cost. If you see my example tables below you can see that I have a table(first table) with all items in it. The second table is how I want my query to return data. i know how to get all the items for a single quote ID, what I want to be able to do is count count how many of each part number type I have and show only a single row for all fo them, then add the count to the qty colum and multiple that by the cost to get the extended cost. Any help is appreciated.



    ManufactureID PartNumber Desc Cost QuoteID
    1 x21 the new x21 12.00 1
    1 x21 the new x21 12.00 1
    2 y17 the new y17 14.00 1
    2 y18 the old y18 22.00 2



    ManufactureID Part Number Desc Cost Qty Ext Cost
    1 x21 the new x21 12.00 2 24.00
    2 y17 the new y17 14.00 1 14.00


  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    There should also be a third value for y18.

    Create an aggregate query by clicking the Totals button in the ribbon
    Add fields manufacturerID, PART NUMBER, desc and cost. All will have Group By in the Totals row of the query
    Now add cost again and change the Group by to Count. Rename the field using Qty: Cost in the top row
    Add cost a third time and change to Sum. Rename using Ext Cost: Cost
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Count items in report
    By FJM in forum Access
    Replies: 5
    Last Post: 07-10-2018, 10:01 AM
  2. Queries - Count items in a column
    By turboace in forum Queries
    Replies: 1
    Last Post: 05-21-2014, 05:12 AM
  3. Count data items by name
    By msuguy71 in forum Programming
    Replies: 9
    Last Post: 01-29-2014, 02:19 AM
  4. Replies: 8
    Last Post: 05-24-2011, 03:41 PM
  5. Count Items by Time Period
    By pawslover in forum Queries
    Replies: 1
    Last Post: 11-15-2010, 03:57 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