Numbering Rows in a query by an ID records

Topic

New Reply

Hi! can this be done..I’m trying to create a query that numbers all the row-records that appear for an individidual ID; i.e. “1-4 0r 6” if that is how many records are available on a particular transaction. Any help is appreciated…:-)

Reply | Quote

Replies

Although it is possible to number rows in a query, it is tricky and not very efficient. It’s better to do this in a report:
– Create a text box with control source =1
– Set the Running Sum property of the text box to Over All if you want to number all records from 1 to (the number of records).
– Set the Running Sum to Over Groups if you want the numbering to restart at 1 at each grouping level of the report.

Reply | Quote

Although it is possible to number rows in a query, it is tricky and not very efficient. It’s better to do this in a report:
– Create a text box with control source =1
– Set the Running Sum property of the text box to Over All if you want to number all records from 1 to (the number of records).
– Set the Running Sum to Over Groups if you want the numbering to restart at 1 at each grouping level of the report.

The reason I asked is because I need to place the data from the query into a table by using an append query. This table is subordinate to another table which has the header information. Can I perform your recommendation in a form.

Reply | Quote

The reason I asked is because I need to place the data from the query into a table by using an append query. This table is subordinate to another table which has the header information.

Can you expand on this with a bit of detail of tables, fields, relationships and
which tables/fields are to be updated.
Also the values. Your 1-4 0r 6 was a bit confusing to follow.

Reply | Quote

Your 1-4 0r 6 was a bit confusing to follow.

It’s like 25 or 6 to 4…

Reply | Quote

You, sir, are a wealth of knowledge, including but not limited to old rock bands!

Reply | Quote

It’s like 25 or 6 to 4…

No worries. The header table has: a one to many relationship to the detail table. Fields: (key-no duplicates)-BillNo, PersonID,InvDate,year, cycle

subordinate table to Header: AcctAssets has fields: (key-no duplicate)-AcctNo,BillNo,GroupID,AcctInfo,Amt, Notes.

I would like the query to get the personID and provide how many AcctNo records under that BillNo before appending the datas to the table.

Third table is subordinate to AcctAssets and will need the same as the previous table: HoursOnJob has fields: (key-no duplicate)-SubAcctNo, AcctNo, BillNo, position, abbr, hours, rate, Amt.

So in theory:

-Header will have one row of a BillNo with the main total of all subordinates.
-AcctAssets will have how many accts (AcctNo) the BillNo used with their respective totals.
-HoursOnJob will then break it down by SubAcctNo used in AcctNo on a BillNo with hours and position utilized.

Hope you can undertand what I wrote..:-)

Reply | Quote

No worries. The header table has: a one to many relationship to the detail table. Fields: (key-no duplicates)-BillNo, PersonID,InvDate,year, cycle

subordinate table to Header: AcctAssets has fields: (key-no duplicate)-AcctNo,BillNo,GroupID,AcctInfo,Amt, Notes.

I would like the query to get the personID and provide how many AcctNo records under that BillNo before appending the datas to the table.

Third table is subordinate to AcctAssets and will need the same as the previous table: HoursOnJob has fields: (key-no duplicate)-SubAcctNo, AcctNo, BillNo, position, abbr, hours, rate, Amt.

So in theory:

-Header will have one row of a BillNo with the main total of all subordinates.
-AcctAssets will have how many accts (AcctNo) the BillNo used with their respective totals.
-HoursOnJob will then break it down by SubAcctNo used in AcctNo on a BillNo with hours and position utilized.

Hope you can undertand what I wrote..:-)

Not quite sure why you would want to append anything.
If all the data already exists in the relevant tables, you ought to be able to extract relevant data out using a Grouping query.
Reckon it will be easier to see an actual example with expected output, because I suspect there is a better solution other than appending.

Reply | Quote

