[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 869
  • Last Modified:

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
0
majervis
Asked:
majervis
  • 8
  • 7
  • 7
  • +1
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
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 MVP, Access and Data Platform)Commented:
"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 MVP, Access and Data Platform)Commented:
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 MVP, Access and Data Platform)Commented:
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 MVP, Access and Data Platform)Commented:
"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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 8
  • 7
  • 7
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now