Grouping Records (1 Viewer)

[sandhurstUK]

I know there must be a simple solution to this, but I have been pulling out what little hair I have left for two days now.
I am fairly new to databases so please be gentle with me !!

Problem: I have a qry which runs fine against a table and shows all the entries (around 1000 rows of data), however, there are many records where for example the name or address or the post code (zip code) are the same. Each record has a field which shows a value for the amount of hours work due for each record.
What I want to do is to group together all records which have say the same address and sum the total hours work for each.

Any help would be immensly appreciated.

 

[KKilfoil]

What you are looking for is a summary query.

Create a new query in design view. Add the appropriate table/query you are trying to group and sum. Click on the 'Totals' tool on the toolbar (its the one with a 'Sigma' on it (or an "M" on its side!)).

A new row called 'Total' appears in the design grid. Where you want to group a field, select 'Group By'. Where you want to sum, select 'Sum', etc. Save and run the query.

HTH

 

[sandhurstUK]

Many thanks for your prompt reply.

When I select the totals it gives me Group By in every field in the qry, I can see how to select the Sum function, but do I have to remove the Group By from those fields which are not relevant and if so how ?

[This message has been edited by sandhurstUK (edited 02-15-2002).]

[This message has been edited by sandhurstUK (edited 02-15-2002).]

 

[KKilfoil]

If you are not performing a summary operation (e.g. sum, average, count, min, etc), AND if you are not using a field for grouping ('Group By') or filtering (the 'Where' option), then you do NOT include the field in the summary query, as they have no meaning in this context.

Delete those fields which are not relevant.

 

[sandhurstUK]

Excellent, works perfectly.

Many thanks