none
Access Database Help Grouping fields and Sorting RRS feed

  • Question

  • Hello I'm in the process of creating my first ever database on Access for a small business. I have two tables so far, one for Customer Profile(CP) and another for Customer Orders(CO). Each customer has a unique ID and customers are known to place several orders on several different dates. I want to write a query that will return results of all customer orders depending on certain criteria I choose. I want the data to be grouped by the Customer ID and sorted by date which is on another table.  Below is an example of the results I'd like to see vs. what I keep getting.

    My results:

    Account#_CP , OrderDate_CO, ItemOrdered_CO, Quantity_CO, Amount_CO

    1234 8/1/2015 T2312 2 $500
    5426 8/2/2015 T1231 1 $399
    1234 8/3/2015 T3212 2 $800

    What I'd like:

    1234 8/1/2015 T2312 2 $500
    1234 8/3/2015 T3212 2 $800
    5426 8/2/2015 T1231 1 $399

    I'd like the orders to be grouped by account number but also sorted by date. I can only seem to get one or the other. I'm thinking it's how I've set everything up. Any help would be appreciated. 

    Wednesday, August 12, 2015 11:05 PM

Answers

  • I want the data to be grouped by the Customer ID and sorted by date which is on another table.  

    I'd like the orders to be grouped by account number but also sorted by date. I can only seem to get one or the other.

    You did not say where you are viewing the data. It should be in a query, form, or report.

    In query design view select Ascending for account and for date fields.

    If you still have trouble post your query SQL by selecting SQL View, highlight all, copy, and paste in a post.


    Build a little, test a little

    Thursday, August 13, 2015 3:09 AM

All replies

  • Hi Sir,

    Do you mind sharing us the follow info that it will help us to assist you better?

    • Table design Structure
    • Query sentence

    Then, I'll move your thread to Access forum:

    http://social.msdn.microsoft.com/Forums/en-US/home?forum=accessdev&filter=alltypes&sort=lastpostdesc

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    George Zhao
    TechNet Community Support


    It's recommended to download and install Configuration Analyzer Tool (OffCAT), which is developed by Microsoft Support teams. Once the tool is installed, you can run it at any time to scan for hundreds of known issues in Office programs.

    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.
    Thursday, August 13, 2015 2:58 AM
  • I want the data to be grouped by the Customer ID and sorted by date which is on another table.  

    I'd like the orders to be grouped by account number but also sorted by date. I can only seem to get one or the other.

    You did not say where you are viewing the data. It should be in a query, form, or report.

    In query design view select Ascending for account and for date fields.

    If you still have trouble post your query SQL by selecting SQL View, highlight all, copy, and paste in a post.


    Build a little, test a little

    Thursday, August 13, 2015 3:09 AM
  • I guess you're just getting your feet wet.  Maybe a few video tutorials will help you get the results you want.

    https://www.youtube.com/watch?v=AykV47ObmFA

    https://www.youtube.com/watch?v=YBwGcslqP00

    https://www.youtube.com/watch?v=boPlhRMFpFk


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Thursday, August 13, 2015 2:30 PM