creating a query that has multiple statistics and keeping seperate

[Pat Chavie]

I have to create a query that 1: shows average ballance for clients. 2:
average balance for clients for a certain bookkeeper. 3: average balance for
each bookkeeper. I can do each individualy but cannot get them to work on
same query.

 

[Duane Hookom]

I can't picture how you would expect this to display. You can possibly
create columns for each bookkeeper with averages in a crosstab query.

 

[KARL DEWEY]

This might be what you are looking for. Use three queries and then a union
query.

PatChavie_BkKeeperClient --
SELECT [Pat Chavie].BookKeeper, [Pat Chavie].Client, Avg([Pat Chavie].Entry)
AS AvgOfEntry
FROM [Pat Chavie]
GROUP BY [Pat Chavie].BookKeeper, [Pat Chavie].Client;

PatChavie_Client --
SELECT [Pat Chavie].Client, Avg([Pat Chavie].Entry) AS AvgOfEntry
FROM [Pat Chavie]
GROUP BY [Pat Chavie].Client;

PatChavie_BkKeeper --
SELECT [Pat Chavie].BookKeeper, Avg([Pat Chavie].Entry) AS AvgOfEntry
FROM [Pat Chavie]
GROUP BY [Pat Chavie].BookKeeper;

UNION QUERY --
SELECT PatChavie_BkKeeperClient.BookKeeper, PatChavie_BkKeeperClient.Client,
PatChavie_BkKeeperClient.AvgOfEntry
FROM PatChavie_BkKeeperClient
UNION ALL SELECT PatChavie_BkKeeper.BookKeeper,NULL ,
PatChavie_BkKeeper.AvgOfEntry
FROM PatChavie_BkKeeper
UNION ALL SELECT NULL, PatChavie_Client.Client, PatChavie_Client.AvgOfEntry
FROM PatChavie_Client;