How to query for a report

[jlsccjan]

I have a table that consists of event, department, name, start date, end date, expenses. I want to make a report that shows for each unique event, which departments participated and how many people from each department participated, and if possible on the same report the expenses for each department. Expenses could be separated, but I still need by department per event. I don't care about specific names in this case. I want the event ordered by start date, but not each individual started on the same date, so the earliest start date for the event would be the criteria to order by. Can I do this with combination of select statemets in one query? If I determine the unique events how do I feed that back to determimen the count for each department.

I am afraid it is not possibleable to attach the database.

Thank you.

Jan

[alansidman]

you should be able to do this with an aggregate query. Look here for information on Aggregate Queries.

http://www.techonthenet.com/access/queries/index.php

Scroll down to the group by features.

[jlsccjan]

This helped some, but I am still having trouble. I am afraid I over simplified the problem. My report needs to order the events by start date. Since different people started at different times, events could over lap. Therefore the start date must be determined by finding the earliest start date of each event. It seems like I can do one or the other but not both. If I group on event and order by start date, I still end up with two entries for event A because someone went there after event B was already rolling. I need it grouped together. I still can't quite figure out how to create a query that determines unique events what the earliest start date was, and then list the other details under it.

[recyan]

Till Allan's back, just check if below gives some guidelines :

SELECT 
	qryEventLeastStartDateA.eventID, 
	qryEventLeastStartDateA.MinOfStartDate, 
	qryEventDeptPeopleCountExpenseSumA.department, 
	qryEventDeptPeopleCountExpenseSumA.CountOftstName, 
	qryEventDeptPeopleCountExpenseSumA.SumOfExpenses
FROM 
	(
	SELECT 
		tblEvents.eventID, 
		Min(tblEvents.StartDate) AS MinOfStartDate
	FROM 
		tblEvents
	GROUP BY 
		tblEvents.eventID
	)
	AS qryEventLeastStartDateA 
	INNER JOIN 
	(
		SELECT 
			tblEvents.eventID, 
			tblEvents.department, 
			Count(tblEvents.tstName) AS CountOftstName, 
			Sum(tblEvents.Expenses) AS SumOfExpenses
		FROM 
			tblEvents
		GROUP BY 
			tblEvents.eventID, 
			tblEvents.department
	)
	AS qryEventDeptPeopleCountExpenseSumA 
	ON 
	qryEventLeastStartDateA.eventID = qryEventDeptPeopleCountExpenseSumA.eventID;

Thanks

[jlsccjan]

This helped significantly once I figured out why it worked using test names with your exact code and why it wouldn't work on my table with my names. Seams I had a lot of names with spaces in them like a table named [Department Travel] and field names like [Last Name]. I hadn't had any problems up until now, but even using brackets, I couldn't get it to work. I renamed everything and took out the spaces and got it working. I think also words like Description are key workds because that kept throwing it off. I changed Description to something else and then it worked.

Now one more question. How do I get it to specifically list the Last names along with the department name. I still need to feed this aggragate query to another one, or vice versa to compile the info that is not aggragated.

[jlsccjan]

So I ended up solving this by dumping the results of the above aggragate query into a new table using an update query and then I was able to join that table to my original table to get the results I was looking for.