Order/identity/row number help needed

[zizi2007 via AccessMonster.com]

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]


thank you for your help.
Z

 

[Michel Walsh]

You don't need to rank your data if you just want it ordered:

SELECT *
FROM sales1
ORDER BY [company name], [employee id]



Vanderghast, Access MVP

 

[KARL DEWEY]

This should be working unless you are seeing it in a report. If in a report
the you do know that Access ignores all sorts in a query and only uses the
Sorting and Grouping within the report.
Open the report in design view, click on menu VIEW - Sorting and Grouping.
Select your fields in the new window.

 

[zizi2007 via AccessMonster.com]

sadly, I NEED that seq # field for later... we export this data (from a
query via a report, ugh)... into excel.... manipulate and sort the data every
which way (there are more fields than I mentioned).... and then want to put
it back in the original order as it was in the export...

in the excel file, we could just add a column after it's exported, but I was
trying to find a way to do it in access.

it's too easy to do in SQL server, why can't it be that easy in Access??? ha
ha

thank you.

You don't need to rank your data if you just want it ordered:

SELECT *
FROM sales1
ORDER BY [company name], [employee id]

Vanderghast, Access MVP

 

[zizi2007 via AccessMonster.com]

I also tried making a function:

Function QCntr(x) As Long
Cntr = Cntr + 1
QCntr = Cntr
End Function

but, I don't know how to call that from the query-- if I send "1" as the
parameter, every row (614 of them) has a 1 in my "seq order" field.

 

[zizi2007 via AccessMonster.com]

I just tried this:

SELECT Employee_ID, Name, Company, State
, sum([Sales]) AS Sales1
, sum(commission) AS commission1
, sum(Actual_Cost) AS Actual_Cost1
,
(select count(*)
from [csi_1] as c1
where c1.Company+c1.Employee_ID = c2.Company+c2.Employee_ID
and c1.Company+c1.Employee_ID <= c2.Company+c2.Employee_ID
) AS Seq_Order
FROM [CSI_1] AS c2
GROUP BY Employee_ID, Name, Company, State
ORDER BY Company, Employee_ID

and every seq_order is 570 (s/b 1 to 614)

when I change to this:
and c1.Company+c1.Employee_ID < c2.Company+c2.Employee_ID


SELECT Employee_ID, Name, Company, State
, sum([Sales]) AS Sales1
, sum(commission) AS commission1
, sum(Actual_Cost) AS Actual_Cost1
,
(select count(*)
from [csi_1] as c1
where c1.Company+c1.Employee_ID = c2.Company+c2.Employee_ID
and c1.Company+c1.Employee_ID < c2.Company+c2.Employee_ID
) AS Seq_Order
FROM [CSI_1] AS c2
GROUP BY Employee_ID, Name, Company, State
ORDER BY Company, Employee_ID

and every seq_order is 0

 

[KARL DEWEY]

I did not understand you before. Try this --
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];