Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Number Log (2003)

    A long time ago, I had help with duplicating numbers in a form <post#=550,304>post 550,304</post#>. This prevents the user entering duplicate numbers when releasing parts. This is working excellantly. If the user finds duplicate numbers, he has to tell the machine operators, who gave him the numbers in the first place. This is where the problem lies, they have a paper system which doesn't prevent them from duplicating numbers. I would like to create a system which gives them the numbers automatically, but don't know if this can be done because of the way we identify parts. A part number cannot have duplicate numbers, but a different part range, can have the same numbers as another. So for example :-

    Part Number X-Ray No
    5N111 DB 25 - 35
    1A500 DB 25 - 35

    is ok. The first letter denotes the year, the second the month. This is where things are complicated. When we start a new month, we need to start a new range of numbers. So DC would start at 1. I don't know if this is a possibility, as Autonumber cannot be re-set in a table containing data. I would be greatfull if anyone could give me advice on this, or even suggest other software that would do the job better.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Number Log (2003)

    Please explain more clearly what in a value such as "DB 25 - 35" should change and what should remain constant. Would the next value be "DB 26 - 35" or "DB 25 - 35" or "DB 26 - 36" or something else? What is the logic behind it?

  3. #3
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Number Log (2003)

    The next number in the range DB 25-35, would be 36. So if we had 10 more parts with the same part number to identify this month, they would be DB 36-46. The next lot would start at DB 47 - , and whatever number of parts. DB would remain constant for this month only, regardless of the part number. Hope this is a bit more clear.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Number Log (2003)

    What is the actual value of the field? A single 'number' such as "DB 25", or a range of 'numbers' such as "DB 25 - 35"?

  5. #5
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Number Log (2003)

    At the moment it's a range DB 25-35, but as a last resort, I suppose they could be split up into a Year/Month field (DB), Start Field (25), End Field (35).

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Number Log (2003)

    I would definitely split up the field. That makes it much easier to calculate the next available number.
    I'd use the following fields:
    DateField: Date/Time, default value Date()
    StartValue: Number, (long) integer
    Quantity: Number, (long) integer

    You can calculate the composite number in a query: the two letters can be derived from the date, and the end value can be calculated from the start value and the quantity.

  7. #7
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Number Log (2003)

    Looking at what you've suggested, I don't need to use autonumber do I? I could put a formula in to make the start number 1 if its a new month.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Number Log (2003)

    No, you shouldn't use an AutoNumber. You can create a query that returns the maxim value of the start value for the relevant month and the corresponding quantity. If this query returns no records, use 1 as new start number, otherwise use the sum of the start number and the quantity from the query.

  9. #9
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Number Log (2003)

    I apoligise in advance for being a bit thick, but what do you mean by 'corresponding number'

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Number Log (2003)

    I suggested using a start value field and a quantity field. When you've found the maximum value of the start value for the relevant month, you have to look up the quantity value in the same record to determine the start value for the new record. I meant this quantity when I wrote "the corresponding quantity".

  11. #11
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Number Log (2003)

    I've managed to calculate the month number using MonthNo: DatePart("m",[DateField]), but how do I convert this to a corresponding letter of the alphabet?

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Number Log (2003)

    You can use this:

    Chr(Month([DateField])+64)

    For example, if DateField = 3/3/2008, Month([DateField]) = 3, so Month([DateField])+64 is 67; this is the ASCII code for the letter C.

    Similarly, if 2008 = "D", you can use the following expression to calculate the year letter:

    Chr(Year([DateField])-1940)

  13. #13
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Number Log (2003)

    Is this right? Shouldn't I only have 1 record showing Max Qty
    Attached Files Attached Files

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Number Log (2003)

    I don't know what you want to accomplish with this query, but you're grouping on lots of fields. In a totals query, the combination of all fields on which you group will be unique.

  15. #15
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Number Log (2003)

    Sorry I haven't replied before, but I've just got back on to the project. Do I need to break the query down, having just the MaxofDate, and create and second query incorporating this?

Page 1 of 3 123 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
  •