Not quite sure why you would want to append anything.
If all the data already exists in the relevant tables, you ought to be able to extract relevant data out using a Grouping query.
Reckon it will be easier to see an actual example with expected output, because I suspect there is a better solution other than appending.

Here is a sample of database and two images of what I am trying to do automated.Thanks for all your help.

Reply | Quote

The thread Item Number (2003) should give you some ideas.

Reply | Quote

The thread Item Number (2003) should give you some ideas.

HansV, thanks for the thread, I got the code below to add the number to the first record but is not looping to the next record in the subform. Any ideas?

Private Sub Form_Load()

Me.VCHR_LN_NO = Nz(DMax(“VCHR_LN_NO”, “VCHR_LN”, “AutoID =” & Me.AutoID), 0) + 1

End Sub

Reply | Quote

The code should not be in the On Load event of the form. Please read the thread I pointed to again.

Reply | Quote

The code should not be in the On Load event of the form. Please read the thread I pointed to again.

I read the whole thread, if I use the before update or On load event the code changes the field value Accordantly. I am trying to make the code loop thru the selected records in the subform and change the field values, to reflect a sequencial order. I placed the code in the On current event and it changes the first record in the required field to “1”, but is no changing the other records field value; I want the code to change the next records as well.

If I select the next record on the subform, the code executes and changes the next record field value to “2” because of the On current event..which is perfect..:-); I am trying to emulate that process automatic with a Do while loop statement but is not working.

Private Sub Form_Current()

Do

Do While (Me.VCHR_LN_NO) = 0
Me.VCHR_LN_NO = Nz(DMax(“VCHR_LN_NO”, “VCHR_LN”, “AutoID =” & Me.AutoID), 0) + 1

Exit Do
Loop

Loop Until (Me.VCHR_LN_NO) 0

Exit Sub

End Sub

Reply | Quote

You should NOT do it this way. The Link Master Fields and Link Child Fields properties of each subform should be set so that the appropriate field will be filled in automatically, and code in the Before Insert of Before Update event to fill in the sequence number.

Reply | Quote

You should NOT do it this way. The Link Master Fields and Link Child Fields properties of each subform should be set so that the appropriate field will be filled in automatically, and code in the Before Insert of Before Update event to fill in the sequence number.

HansV, I have being trying to get this done but cant see to find the solution. I came up with the below event procedures to change all records in the field in a sequencial order.The field default value is “0” when it gets created from the queries and added into the table that keep the data with required field format and information. By doing this, I only can change 3 records automatically to show a sequence of (1,2,3)in the field.

Private Sub Form_Current()

If (Me.SUB_LN_NO) = 0 Then
Me.SUB_LN_NO = Nz(DMax(“SUB_LN_NO”, “VCHR_LAB_VEND”, “REF1_ID =” & Me.REF1_ID), 0) + 1
End If

End Sub

Private Sub Form_AfterUpdate()

RunCommand acCmdRecordsGoToNext
If (Me.SUB_LN_NO) = 0 Then
Me.SUB_LN_NO = Nz(DMax(“SUB_LN_NO”, “VCHR_LAB_VEND”, “REF1_ID =” & Me.REF1_ID), 0) + 1
End If

End Sub

Private Sub Form_AfterInsert()

RunCommand acCmdRecordsGoToNext
If (Me.SUB_LN_NO) = 0 Then
Me.SUB_LN_NO = Nz(DMax(“SUB_LN_NO”, “VCHR_LAB_VEND”, “REF1_ID =” & Me.REF1_ID), 0) + 1
End If

End Sub

I was trying to create a loop to do this for me and if I have more than 3 records to change; but is not working for some reason.

Dim dbs As DAO.Database
Dim rs As DAO.Recordset

Set dbs = CurrentDb
Set rs = dbs.OpenRecordset(“VCHR_LAB_VEND”, dbOpenDynaset)

