Monthly Report by SQL Query

The following instructions are vague, but you will be able to figure it out.

create a new datasource pointing to your RMS database. start excel and create a new pivot table using external datasource. follow "wizard" customize query in MS Query. (view | SQL) use statement below for the SQL query, return data to MS Excel. (under file menu) customize table layout.

Just replace YOURDSN with your DSN (datasource name created in 1st step above.). You will have to customize the table layout to your specs.

SELECT CONVERT(datetime, CONVERT(nvarchar, [Transaction].Time, 101), 101) AS Time, Department.Name AS Department, Sum(transactionEntry.Quantity*TransactionEntry.Price) AS TotalRetail, Sum(TransactionEntry.Cost*TransactionEntry.Quantity) AS TotalCost FROM YOURDSN.dbo.[Transaction], YOURDSN.dbo.Department Department, YOURDSN.dbo.Item Item, YOURDSN.dbo.TransactionEntry TransactionEntry WHERE Item.DepartmentID = Department.ID AND TransactionEntry.ItemID Item.ID AND [Transaction].TransactionNumber = TransactionEntry.TransactionNumber GROUP BY [Transaction].Time, Department.Name

SQL queries will give you a basic datalist type of output. Although you can get complex with them, I really am not certain as to the full scope of their abilities. The reporting engines like Crystal/SQL Reporting Services do make it more comprehensive. I'm sure some of the more prolific coders/contributors to this group may be able answer that question more fully than I. Given the layout you used in your original post and refering to Excel, the first thing that came to mind was a pivot table.