Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2004

    Unanswered: Increment a Number Based on Date

    Incremental Numbering - Based on Date
    Question: I am trying to develop a numbering scheme as follows

    Number the entries entered into the database incrementally by day

    the fields I will use are FunctionDate and FunctionNo

    I want the numbering to look like below - starting at 1 for each day

    date function name functionno

    1/1/07 wedding 1
    1/1/07 party 2
    1/1/07 meeting 3
    1/3/07 party 1
    1/3/07 name here 2
    1/5/07 party 1
    1/5/07 party 2
    1/5/07 meeting 3
    1/5/07 wedding 4
    1/7/07 party 1

    what might be some ways to do this?

    another thought is this - what if i have functions 1 thru 5 for a given day, and function 2 is deleted, or changed to a diff date - that will of course throw off the numbering scheme.... 1-2-3-4-5 will be 1-3-4-5

    I thought of counting in a query - but the counter changes depending on how records may be sorted....

    also, if I use dMAx() - how do i incorporate the functiondate?

    Any ideas are appreciated.

    thanks! - AB

  2. #2
    Join Date
    Oct 2002
    Baghdad, Iraq
    Quote Originally Posted by abinboston
    Number the entries entered into the database incrementally by day


    I thought of counting in a query - but the counter changes depending on how records may be sorted....
    So within a particular day they're numbered by order of entry into the database? I'm not clear as to why you don't want to simply sort by time of day, but it's your data...

    Anyway, either use the time of the event or add another field to store the record creation (or update, if you prefer) date/time with a default of NOW().

    Now you have a reliable way to sort your data. Call it the Time column.

    There isn't any straightforward way that I know of to determine whether a value is 1st, 2nd, 3rd in some group. Most functions available to queries only "know" about the row in question or, in the case of agreggate functions, the group as a whole.

    On the bright side, you don't really need this number field. A date/time field is a perfectly good value to use as a key, or to join or sort on.

    If you really need to maintain the number field, you need two procedures:

    Add Record should check to see if the new record's time field is greater than any other for that day. If so, it sets the number to 1 + max() of the existing records for that day. If not, it falls through to Renumber Day.

    Renumber Day is used when deleting or updating the time field. It should select all rows for the day sorted by the time column. It then simply iterates through them setting the row number.

    This is a case where SQL Server would be nice to use because you could easily design these as triggered procedures.

  3. #3
    Join Date
    Mar 2007
    Make a tabel with the possible as many records as the maximum number of functions in a day.
    query 1 (get the possible numbers in a certain date as in your form)
    SELECT TScheduleItems.Date, TSeqNo4Combo.SeqNumber
    FROM TScheduleItems, TSeqNo4Combo
    GROUP BY TScheduleItems.Date, TSeqNo4Combo.SeqNumber
    HAVING (((TScheduleItems.Date)=[Forms]![FScheduleItems]![Date]));

    Query2 Get the first still available number (NB the joins)
    SELECT First(Query1.SeqNumber) AS FirstOfSeqNumber
    FROM Query1 LEFT JOIN TScheduleItems ON (Query1.SeqNumber = TScheduleItems.FunctionNo) AND (Query1.Date = TScheduleItems.Date)
    GROUP BY Query1.Date, TScheduleItems.FunctionNo
    HAVING (((Query1.Date)=[Forms]![FScheduleItems]![Date]) AND ((TScheduleItems.FunctionNo) Is Null))
    ORDER BY First(Query1.SeqNumber);
    Here you have the number you are looking for.
    Query2 can be the feed for a combo box. Remember to have it requeried.

    Hope this helps you.


Posting Permissions

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