|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
February 12th, 2007, 11:52 AM
posted to microsoft.public.access.queries
|
|||
|
|||
Record Counts in Query level
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 
|
|
#2
February 12th, 2007, 12:33 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Record Counts in Query level
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 "Jai_Friday" wrote: 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
|
|
#3
February 12th, 2007, 02:13 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Record Counts in Query level
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 "Duane Hookom" wrote: 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 "Jai_Friday" wrote: 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
|
|
#4
February 12th, 2007, 03:01 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Record Counts in Query level
Does it show the order that you expect?
What do you mean by "It passes Count as a variable"? -- Duane Hookom Microsoft Access MVP "Jai_Friday" wrote: 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 "Duane Hookom" wrote: 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 "Jai_Friday" wrote: 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
|
|
#5
February 12th, 2007, 03:18 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Record Counts in Query level
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 "Duane Hookom" wrote: Does it show the order that you expect? What do you mean by "It passes Count as a variable"? -- Duane Hookom Microsoft Access MVP "Jai_Friday" wrote: 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 "Duane Hookom" wrote: 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 "Jai_Friday" wrote: 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
|
|
#6
February 12th, 2007, 04:12 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Record Counts in Query level
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 "Jai_Friday" wrote: 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 "Duane Hookom" wrote: Does it show the order that you expect? What do you mean by "It passes Count as a variable"? -- Duane Hookom Microsoft Access MVP "Jai_Friday" wrote: 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 "Duane Hookom" wrote: 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 "Jai_Friday" wrote: 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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
