Solved

set record number based on grouping in a query (recordcount)  Looking for function

Posted on 2015-01-26
16
118 Views
Last Modified: 2015-01-28
Need to set a sequential numbering per a grouping from within a query.

Data = ReqNO = 1
IDNo

So
ReqNo = 1, IdNo 1
ReqNo = 1, IdNo 2
ReqNo = 1, IdNo 3
ReqNo = 1, IdNo 4
next
ReqNo = 2, IdNo 1
ReqNo = 2, IdNo 2
ReqNo = 3, IdNo 1
ReqNo = 3, IdNo2

Looking for a function I can call from within the report's datasource query.

Thanks,
0
Comment
Question by:Karen Schaefer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 3
  • +1
16 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40571505
do you have a unique record Id?
0
 

Author Comment

by:Karen Schaefer
ID: 40571519
Yes I need to have separate numbering that restarts with the next group hence.  

Example

ReqNo = 1, IdNo 1
ReqNo = 1, IdNo 2
ReqNo = 1, IdNo 3
ReqNo = 1, IdNo 4
next
ReqNo = 2, IdNo = 1
ReqNo = 2, IdNo = 2
ReqNo = 3, IdNo = 1
ReqNo = 3, IdNo=2
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40571522
what is the unique id field name?
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:Karen Schaefer
ID: 40571523
unique id field name = ID.
0
 
LVL 11

Expert Comment

by:Dany Balian
ID: 40571537
can u provide us a sample data, and how do u want the result based on ur sample?
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 40571541
select Id,ReqNo,(select count(*) from tableName where reqno= a.reqno and id <= a.id ) as IdNo
from TableName as a

change "TableName" with actual name of table
0
 
LVL 37

Expert Comment

by:PatHartman
ID: 40571634
Since you are doing this in a report, there is no need to run a separate count query for every single record in the recordsource.  Simply add a control with a controlSource of:
=1
Then set its RunningSum property to OverGroup or OverAll.
0
 

Author Comment

by:Karen Schaefer
ID: 40573046
Pat, Thanks for the suggestion, however, I rethought this issue and I will need to actually assign a value to the underlining table.

K
0
 

Author Comment

by:Karen Schaefer
ID: 40573087
Rey,

Thanks for the input, I tried your query, however the numbering is not working correctly,  for some reason, some of the ID's return the value of 3 when it should be 1.

SELECT a.ReqNo, a.Id, (select count(*) from DBAQuestions where reqno= a.reqno and id <= a.id ) AS QNo
FROM DBAQuestions AS a
ORDER BY a.ReqNo;

Open in new window

screenshot.png
0
 

Author Comment

by:Karen Schaefer
ID: 40573093
Sorry posted wrong query
SELECT a.ReqNo, a.Id, (select count(*) from DBAQuestions where reqno= a.reqno and id <= a.id ) AS QNo
FROM DBAQuestions AS a
ORDER BY a.ReqNo;

Open in new window

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40573116
upload a db with your table
0
 

Author Closing Comment

by:Karen Schaefer
ID: 40573132
Thanks for the input, disregard the last 2 post, I got it to work.
0
 
LVL 37

Expert Comment

by:PatHartman
ID: 40573884
I rethought this issue and I will need to actually assign a value to the underlining table.
Just make sure you understand that the query method you have elected to use is the worst of all possible solutions.  It must run a separate query for each row returned by the main query.  So if the query returns 1000 rows, then 1000 additional queries must run to return the count for each record.  If the query returns 10,000 records then 10,000!!!! queries must run to return the count.  The more rows the main query returns, the slower this process becomes.  The method I suggested for use in the report doesn't require running ANY additional queries since the report processes the query as a sequential recordset and so can keep track of where it is and count and break as it formats the report.  If it is necessary to assign the sequence number permanently, then the best solution is to do it in the form that adds the record.  That way it is unchanging and you only have to calculate it once.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40573929
<Just make sure you understand that the query method you have elected to use is the worst of all possible solutions.>

????

<The method I suggested for use in the report doesn't require running ANY additional queries ..>

True, but

can you use that method to assign the sequence number permanently ? NO

< then the best solution is to do it in the form that adds the record.>

how about the existing records that, how will you update them?
>> use the query
0
 
LVL 37

Expert Comment

by:PatHartman
ID: 40574011
Rey,
I think you misunderstood my objection.  It wasn't a criticism of you.  The query you suggested does do what the OP requested and the technique does work, to a point.  You answered the question literally.  The OP specifically asked for a query.   That was very likely because he didn't know that he had other options.  I was pointing out two other options, both of which would be less "expensive" than the query solution.

If the objective is to update the existing records, you have to bite the bullet and run a query similar to the one you posted but the query you posted wasn't an update query.  It was a select query so the implication is that the numbers are generated again every time the query runs.  As long as the recordset is small, it's no big deal but once you start running thousands of queries (remember - it's a separate query for EACH row), you start taking a long time each and every time the query runs.  If the only reason for the numbers is to number items in a printed report, then the method I suggested is the way to go since it doesn't degrade the process.  If the sequence number is permanent, then it should be assigned when the record is added.  

I was trying to convince the OP to either assign the number permanently or let the report assign the number as part of the printing process.  Assigning potentially new numbers each time a query runs is the least efficient of the three options.  And as the recordset gets larger, the length of time to actually run the query will be longer than any user will be happy with.

There is also a second query option which is more efficient than the extra query per row method but it does result in a non-updateable recordset.  So, if the recordset doesn't need to be updateable, I would use a self join.  The query joins a table to itself using a non-equi-join so it has to be created in SQL view and can never be displayed in QBE view.

Select GroupID, fld1, fld2, Count(*) as Seq
From tblA Inner Join tblA_1 on tblA.GroupID = tblA_1.GroupID AND tblA_1.PK < tblA.PK
Group By GroupID, fld1, fld2;
0
 

Author Comment

by:Karen Schaefer
ID: 40575786
Thanks for all the input, the query did the job I just needed to create the initial numbering. I used the sql as part of a update query and it worked great.  it was just a one off situation.

Thanks again.
Karen
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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question