Results 1 to 2 of 2
  1. #1
    basher99 is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2017
    Posts
    1

    Question Sub-query help(SQL inside)

    My group is doing a term project where we have to create a database for an online book store. Currently I am trying to query(sub-Query) the total amount of sales we have in each book category. As of right now, I have it working to a degree. However, the total I am getting is the same for each category. I just need some pointer in the right direction to get me moving again. My groupmates are just as stumped as I am right now



    SELECT Category_Description, Sum(Price * Quantity) AS Sales
    FROM Book, Order_Items, Book_Category, Categories
    WHERE Book.Book_Id = Book_Category.Book_Id
    AND Book.Book_id = Order_Items.Book_id
    AND Category_Description In (Select Category_Description From Categories, Book_Category Where Book_Category.Category_Id =Categories.Category_Id)
    GROUP BY Category_Description;

    Above is my current SQL for the query and sub-query.

    Thanks all for helping the new guy

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Why don't you use JOIN clause? Are you using Access query builder in Design View?

    Why do you need subquery for what appears to be a simple aggregation?

    Consider building a report and use its sorting and grouping features. This will allow display of detail records as well as summary calcs.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Can I set PK inside my union query?
    By niloufar in forum Queries
    Replies: 8
    Last Post: 06-25-2014, 08:56 AM
  2. Replies: 1
    Last Post: 09-05-2012, 07:04 AM
  3. Replies: 1
    Last Post: 06-25-2012, 07:01 PM
  4. TimeDiff calculations inside Query.
    By cap.zadi in forum Queries
    Replies: 1
    Last Post: 09-15-2011, 09:27 AM
  5. Filtering and using First() inside a query
    By Gilligan in forum Queries
    Replies: 17
    Last Post: 03-10-2011, 05:52 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