Results 1 to 7 of 7
  1. #1
    billmark is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Aug 2016
    Posts
    13

    Combine number in the same field with same product ID

    Hi


    I have an access table with over 2000 record and want to combine the number in the field Weight, Amount and Cost under the same Product ID field. Please advise how to write the query to combine.

    Asof Client No Client Name ProdID Product Name Weight Amount Cost
    20191129 XYZ Dummy 0130Z3P46 Brown Sugar 23,835.78 23,733.29 23,733.29
    20191129 XYZ Dummy 0130Z3P46 Brown Sugar 176,193.76 175,436.13 175,436.13
    20191129 XYZ Dummy 1350Z74J0 Lime 11,441.18 11,413.71 11,413.71
    20191129 XYZ Dummy 1350Z74J0 Lime 24,026.42 23,969.72 23,969.72
    20191129 XYZ Dummy 235851102 Strawberry 144.58 28,033.45 25,919.67
    20191129 XYZ Dummy 235851102 Strawberry 1,901.93 368,780.77 174,616.83
    20191129 XYZ Dummy 2852533 Pineapple 254.52 71,223.79 70,940.46
    20191129 XYZ Dummy 2852533 Pineapple 372.27 104,173.71 119,761.68
    20191129 XYZ Dummy 2GFKW32 Orange 44,811.27 44,639.06 44,639.06
    20191129 XYZ Dummy 2GFKW32 Orange 424,623.20 423,050.98 423,050.98
    20191129 XYZ Dummy 5069211E Pear 122.43 12,318.14 9,688.79

    Regards
    Bill

  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
    I'm sorry but I don't understand the requirement.

    Perhaps you could give an example of the result you want for a couple of the records you have shown us.
    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
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    You need a totals query:
    Code:
    SELECT table1.Asof, table1.[Client No], table1.[Client Name], table1.ProdID, table1.[Product Name], Sum(table1.Weight) AS SumOfWeight, Sum(table1.Amount) AS SumOfAmount, Sum(table1.Cost) AS SumOfCostFROM table1
    GROUP BY table1.Asof, table1.[Client No], table1.[Client Name], table1.ProdID, table1.[Product Name];

  4. #4
    billmark is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Aug 2016
    Posts
    13

    Combine number in the same field with same product ID

    Here is the mdb format.
    Attached Files Attached Files

  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
    Quote Originally Posted by billmark View Post
    Here is the mdb format.
    Uploading your db may be useful but not until your requirement is understood. Please read again my post#2
    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
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by billmark View Post
    Here is the mdb format.
    Uploading your db may be useful but not until your requirement is understood. Please read again my post#2
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  7. #7
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    You didn't respond to post 2 or 3, so here's my best GUESS.
    Click image for larger version. 

Name:	invSource.png 
Views:	8 
Size:	17.9 KB 
ID:	40748

    And here's the result:

    Click image for larger version. 

Name:	invRslt_.png 
Views:	7 
Size:	18.9 KB 
ID:	40749

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

Similar Threads

  1. Replies: 5
    Last Post: 10-02-2019, 01:35 PM
  2. Replies: 2
    Last Post: 04-22-2015, 12:28 PM
  3. Replies: 6
    Last Post: 02-28-2015, 11:57 AM
  4. Replies: 3
    Last Post: 01-18-2015, 06:05 PM
  5. Count Number of People Per Product
    By Lorlai in forum Queries
    Replies: 3
    Last Post: 06-29-2011, 06:15 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