Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2010

    Unanswered: Sequential number depending on 2 other fields

    I need to add a sequential number depending on 2 other fields
    here are the details. pls help

    Mainform = racesetupf
    subform = racetimingsf
    the table name = racetimingT
    1. racetimingId -- autonumber field
    2. racedate - (only 1x race per day on system and added from mainform with link) --- datefield
    3. racename -- (link master-child field) -- txtfield
    4. finishtime -- (added with now() by entering in form) --- txtfield
    5. LapNo - (can be up to 10 laps per cyclist) --- numberfield
    6. RaceNo - (of the cyclist on his bike, captured up to 10x) -- numberfield
    Basicly, i need a sequential number starting at 1, after every lap per cyclist(raceno) per racedate..

    can something like this work if i get the punctiation right?
    Private Sub Form_BeforeInsert(Cancel As Integer)
    Me.LapNo = Nz(DMax("LapNo", "RacetimingT", "RacetimingID<>" & RacetimingID & " AND RaceNo=" & RaceNo & " AND [Racedate] = #" & RaceDate & "#"), 0) + 1

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    sounds like flaky table design to me
    off the top of my head I'd expect...

    riders..contains details of the rider
    races.. contains detail of the race
    raceriders.. intersection table identifying which riders are entered in which race
    raceridereslaptimes..contains details of the rider in a specific race on a specific lap

    you can derive the finish time as being the sum of the lap times...

    have a look at the following references on normalisation and table design, there are others out there whihc may also be of use
    Fundamentals of Relational Database Design --
    The Relational Data Model, Normalisation and effective Database Design
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

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