rownumber/count field

[MK]

I need to create a sequence/order # field in a query so it puts the data in
order by 2 fields (company name and employee ID).

i.e.:
order company name employee ID total sales
1 acme 989785 32893.22
2 acme 9878753 455666.55
3 acme 564546 78.88
4 circuit city 892397 55687.47
5 jones cable 3893983 15036.78
6 jones cable 89211 2500.55
7 jones cable 3262222 305566.55
8 king soopers 56776 2
9 king soopers 113345456 456.55
(columns are not lined up--- but I hope you get the gist)

I tried this, but it didn’t work. – plus I want to set the order based on
the final results, not the subquery… right?
SELECT [company name], [employee id],
, sum([sales]) as [total sales]
, (select count(*)
FROM [Sales1] as c1
where c2.[employee ID] >= c1.[employee ID] ) as Seq_Order
FROM [Sales1] as c2
group by [company name], [employee id]
ORDER BY [company name], [employee id]

---- for more information, please see this post:
http://www.accessmonster.com/Uwe/Fo...s/47189/Order-identity-row-number-help-needed

 

[John Spencer]

You may need to do this in stages. Build QueryA
SELECT [company name], [employee id]
, Sum([sales]) as [total sales]
FROM Sales1
GROUP BY [company name], [employee id]

Now use that query in another query
SELECT [company name], [employee id]
, [total sales]
, (SELECT Count(*)
FROM QueryA as S2
WHERE S2.[Company Name] <= S1.[Company Name]
AND S2.[Employee Id] < S1.[Employee Id]) as SequenceNumber
FROM QueryA as S1

The following MIGHT work for you, but I think you want to generate the
sequence number as a one-up number on the results of the aggregated data.

SELECT [company name], [employee id]
, Sum([sales]) as [total sales]
,(SELECT Count(*)
FROM Sales1 as S2
WHERE S2.[Company Name] <= S1.[Company Name]
AND S2.[Employee Id] < S1.[Employee Id]) as SequenceNumber
FROM Sales1 as S1
GROUP BY [company name], [employee id]


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[KARL DEWEY]

This must be a class project as I post solution yesterday to the exact same
requirement and very same data.
SELECT (SELECT COUNT(*) FROM Sales11 Q1
WHERE Q1.[company name] & Q1.[employee id] <= Q.[company name] &
Q.[employee id]) AS Seq_Order, Q.[company name], Q.[employee ID],
Sum(Q.Sales) AS [Total Sales]
FROM Sales11 AS Q
GROUP BY Q.[company name], Q.[employee ID]
ORDER BY Q.[company name], Q.[employee ID];

 

[MK]

no, just a Szichophrenic trying to get an answer!!!

thanks... after all that, I found the best solution for the user in
excel...duh.