Results 1 to 13 of 13
  1. #1
    dennisb82 is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Aug 2017
    Posts
    43

    Query Sum of quantity’s for different products per customer/store

    I’m a beginner in making Query’s (and SQL).

    In the Example file I have a Query. In the last Column I made a query to count the products per Customer/Storenr. Now I want different columns and count the quantity of Product A, B and C.

    Also I want to show all stores, even if don’t have any products. I need an OUTER JOIN I think. When I replace INNER JOIN with OUTER JOIN in the SQL-View, it's not working.



    I think this is a basic question, but can’t find the right solution. I played around with the criteria, but no luck.
    Attached Files Attached Files

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Not sure that I really understand your requirement but perhaps the attached db helps
    Attached Files Attached Files
    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
    dennisb82 is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Aug 2017
    Posts
    43
    Thanks Bob! Interessting and very usefull.

    I like to learn from this and recreate this myself.
    When does the <> column appear?

    When I want to show the Address and City it makes a record for each product again.

    I would like to see the columns:
    Customer, Storenr, City, Address, Qty(Product A, Qty(Product B), Qty(Product C).

  4. #4
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by dennisb82 View Post
    Thanks Bob! Interessting and very usefull.

    I like to learn from this and recreate this myself.
    Is this a crosstab query wizard? and put "SomVanQty: Som(Nz([Qty];0))" column by hand?
    When does the <> column appear?
    Yes that is a crosstab query. Google for more info. For example: https://support.office.com/en-gb/art...0-2cd8484667e8

    The Nz() function can be used to return the value specified after the , if the value in the field/expression before the , is a null value. Lots of info if you google it. For example: https://www.techonthenet.com/access/...dvanced/nz.php
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  5. #5
    dennisb82 is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Aug 2017
    Posts
    43
    Tanks a lot!

  6. #6
    dennisb82 is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Aug 2017
    Posts
    43
    Hi Bob,

    I'm almost done with my little project.

    I copied the query and used it for a make-table. See Example database.
    When I run the copied query and open the table (tblqryCountPerStore) I see the products (A, B and C) are counted per row per store.
    I want the table to show the count of A, B and C on 1 row. Just like the qryCountPerStore.

    Maybe you have an idea how to do this?
    Attached Files Attached Files

  7. #7
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Sorry, but I don't understand your requirement.
    Perhaps you can post an example of what you want the query to return.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  8. #8
    dennisb82 is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Aug 2017
    Posts
    43
    Hi Bob, Thanks for the reply

    I want a table with the result of qryCountPerStore. See the example file.

    I tried to do this by copying the query and convert it to a make-table query.
    So when I run "Copy of qryCountPerSore", the table tblCountPerStore is made.
    But it don't show the exact result of the qryCountPerStore.

    So how can I get a table with the result of the qryCountPerStore?

  9. #9
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by dennisb82 View Post
    Hi Bob, Thanks for the reply

    I want a table with the result of qryCountPerStore. See the example file.

    I tried to do this by copying the query and convert it to a make-table query.
    So when I run "Copy of qryCountPerSore", the table tblCountPerStore is made.
    But it don't show the exact result of the qryCountPerStore.

    So how can I get a table with the result of the qryCountPerStore?
    Why do you need to create a table and then create another query that uses this new table. Why not just use the existing query.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  10. #10
    dennisb82 is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Aug 2017
    Posts
    43
    I only want to create a table.

    Your query (qryCountPerStore) is fantastic.
    But when I copy this query and convert it to a make-table query, it shows the information in another way in the table that is made by the query.

    See the example and run both query's and see the diffrence between the tblCountPerStore and qryCountPerStore.

    The table that is made using "Copy of qryCountPerStore" shows a row for product a, b and c per store. Then a store can have 3 rows (for each product 1 row).
    I want to use your query (qryCountPerStore) to make a table with the same result. For each store a row and a column for product a, b and c.

    hopefully this clarifies the issue

  11. #11
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    I want to use your query (qryCountPerStore) to make a table with the same result. For each store a row and a column for product a, b and c.
    Yes, I understand that you want to make a table. The question is why you feel the need to make a table when you already have a query that returns the data required .
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  12. #12
    dennisb82 is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Aug 2017
    Posts
    43
    We use the table to make some changes that has to be made by a human being.
    After the changes are made, we make reports for our drivers from this table.

    We must be able to remove some rows, so the driver don't go to that store. This dissisions are made by number of product, milage to drive, events in city, closed store, etc.
    Sometimes the warehouse manager make a change in the count of products.

    So by getting this table, we can make some changes and give the driver a good report with the actual route to drive and the products per store.

  13. #13
    dennisb82 is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Aug 2017
    Posts
    43
    Quote Originally Posted by Bob Fitz View Post
    Not sure that I really understand your requirement but perhaps the attached db helps
    Hi Bob,

    Maybe you can help me with this thread: https://www.accessforums.net/showthread.php?t=68501

    I want to add a yes/no field to the cross-table query you helped me with.
    Maybe you have an idea?

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

Similar Threads

  1. Replies: 6
    Last Post: 12-11-2013, 09:27 PM
  2. Replies: 3
    Last Post: 07-11-2013, 02:48 AM
  3. Lookup OnHand Quantity F/ Products Table
    By burrina in forum Forms
    Replies: 1
    Last Post: 11-30-2012, 11:22 PM
  4. Replies: 8
    Last Post: 04-24-2012, 01:05 PM
  5. products and quantity
    By woody in forum Forms
    Replies: 2
    Last Post: 02-06-2011, 11:58 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