Results 1 to 7 of 7
  1. #1
    mlbwhf is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Oct 2012
    Posts
    14

    Grouping results in Querey

    I created a query and everything is OK, but i wanted to group the result, here is the code



    Code:
    SELECT Equipment.Category, ([Equipment]![weekday_rate]*[Rent]![weekdays_num])+([Rent]![weekend_num]*[Equipment]![weekdend_rate])+(([Equipment]![weekday_rate]*[Rent]![weekdays_num])+([Rent]![weekend_num]*[Equipment]![weekdend_rate])*0.2) AS Income
    FROM Equipment INNER JOIN Rent ON Equipment.Equip_ID=Rent.Equip_ID;
    I want the total for all equipment which belongs to the same category

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Code:
    SELECT Equipment.Category, ([Equipment]![weekday_rate]*[Rent]![weekdays_num])+([Rent]![weekend_num]*[Equipment]![weekdend_rate])+(([Equipment]![weekday_rate]*[Rent]![weekdays_num])+([Rent]![weekend_num]*[Equipment]![weekdend_rate])*0.2) AS Income FROM Equipment INNER JOIN Rent ON Equipment.Equip_ID=Rent.Equip_ID 
    GROUP BY Equipment.Category
    The easiest way is to go to your design view of your query and click the SUMMARY (greek letter sigma, looks like a odd capital E) the default for queries is GROUP BY so all your numerical fields you'd change to SUM but it's easier to see how to do it than looking at a SQL statement

  3. #3
    mlbwhf is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Oct 2012
    Posts
    14
    ok I have those tables
    Equipment (Equip_ID, Equip_name, Equip_brand, Sup_ID, weekedays_rate, weekend_rate, Delivery_time, Category, Qty_in_stock)
    Rent (Rent_ID, Cust_ID, Equip_ID, Equip_name, hire_start, exp_return_date, act_return_date, weekday_num, weekend_num,hire_ income)/ *Cus_ID referencing Customers.Cus_ID* Equip_ID referencing Equipmnet.Equip_ID* Equip_name referencing Equipmnet.Equip_name */
    i want a report for the summary of income from hiring equipments for a specific given date range

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    so you would add criteria to your query (in your query design its on the CRITERIA line, just make sure your TOTALS line reads WHERE instead of GROUP BY), on the date field you want to filter by (actual return date?) put the criteria:

    between [Enter the Starting Date] and [Enter the Ending Date]

    the query would then return the summary in your first sql statement based on what the user enters as the starting and ending date of the actual return date.

    I don't know the terms of your rentals, if they can span multiple ranges you'd likely want the same criteria in the first OR line of the criteria for your secondary date.

  5. #5
    mlbwhf is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Oct 2012
    Posts
    14
    the only remaining thing is: The report should be sub-divided according to equipment categories, for that given date range.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,847

  7. #7
    mlbwhf is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Oct 2012
    Posts
    14

    is it possible to check some SQL queries?

    I have the following tables:
    Customers (Cust_ID, Cust_name, Cust_address, Cust_email, Type)
    Suppliers (Sup_ID, Sup_name, Sup_address, Sup_email)

    Equipment (Equip_ID, Equip_name, Equip_brand, Sup_ID, weekedays_rate, weekend_rate, Delivery_time, Category, Qty_in_stock)/ *Sup_ID referencing Supplier.Sup_ID*/

    Complain (Comp_ID, hire_ID,Equip_ID, Equip_name, Cust_ID, comp_date,Comp_action) /*hire_ID refrencing Hire.hire_ID* Equip_ID referencing Equipmnet.Equip_ID* Equip_name referencing Equipmnet.Equip_name *Cus_ID referencing Customers.Cus_ID*/

    Rent (Rent_ID, Cust_ID, Equip_ID, Equip_name, Rent_start, exp_return_date, act_return_date, weekday_num, weekend_num,hire_ income)/ *Cus_ID referencing Customers.Cus_ID* Equip_ID referencing Equipmnet.Equip_ID* Equip_name referencing Equipmnet.Equip_name */


    and i need to produce the following queries:

    - A list of names and addresses of all suppliers along with the total number of equipments from all categories they currently supply.

    Code:
    SELECT     S.Sup_name, Sup_address AS address, (SELECT SUM(E.Qty_in_stock) FROM  Equipment  E, Suppliers S GROUP BY Sup_ID) AS quantity
        FROM Suppliers S, Equipment E
        WHERE S.Sup_ID = E.Sup_ID 
    ORDER BY S.Sup_name;

    - Produce, for a given category, the total number of equipments under that category available in stock and the number currently on hire to customers.
    Code:
    SELECT     E.category, SUM(E.Qty_in_stock) AS quantity,  (SELECT COUNT(*.R) FROM Rent R, Equipment E WHERE E.Equip_ID=R.Equip_ID  AND E.Category = ‘Access Equipment’) AS rent
        FROM Equipment E, Rent R
        WHERE E.Category = ‘Access Equipment’
     AND R.exp_return_date > getdate() 
    AND E.Equip_ID =R.Equip_ID
    GROUP BY E.Category;
    Last edited by mlbwhf; 10-29-2012 at 02:59 AM.

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

Similar Threads

  1. Replies: 6
    Last Post: 05-14-2012, 07:24 AM
  2. Replies: 1
    Last Post: 04-26-2012, 09:13 PM
  3. Confounded Query Results - Grouping help
    By Phoenyxsgirl in forum Queries
    Replies: 5
    Last Post: 11-10-2011, 01:49 PM
  4. Need help grouping results.
    By cljac in forum Queries
    Replies: 9
    Last Post: 03-24-2010, 10:10 AM
  5. how to show all data in this querey
    By grad2009 in forum Queries
    Replies: 3
    Last Post: 02-08-2010, 07:35 AM

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