Displaying other fields in a total calc query? 1

[nathanstevenson]

Hello,

There must be a really simple answer to this question:

In a query with many "information rows" how do you display those rows, group by one field, and show the totals (grouped by that one field) for other fields.

I get "You tried to execute a query that doesn't include the specified expression "informative field" as part of an aggregate function.

Any ideas?
Nathan

 

[jebry]

Hi Nathan!

If I understand your question correctly all you need to do is the following:

Query1

Select Field1, Sum(Field2) As SumField2, (sum rest of the fields that need summing) From Table1 Group By Field1

Query2 Select Table1.Field1, Info1, Info2, etc, SumField2, etc. From Table1 Inner Join Query1 On Table1.Field1 = Query1.Field1

hth
Jeff Bridgham
bridgham@purdue.edu

 

[nathanstevenson]

Thanks Jebry!

Kind of got what you were saying... for those who aren't so SQL orientated:

Perform 2 queries - one with the calculated fields, the other with the "info" fields with at least one field common to both of them, then create a third query that puts them together.

Works like a dream!!

 

[nathanstevenson]

Hi there,

I am having a bit of a problem ... I have done an "info" query, and a "calc" query and put them together in a third query, but no matter how I change the join type it still repeats all the entries from the calc table for each "info" record. It should be the other way round, e.g only showing the "info" for each seperate calc. THere are 87 calc results, and 377 infos, I need to limit it to 87, but like I said, everytime I change the join type it remains the same!

Help!

 

[jebry]

Hi!

Try adding the word Distinct after the word Select in the third query.

hth
Jeff Bridgham
bridgham@purdue.edu