• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 299
  • Last Modified:

Dynamically Number Records in by Grouping using Access 2007

I am using an Access database to manage some inspection information. I have a scenario where I have to dynamically number points associated with a location. The point numbers will be sequential starting a 1 for each location.  The PrcReq drives the order based on a percentage (calculated elsewhere).  One gotcha, whenever PrcReq = -100% it indicates an error and these should be at the bottom of the sort order.  

Thank you for the support! Sample Data Desired Result
0
mmcrain
Asked:
mmcrain
  • 7
  • 5
  • 3
2 Solutions
 
Rey Obrero (Capricorn1)Commented:
try this query

select Location as [Point ID],(select count(*) from tableX as X where X.location=TableX.location and X.PrcReq<=tableX.PrcReq) as [Point]
from TableX
order by TableX.PrcReq
0
 
SheilsCommented:
I take it that you are entering the data directly into a table. If so this is not good practice.

I notice that you have -9% at higher order than 10% yet -100% is even lower. This does not make much sense and also make it impossible to set the sort order as you requested.Either you sort ascendingly or descendingly. You can't have a mixture of both in a sort order.

To create the incremental numbering that you are requesting first create the following query

Select * from tableName
Order by [point id],[PrcReq]

Let call this query qrySort

Then set the default value of the textbox on the data entry form to
DMax("point","qrySort","location=" & Me.location)

0
 
mmcrainAuthor Commented:
capricorn1, almost there. The only thing I need to do is drop the -100% values to the bottom of the list.
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
Rey Obrero (Capricorn1)Commented:
change the order by to descending

select Location as [Point ID],(select count(*) from tableX as X where X.location=TableX.location and X.PrcReq<=tableX.PrcReq) as [Point]
from TableX
order by TableX.PrcReq desc
0
 
Rey Obrero (Capricorn1)Commented:
perhaps


select Location as [Point ID],(select count(*) from tableX as X where X.location=TableX.location and X.PrcReq<=tableX.PrcReq order by X.PrcReq desc) as [Point]
from TableX
order by TableX.PrcReq desc
0
 
mmcrainAuthor Commented:
sb9, -100% represents and Error in the data that the end users will have to address. For the purpose of this utility that should not be the first point referenced. The data is being appended from another table and then I use queries to do a few validation checks.
0
 
SheilsCommented:
Guy the suggested order is impossible.

-10<10<-100 is a wrong order. Access will not be able to compute it.

The correct order would be

-100<-10<10

or

10>-10>-100

0
 
SheilsCommented:
sb9, -100% represents and Error

replace it by 999% or something then your sort order will work.
0
 
mmcrainAuthor Commented:
capricorn1, I was able to get around the -100% issue and was able to run this against my production data. A location with multiple points that have the same reading.

For Location 1 the PrcReq values are .19, .19, .19, .19 as a result all four records returned a result of 4 for the Point.
0
 
Rey Obrero (Capricorn1)Commented:
yes, that will be true.
does your table have a unique record ID?
0
 
mmcrainAuthor Commented:
Yes, there is a auto number that I add to the table when it is imported into Access. Call it ImpKey.
0
 
Rey Obrero (Capricorn1)Commented:
try this


select Location as [Point ID],(select count(*) from tableX as X where X.location=TableX.location and X.ImpKey<=tableX.ImpKey) as [Point],PrcReq
from TableX
order by TableX.PrcReq desc
0
 
mmcrainAuthor Commented:
I am getting a unique number, but the sequence is based on the autonumber sort instead of PrcReq.
Sample.accdb
0
 
mmcrainAuthor Commented:
I ended in referencing http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_24702905.html

 
'Create the Variables and Objects
Dim rstTx   As DAO.Recordset
Dim rstTxTmp  As DAO.Recordset
Dim lngTMLCounter As Long
Dim lngTML As Long

'Set the Variables and Objects
Set rstTx = CurrentDb.OpenRecordset("SELECT * FROM TableX ORDER BY Location, PrcReq")
Set rstTxTmp = CurrentDb.OpenRecordset("SELECT * FROM TableX_Tmp")
    
    'Delete the existing reords from the temp table
    CurrentDb.Execute "DELETE * FROM TableX_Tmp", dbFailOnError
    
    'Move to the first record in the main recordset
    rstTx.MoveFirst
        'Do until the last record
        Do Until rstTx.EOF
            'Add a new record to the secondary Recordset
            rstTxTmp.AddNew
            'Set the values for the secondary recordset
            rstTxTmp!Location = rstTx!Location
            rstTxTmp!PrcReq = rstTx!PrcReq
            rstTxTmp!aNum = rstTx!aNum
                'If the Main TML is the same as the Ref TML Number
                If rstTx!Location = lngTML Then
                    'Increase the counter by one
                    lngTMLCounter = lngTMLCounter + 1
                Else
                    'Reset the counter to 1
                    lngTMLCounter = 1
                End If
                'Set the Point Number to the Counter variable
                rstTxTmp!Point = lngTMLCounter
            'Save the records in the temp table
            rstTxTmp.Update
            'Set the Ref Location  to current Main table Location
            '(to be used for comparison in the next loop)
            lngTML = rstTx!Location
            'Move to the next record in the main table
            rstTx.MoveNext
        'Keep looping
        Loop
        
    CurrentDb.Execute "UPDATE TableX INNER JOIN TableX_Tmp ON TableX.aNum = TableX_Tmp.aNum SET TableX.Point = [TableX_Tmp].[point]"
        
        'Completion Confirmation
        MsgBox "Done.", vbInformation
        
'Cleanup
rstTx.Close
rstTxTmp.Close
Set rstTx = Nothing
Set rstTxTmp = Nothing

Open in new window


This worked, although I would still like to be able to do it without using a temporary table.
Mike
0
 
mmcrainAuthor Commented:
I ended up having to find an alternative approach to meet all of the requirements. However the query provided by Capricorn1 met the initial requirements and was easy to implement.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 7
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now