Do While Not rs.EOF
If (Me.SUB_LN_NO) = 0 Then
Me.SUB_LN_NO = Nz(DMax(“SUB_LN_NO”, “VCHR_LAB_VEND”, “REF1_ID =” & Me.REF1_ID), 0) + 1
End If
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set dbs = Nothing

Reply | Quote

I am very sorry; I’m not able to get my point across. I hope that someone else will be able to help you.

Reply | Quote

I am very sorry; I’m not able to get my point across. I hope that someone else will be able to help you.

No worries, HansV!! I know i am not getting my point across; I was able to emulate the thread you suggested. It creates the sequence as new files are created, not when the files are already existing in the table. You’re still the greatest guru…:-)

Reply | Quote

You shouldn’t use form code to add sequence numbers to already existing records, but either create an update query to fill in the field, or write code that loops through the records in the appropriate table(s) and updates the records.

Reply | Quote

Pictures are good, but when should the sequencing be done,
and in what order.

Is it when a parent changes or a child.
Is it when a New child record is added, OR after a change.

All pretty relevant details.

However you write the sequence numbers against the child records, the sequence will be based
upon the order that the rows are retrieved from the child recordset.

Also even if looping, what would be the key field that links parent to child, OR
are there 2 key fields etc.

None of that is obvious from your pictures.

You just need to explain how the sequence relates from parent to child,
and what the relevant triggers are.

Reply | Quote

It’s like 25 or 6 to 4…

Funny you should mention that, a few weeks ago I converted ’Transit Authority’ from Vinyl to MP3.

Reply | Quote

You shouldn’t use form code to add sequence numbers to already existing records, but either create an update query to fill in the field, or write code that loops through the records in the appropriate table(s) and updates the records.

Pictures are good, but when should the sequencing be done,
and in what order.

Is it when a parent changes or a child.
Is it when a New child record is added, OR after a change.

All pretty relevant details.

However you write the sequence numbers against the child records, the sequence will be based
upon the order that the rows are retrieved from the child recordset.

Also even if looping, what would be the key field that links parent to child, OR
are there 2 key fields etc.

None of that is obvious from your pictures.

You just need to explain how the sequence relates from parent to child,
and what the relevant triggers are.

Hey, guys! I really appreciate all ya do…thanks..:-)

Dont know if ya were able to open the database sample that I posted with the images. but it does make sense to create an update query to create the senquency by unique master and child id records. If you get a change to open the database sample; the records in tables VCHR_HDR, VCHR_LN and VCHR_LAB_VEND are inserted by the respective append queries (appVCHR_HDR, etc). The Form frmExport will retrieve the data according to the date criterias; when this data is uploading it has to be already in the sequencial order by their respective ID. The master and child link for the first and second sub form is the autoID field; the second and third sub form are link thru the REF1_ID field. The fields that I’m trying to create the sequence are not primary keys or used as link fields; i just need to be able to create the sequence in accordance with the previous mention linking fields.

In the main form I have a button that executes the append queries to create the data for the tables mentioned previously; then the subforms as they load are retrieving the data from the tables according to their parent to child link. So, my problem or what I am trying to achieve is, as they doing that or before the data is fully display. I want to sequence these two fields on each second and third subforms as you see in the images posted. Hope this help..Thank again guys!!

Reply | Quote

Hey, guys! I really appreciate all ya do…thanks..:-)

Dont know if ya were able to open the database sample that I posted with the images. but it does make sense to create an update query to create the senquency by unique master and child id records. If you get a change to open the database sample; the records in tables VCHR_HDR, VCHR_LN and VCHR_LAB_VEND are inserted by the respective append queries (appVCHR_HDR, etc). The Form frmExport will retrieve the data according to the date criterias; when this data is uploading it has to be already in the sequencial order by their respective ID. The master and child link for the first and second sub form is the autoID field; the second and third sub form are link thru the REF1_ID field. The fields that I’m trying to create the sequence are not primary keys or used as link fields; i just need to be able to create the sequence in accordance with the previous mention linking fields.

