Sorting in Queries

[Charles]

Hi,

I am trying to write a query that assigns contributions for each account
considered to either East or West depending on which territory has the larger
contribution.

I am wrote a queryStep1:

SELECT [tbl_Transactions].month_end_date, [tbl_Transactions].account,
[tbl_Transactions].contribution, [tbl_Transactions].territory
FROM [tbl_Transactions]
ORDER BY [tbl_Transactions].account, [tbl_Transactions].contribution DESC ,
[tbl_Transactions].territory;

Then wrote queryStep2:

SELECT step1.month_end_date, step1.account, Sum(step1.contribution) AS
SumOfcontribution, First(step1.territory) AS FirstOffterritory
FROM step1
GROUP BY step1.month_end_date, step1.account;

I thought query Step2 acting on queryStep1 would provide the total amount of
contributions and list the territory (East or West) that had the lion's share
of contributions (for the account concerned). I thought this would occur
since contribution amounts for each account are listed in decreasing order so
the territory with the lion's share would come first.

However, in the result, sometimes the territory with the smaller
contribution (for an account) is listed.

Can someone help me with what I am doing wrong with my queries and how I can
correct them?

Thanks

 

[Ken Snell]

Try this:

SELECT step1.month_end_date, step1.account, Sum(step1.contribution) AS
SumOfcontribution, First(SELECT DISTINCT TOP 1 T.Territory
FROM step1 T) AS FirstOffterritory
FROM step1
GROUP BY step1.month_end_date, step1.account;

 

[KARL DEWEY]

Try this --

SELECT [tbl_Transactions].month_end_date, [tbl_Transactions].account,
[tbl_Transactions].territory, Sum([tbl_Transactions].contribution) AS
SumOfcontribution
FROM [tbl_Transactions]
GROUP BY [tbl_Transactions].month_end_date, [tbl_Transactions].account,
[tbl_Transactions].territory
ORDER BY [tbl_Transactions].month_end_date,
SUM([tbl_Transactions].contribution) DESC, [tbl_Transactions].territory,
[tbl_Transactions].account;

If this does not do what you want then post example of desired output.