Grouping results in Querey

[mlbwhf]

I created a query and everything is OK, but i wanted to group the result, here is the 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

[rpeare]

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

[mlbwhf]

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

[rpeare]

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.

[mlbwhf]

the only remaining thing is: The report should be sub-divided according to equipment categories, for that given date range.

[orange]

Grouping on a report tutorial from FunctionX
http://www.functionx.com/access/Lesson25.htm

[mlbwhf]

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.

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.

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;