Page 1 of 3 123 LastLast
Results 1 to 15 of 38
  1. #1
    bdhFS is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    May 2010
    Posts
    32

    Crosstab Query Help (Create a record number that restarts)

    Hi! I normalized some tables into a one-to-many relationship...tblFrgCaptures to many tblFrgMarks (each captured frog can have many ink marks). Well now I need to output the data so that tblFrgCaptures show the tblFrgMarks as one row of data. (Crosstab). However, I don't have any data to display for the column headers in the tblFrgMarks. What I was wondering is there an easy way to create a "record" order number for each UniqueMRId in tblFrgMarks that restarts on 1 again when that number changes? Example below. Thanks for the help!!!
    UniqueMRId InkMark What I would like to generate is this kind of numbering system
    2654 LR-P 1
    2655 LR-G 1
    2656 RR-P 1
    2656 LR-P 2
    2657 RR-P 1
    2657 RF-G 2
    2658 RR-K 1
    2658 RF-G 2
    2660 LF-G 1
    2660 RF-G 2
    2661 LR-P 1
    2661 LF-G 2
    2661 RF-G 3
    2662 RF-G 1
    2663 RF-G 1
    2663 RR-K 2
    2664 RR-Y 1


  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    There is a way to do that with VBA code but don't think could build CROSSTAB from that. http://allenbrowne.com/ranking.html

    Then there is this http://allenbrowne.com/func-concat.html

    Otherwise, need to actually populate a field in table. VBA code could accomplish that.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    alcapps is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jan 2012
    Posts
    292
    Can you mock up a sample output of what you think the cross tab would look like?

  4. #4
    alcapps is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jan 2012
    Posts
    292
    Really no sure of what you are asking at this point. If you are trying to get the next seq no. Query with uniquemrid count + 1 gets the next number.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    al, think OP wants a sequence number calculated for each UniqueMRld group and then a CROSSTAB of that, lilke:

    UniqueMRld 1 2 3
    2654 LR-P
    2655 LR-G
    2656 RR-P LR-P
    2657 RR-P RF-G
    2658 RR-K RF-G
    2660 LF-G RF-G
    2661 LR-P LF-G RF-G
    2662 RF-G
    2663 RF-G RR-K
    2664 RR-Y
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    alcapps is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jan 2012
    Posts
    292
    TRANSFORM First(Table1.[inkmark]) AS FirstOfinkmark
    SELECT Table1.[ID]
    FROM Table1
    GROUP BY Table1.[ID]
    PIVOT Table1.[num];

    ID
    1
    2
    3
    2654
    LR-P

    2655
    LR-G

    2656
    RR-P LR-P
    2657
    RR-P RF-G
    2658
    RR-K RF-G
    2660
    LF-G RF-G
    2661
    LR-P LF-G RF-G
    2662
    RF-G

    2663
    RF-G RR-K
    2664
    RR-Y


  7. #7
    alcapps is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jan 2012
    Posts
    292
    june7.. that would be too easy I am sure. thanks for laying out what he needed.

    or we think he needs.

    Also just change the name of the table1 to your table..

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The problem is sequence number field doesn't exist in table. OP is asking how to calculate that sequence in query and then to pivot that.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    alcapps is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jan 2012
    Posts
    292
    see I told you that was too easy.

  10. #10
    alcapps is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jan 2012
    Posts
    292
    I think the answer is I think I told him before.

    on before insert 'New Records
    =nz(dcount("ID", "table1", "[ID] = " & me.ID) + 1,0)

    then use the cross tab query I already did.

    and let me quess.. he won't know how to keep the ID going or repeat as well

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Your suggestion will help for new records but my impression is the table already has records so question is how to handle that. If the query with VBA procedure will not work with CROSSTAB then will have to update the records.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    alcapps is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jan 2012
    Posts
    292
    call this sub routine like this
    AssignRowNum "table1"
    replace table1 with your table name.
    if the num column is null it will pick it up and try to put the pattern numbers in. Then run the cross tab query already defined and we should be ok.
    Code:
    Public Sub AssignRowNum(datTableName As String)
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim cnt As Long
    Dim lastID As Long
    
    Set db = CurrentDb()
    
    strSQL = "Select * from " & datTableName & " where num is NULL order by ID, inkmark"
    
    Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
    
    If rst.RecordCount > 0 Then
       rst.MoveFirst
       lastID = rst!ID
       cnt = 0
       Do While Not rst.EOF
          If lastID = rst!ID Then
             cnt = cnt + 1
             rst.Edit
             rst!num = cnt
             rst.Update
          Else
             cnt = 1
             rst.Edit
             rst!num = cnt
             rst.Update
             lastID = rst!ID
          End If
          rst.MoveNext
       Loop
    End If
    End Sub
    Last edited by June7; 01-28-2013 at 12:34 PM.

  13. #13
    alcapps is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jan 2012
    Posts
    292
    sorry my last post didn't format the way I indent my code.

  14. #14
    bdhFS is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    May 2010
    Posts
    32
    Okay, just got back to this after the weekend and June7 hit the nail on the head as far as what I am looking to do. I need to look at the previous posts to try and work through your suggestions. I will get back to you soon.


    UniqueMRld 1 2 3
    2654 LR-P
    2655 LR-G
    2656 RR-P LR-P
    2657 RR-P RF-G
    2658 RR-K RF-G
    2660 LF-G RF-G
    2661 LR-P LF-G RF-G
    2662 RF-G
    2663 RF-G RR-K
    2664 RR-Y

  15. #15
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    al, I edited your post to show indentation. Use code tags when copy/paste code and indentation will be retained.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 12-04-2012, 02:46 AM
  2. Use VBA to edit record or create new record in a query
    By ryantam626 in forum Programming
    Replies: 11
    Last Post: 08-09-2012, 02:37 AM
  3. Replies: 2
    Last Post: 06-06-2012, 09:35 AM
  4. Replies: 1
    Last Post: 03-08-2012, 09:17 AM
  5. Report Restarts Itself And Never Completes
    By VariableZ in forum Reports
    Replies: 10
    Last Post: 03-01-2012, 10:47 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums