How do I add counter for MS Access query

I have a group on query and I need to rank order the results based on the sort order that is in the query below.  Please help me.

SELECT [Manager's Holdings].Date, [Manager's Holdings].[Fund Short Name]
FROM [Manager's Holdings]
GROUP BY [Manager's Holdings].Date, [Manager's Holdings].[Fund Short Name]
ORDER BY [Manager's Holdings].Date, [Manager's Holdings].[Fund Short Name];
Query3.xls
majervisAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Do you mean this:

SELECT [Manager's Holdings].Date, [Manager's Holdings].[Fund Short Name], Count([Manager's Holdings].[Fund Short Name]) As Rank
FROM [Manager's Holdings]
GROUP BY [Manager's Holdings].Date, [Manager's Holdings].[Fund Short Name]
ORDER BY [Manager's Holdings].Date, [Manager's Holdings].[Fund Short Name];

mx
0
majervisAuthor Commented:
Not exactly.  I need a counter that starts with #1 and ends with the last record (in this instance 761)based on the sort order above.
0
hnasrCommented:
Try to use a report, setting a textbox with value=1 and running sum over group.
A query keeps updating the records and increasing the value when navigating through its records.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Well, not really a 'clean' way to do this in a query. Use hnasr's suggestion and to this in a report.  Basically, you can't create data that doesn't exist, mainly the counter.  There are some schemes using code, but ... they are problematic at best.

mx
0
majervisAuthor Commented:
Regrettably, i wouldn't know how to set that up.  I am a novice with Access.
0
hnasrCommented:
" I am a novice with Access." No problem all started like that.
Start with a report. In detail section insert a textbox; set its control source property to =1
Set running sum property to Over All.
Run report and check.
Repeat the process for groups setting the running sum over group.

If more help is needed, attach a sample database, with one table, and one report with colored text boxes where you want the counters.
Good luck!
0
hnasrCommented:
Here is a sample database, with one table, a query, a report and one module.
The counter is introduced in the query, but invoked directly in a report.
A global variable is initialized when invoking the report by clicking a button on a form.
Run form x3, and click rank
CounterInQuery.mdb
0
hnasrCommented:
Here is a sample database adding the report option with running sum as counter:

Run report a_2
CounterInQuery-and-report.mdb
0
Andrew_WebsterCommented:
You can do this using only SQL.  Here's an example from a database on mine, where I'm ranking Exercise types my alphabetical order.  The original table is simple an ID field and a test field that contains the names of exercise types.

SELECT Count(et1.ExerciseID) AS Rank, et1.Exercise
FROM ExerciseType AS et1, ExerciseType AS et2
WHERE et1.Exercise >=et2.Exercise
GROUP BY et1.Exercise
ORDER BY Count(et1.ExerciseID);

It depends on joining the data to itself.  In this example I've also aliased the table names to keep the typing to a minimum.

The result look like this:
Rank      Exercise
1      Chest Press
2      Leg press
3      Pull down
4      Quad curl
5      Seated ab crunch
6      Seated row
7      Shoulder Press
8      Thigh Extension
0
majervisAuthor Commented:
Can I do this by grouping and sorting on two fields?

SELECT [Manager's Holdings].Date, [Manager's Holdings].[Fund Short Name], Count([Manager's Holdings].[Fund Short Name]) As Rank
FROM [Manager's Holdings]
GROUP BY [Manager's Holdings].Date, [Manager's Holdings].[Fund Short Name]
ORDER BY [Manager's Holdings].Date, [Manager's Holdings].[Fund Short Name];
0
hnasrCommented:
Good comment by majervis, but watch for the following. As DatabaseMX commented "... they are problematic at best"

For multiple fields, you need to rank on concatenated fields. This means code varies with situation.
If you change the sort order, you need to change the where condition.

0
Andrew_WebsterCommented:
Let me give you a two column example (different data, but see the screenshot attached):

SELECT Count(Emp.EmpNo) AS Rank, Emp.Ename, Emp.Job
FROM Emp, Emp AS Emp_1
WHERE ((([Emp].[Job] & [Emp].[Ename])>=[Emp_1].[Job] & [Emp_1].[Ename]))
GROUP BY Emp.Ename, Emp.Job
ORDER BY Count(Emp.EmpNo);

The attached screenshot shows the original data, the data as displayed in the query, and the query in the query builder.
Untitled.png
0
majervisAuthor Commented:
I will see if I can make any of the above work.  Thanks for all the suggestions and hard work.
0
Andrew_WebsterCommented:
That's fine.  You've hit on one of the classic SQL tricks, and having this one up your sleeve can prove incredibly useful.
0
majervisAuthor Commented:
I think we are almost there.  I am stumbling a bit in my situation as I don't really have a third column like in your example witih empNO.  I have two fields (date and fund short name) that are grouped and ordered.  I have confused with the addition of the third field.  Thank you for all of your help.
0
Andrew_WebsterCommented:
Oh, right.  In Access you can't ask SQL to Count(*), it doesn't like it.  So you have to count something, right?  In this case I just used a column I knew couldn't be Null, the ID column on this table (EmpNo).

In your case, you could Count either of the columns you're using, as long as you know they won't be Null.  

In my example, you could change SELECT Count (Emp.EmpNo) As Rank... to SELECT Count (Emp.Ename) As Rank... for instance.

Clearer?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hnasrCommented:
Correction:
"Good comment by majervis" ---> "Good comment by Andrew_Webster" :)
0
majervisAuthor Commented:
Thanks Andrew.  I will give it a try.  Thanks for your patience.  While I use access alot.  I don't have to go beyond the very basics very often.
0
Andrew_WebsterCommented:
No problem.  If it all works, don't forget to accept the solution!
0
majervisAuthor Commented:
Nice back and forth.  Very patient.  A plus
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"Oh, right.  In Access you can't ask SQL to Count(*), it doesn't like it.  So you have to count something, right?"

Actually you can.  This works fine:

SELECT Count(*) AS TheRecCount
FROM Table1;


mx
0
Andrew_WebsterCommented:
Huh.  Whaddya know.  I've always fallen for the attached message and never even tried to just write the SQL.  Thanks MX!  You learn something every day on this site.
Untitled1.png
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Well, that message is a different situation, except I can't remember when it occurs.

mx
0
Andrew_WebsterCommented:
In the query builder choose "*" or double-click "*" on a displayed table, then click on the Totals button.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Humm ... well, there is still another situation where I've seen that message, but can't seem to replicate it.  I'm sure it's if you have a Grouping query.

mx
0
hnasrCommented:
Reproducing the error: In the query builder, in totlas query, selecting the tbl.* produces the error message.
Work around: Select any other field. Select Count from totals. Switch to sql view. Change the field to *. It works
0
Andrew_WebsterCommented:
Absolutely, and writing dynamic SQL in code produces no error for Select Count(*).  I think the error is there to make sure someone doesn't try GROUP BY *, which would be crazy-talk.

Now I'm thinking about this, I'm sure I've read somewhere that using Count(*) is a wise thing to do, as it must return a row count, whereas Count(MyField) has the risk of not counting Nulls in that field.  As a best practice Count(*) avoids being caught out by unexpected Nulls.

Now, using Select *.... that's another story, and IMHO should require the user to be slapped upside their head if they insist on using it!
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"I'm sure I've read somewhere that using Count(*) is a wise thing to do,"
Yes ... because Count(*) is optimized to return the full record count (per any criteria if any)

mx
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.