Record Counts in Query level

[Guest]

Good Morning/Afternoon to all...

A quick question I hope....

Is there a basic function in query level that will put the record number on
each record

What I am trying to acheive is a list of customer with a revenue total put
in descending order of revenue.

example

ABC 1000
TESCO 860
McDonalds 600
J&P 400
YES 300
MICROSOFT 250

and to add in a query the record number so that I can filter off the top 3
to view

example

1 ABC 1000
2 TESCO 860
3 McDonalds 600

Kindest Regards

Jai

 

[Guest]

You can try:
SELECT TOP 3 FldA, FldB,
(SELECT COUNT FROM tableC C WHERE C.FldB>=TableC.FldB) as FldD
FROM TableC
ORDER BY FldB Desc;

You

 

[Guest]

Thanks Duane for the swift reply,

However I am somewhat... er... lost with the syntax

It passes Count as a variable, this is my SQL View

SELECT [Order by Rev].Cus, [Order by Rev].SumOfRev
(SELECT COUNT FROM [Order by Rev] where [Order by Rev].SumOfRev>= [Order
by Rev].SumOfRev) AS FieldD
FROM [Order by Rev]
ORDER BY [Order by Rev].Cus DESC;

Jai

 

[Guest]

Does it show the order that you expect?
What do you mean by "It passes Count as a variable"?
--
Duane Hookom
Microsoft Access MVP

Thanks Duane for the swift reply,

However I am somewhat... er... lost with the syntax

It passes Count as a variable, this is my SQL View

SELECT [Order by Rev].Cus, [Order by Rev].SumOfRev
(SELECT COUNT FROM [Order by Rev] where [Order by Rev].SumOfRev>= [Order
by Rev].SumOfRev) AS FieldD
FROM [Order by Rev]
ORDER BY [Order by Rev].Cus DESC;

Jai

You can try:
SELECT TOP 3 FldA, FldB,
(SELECT COUNT FROM tableC C WHERE C.FldB>=TableC.FldB) as FldD
FROM TableC
ORDER BY FldB Desc;

You

 

[Guest]

What do you mean by "It passes Count as a variable"?

Sorry .... I meant it passes the COUNT as a parameter (asks me for the value
for COUNT)

and doesn't run - it gives me the message ' At most one record can be
returned by the sub query'

Jai

Does it show the order that you expect?
What do you mean by "It passes Count as a variable"?
--
Duane Hookom
Microsoft Access MVP

Thanks Duane for the swift reply,

However I am somewhat... er... lost with the syntax

It passes Count as a variable, this is my SQL View

SELECT [Order by Rev].Cus, [Order by Rev].SumOfRev
(SELECT COUNT FROM [Order by Rev] where [Order by Rev].SumOfRev>= [Order
by Rev].SumOfRev) AS FieldD
FROM [Order by Rev]
ORDER BY [Order by Rev].Cus DESC;

Jai

You can try:
SELECT TOP 3 FldA, FldB,
(SELECT COUNT FROM tableC C WHERE C.FldB>=TableC.FldB) as FldD
FROM TableC
ORDER BY FldB Desc;

You
--
Duane Hookom
Microsoft Access MVP


:

Good Morning/Afternoon to all...

A quick question I hope....

Is there a basic function in query level that will put the record number on
each record

What I am trying to acheive is a list of customer with a revenue total put
in descending order of revenue.

example

ABC 1000
TESCO 860
McDonalds 600
J&P 400
YES 300
MICROSOFT 250

and to add in a query the record number so that I can filter off the top 3
to view

example

1 ABC 1000
2 TESCO 860
3 McDonalds 600

Kindest Regards

Jai

 

[Guest]

Try this SQL which corrects one of my errors and one of yours.

SELECT [Order by Rev].Cus, [Order by Rev].SumOfRev
(SELECT COUNT(*)
FROM [Order by Rev] R
WHERE R.SumOfRev>= [Order by Rev].SumOfRev) AS FieldD
FROM [Order by Rev]
ORDER BY [Order by Rev].Cus DESC;

--
Duane Hookom
Microsoft Access MVP

What do you mean by "It passes Count as a variable"?

Sorry .... I meant it passes the COUNT as a parameter (asks me for the value
for COUNT)

and doesn't run - it gives me the message ' At most one record can be
returned by the sub query'

Jai

Does it show the order that you expect?
What do you mean by "It passes Count as a variable"?
--
Duane Hookom
Microsoft Access MVP

Thanks Duane for the swift reply,

However I am somewhat... er... lost with the syntax

It passes Count as a variable, this is my SQL View

SELECT [Order by Rev].Cus, [Order by Rev].SumOfRev
(SELECT COUNT FROM [Order by Rev] where [Order by Rev].SumOfRev>= [Order
by Rev].SumOfRev) AS FieldD
FROM [Order by Rev]
ORDER BY [Order by Rev].Cus DESC;

Jai


:

You can try:
SELECT TOP 3 FldA, FldB,
(SELECT COUNT FROM tableC C WHERE C.FldB>=TableC.FldB) as FldD
FROM TableC
ORDER BY FldB Desc;

You
--
Duane Hookom
Microsoft Access MVP


:

Good Morning/Afternoon to all...

A quick question I hope....

Is there a basic function in query level that will put the record number on
each record

What I am trying to acheive is a list of customer with a revenue total put
in descending order of revenue.

example

ABC 1000
TESCO 860
McDonalds 600
J&P 400
YES 300
MICROSOFT 250

and to add in a query the record number so that I can filter off the top 3
to view

example

1 ABC 1000
2 TESCO 860
3 McDonalds 600

Kindest Regards

Jai