In the main form I have a button that executes the append queries to create the data for the tables mentioned previously; then the subforms as they load are retrieving the data from the tables according to their parent to child link. So, my problem or what I am trying to achieve is, as they doing that or before the data is fully display. I want to sequence these two fields on each second and third subforms as you see in the images posted. Hope this help..Thank again guys!!

Hadn’t got the database then, but have now.
So best idea would probably be to have code behind the parent load button,
that builds all the sequences into the child tables,
before they are loaded when the export button is clicked.
Then at the end of that process instruct access to load the subforms rather than getting them loaded twice and then having to requery them.
I’ll have a look.

Reply | Quote

I’ll have a look.

Had look, but it’s pretty bust.
The subforms and the combo’s reference the fields on the main form, which seem to reference the fields on the subforms.
It errors with a parameter as soon as it is loaded.
So half the combos never get populated.
Also Access loads subform data before it loads the main form data.

Final point, it is not clear in each temporary table, which field you are trying to sequence,

I have some code that does some sequencing against the button press, by looping through the tables.
This is shown below.
But I really an not quite sure how and what you are trying to update and when.
You need to give a step by step process required, because its a bit confused.

Also, a lot of the stuff on the forms does not appear to work.

Anyway

Example Loop that might well be of no use at all

Code:

Private Sub cmdCreateHeader_Click()

Dim rstHdr As DAO.Recordset, strSQLH As String
Dim rstLN As DAO.Recordset, strSQLLN As String
Dim rstLAB As DAO.Recordset, strSQLLAB As String
Dim lngSEQLN As Long, lngSEQLAB As Long

lngSEQLN = 0
lngSEQLAB = 0

DoCmd.SetWarnings False

‘Build the Data for the Subforms these don’t work at the moment because
‘the combo boxes don’t work correctly at the top, but if they did….
‘DoCmd.OpenQuery “appVCHR_HDR”
‘DoCmd.OpenQuery “appVCHR_LN”
‘DoCmd.OpenQuery “appVCHR_LAB”

‘Now Run Updates to the Sequencing
strSQLH = “Select AutoID From VCHR_HDR”
Set rstHdr = CurrentDb.OpenRecordset(strSQLH)
Do Until rstHdr.EOF
    ‘Now Loop Through the LN Table
    strSQLLN = “SELECT * FROM VCHR_LN WHERE AutoID=” & rstHdr!AutoID
    Set rstLN = CurrentDb.OpenRecordset(strSQLLN)
    lngSEQLN = 0
    Do Until rstLN.EOF
        lngSEQLN = lngSEQLN + 1
        rstLN.Edit
            rstLN!VCHR_LN_NO = lngSEQLN
        rstLN.Update
        ‘Now Get the Data for the LAB
        strSQLLAB = “SELECT * FROM VCHR_LAB_VEND WHERE REF1_ID=” & rstLN!REF1_ID
        Set rstLAB = CurrentDb.OpenRecordset(strSQLLAB)
        lngSEQLAB = 0
        Do Until rstLAB.EOF
            lngSEQLAB = lngSEQLAB + 1
            rstLAB.Edit
                rstLAB!VCHR_LN_NO = lngSEQLN
                rstLAB!SUB_LN_NO = lngSEQLAB
            rstLAB.Update
            rstLAB.MoveNext
        Loop
        rstLN.MoveNext
    Loop
    rstHdr.MoveNext
Loop

rstLAB.Close
rstLN.Close
rstHdr.Close

Set rstLAB = Nothing
Set rstLN = Nothing
Set rstHdr = Nothing

Me!qryExport.Requery
Me!qryExport.Form![qryVCHR_LN subform].Requery


‘This last line may well not work it should in principle but it failed for me
Forms!frmExport![qryExport].Form![qryVCHR_LN subform].Form![qryVCHR_LAB subform].Requery

DoCmd.SetWarnings True

Reply | Quote