Group By problem

[David Ball]

Hi,

When i use Group By in a Query I always want to show my data grouped by one particular field but also show other values. Access puts Group By in all my fields and gives me options such as, Max, Count, Sum, etc.

The problem is I can't see the other values I need in my query results.

For example, say I had fields for Car Model and Manufacturer. If I want to Group by Car Model and still show the Manufacturer for each result I cant do that. Access groups by Car Model then Manufacturer is just a Sum ot Total or something I don't need. How can I set up my query so that for each car model the Manufacturer is shown in the next column?

Thanks very much.

Dave

 

[Bob Barrows]

Hi,

When i use Group By in a Query I always want to show my data grouped
by one particular field but also show other values.

Think about what a Group By is doing: it's generating a _single row_ for
each value in the grouped by field(s). Any field that is not being grouped
by needs to be aggregated in order to display it in the result.

Access puts Group
By in all my fields and gives me options such as, Max, Count, Sum,
etc.

The problem is I can't see the other values I need in my query
results.

For example, say I had fields for Car Model and Manufacturer. If I
want to Group by Car Model and still show the Manufacturer for each
result I cant do that. Access groups by Car Model then Manufacturer
is just a Sum ot Total or something I don't need. How can I set up my
query so that for each car model the Manufacturer is shown in the
next column?

?? There is a single manufacturer for each model is there not? So simply
group by the manufacturer in addition to model. Nothing says you are
restricted to a single grouping field

If it were possible to have multiple manufacturers for each model, and you
wanted to group by model only, trying to display manufacturer would result
in multiple rows for each model, which cannot happen when grouping by model
oly. Remember, the result _has_ to be a single row for each model. So, you
can display the First, Last, Max or Min manufacturer in a single column.

Alternatively, you can create a saved query that groups by model and
aggregates the data you wish to aggregate (perhaps sales). Then create a new
query that joins the saved query to the original table using model to link
them, and select the other fields you wish to display, keeping in mind that
the total sales will be repeated for each row for each model.

 

[John W. Vinson]

Hi,

When i use Group By in a Query I always want to show my data grouped by one particular field but also show other values. Access puts Group By in all my fields and gives me options such as, Max, Count, Sum, etc.

The problem is I can't see the other values I need in my query results.

For example, say I had fields for Car Model and Manufacturer. If I want to Group by Car Model and still show the Manufacturer for each result I cant do that. Access groups by Car Model then Manufacturer is just a Sum ot Total or something I don't need. How can I set up my query so that for each car model the Manufacturer is shown in the next column?

Thanks very much.

Dave

If all the members of the Car Model group have the same manufacturer, use
First as the aggregate function.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com