Grouping table data

[nicksource]

I have a form that has a sub-table in it, it lists loads of dates and the job the goods were sent out to from a table called GoodsOutJob.

Under each row, it then lists (using another sub-table) all the products from the GoodsOutProd table for that job/date - with this I can expand and collapse the dates to see or hide the products underneath.

What I want to do though, is because there can be several jobs on the same date, is to group all the dates (the GoodsOutJob) so they collapse/expand too in the form. So I only see one date, like 04/08/08, then when I expand it, I see all the jobs that went out under this date (at the moment it lists each date).

How do I do this?

The forms purpose is to show a summary of the dates, so I can see what has gone out on certain days.

Thanks,
Nick.

 

[khawar]

It could be done through a query and also a pivot table
Check the attached db

 

[nicksource]

Thanks, I see the GROUP BY in the query but do not understand what the SUM and COUNT are required for?

Here is my query, I wish to group by DateOut:

SELECT GoodsOutJob.ID, GoodsOutJob.JobID, GoodsOutJob.DateOut, GoodsOutJob.Reserved
FROM GoodsOutJob;

Could you explain the SUM and COUNT part?

Thanks in advance.

 

[khawar]

Just to show the summary of total day