Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts
    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...:-)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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.

  3. #3
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='800369' date='28-Oct-2009 20:00']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.[/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 I perform your recommendation in a form.

  4. #4
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    [quote name='Lastcall' post='800372' date='28-Oct-2009 19:07']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.[/quote]

    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.
    Andrew

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='AndrewKKWalker' post='800373' date='28-Oct-2009 20:11']Your 1-4 0r 6 was a bit confusing to follow.[/quote]
    It's like 25 or 6 to 4...

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    You, sir, are a wealth of knowledge, including but not limited to old rock bands!
    Wendell

  7. #7
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Post

    [quote name='HansV' post='800374' date='28-Oct-2009 20:14']It's like 25 or 6 to 4... [/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..:-)

  8. #8
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    [quote name='Lastcall' post='800389' date='28-Oct-2009 20:16']:-) 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..:-)[/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.
    Andrew

  9. #9
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    [quote name='HansV' post='800374' date='28-Oct-2009 19:14']It's like 25 or 6 to 4... [/quote]
    Funny you should mention that, a few weeks ago I converted 'Transit Authority' from Vinyl to MP3.
    Andrew

  10. #10
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by AndrewKKWalker View Post
    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.
    Attached Images Attached Images
    Attached Files Attached Files

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The thread Item Number (2003) should give you some ideas.


  12. #12
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    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

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The code should not be in the On Load event of the form. Please read the thread I pointed to again.


  14. #14
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    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

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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.


Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •