Results 1 to 15 of 31
Thread: Number Log (2003)

20080229, 11:52 #1
 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 XRay 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 reset 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.

20080229, 11:58 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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?

20080229, 12:06 #3
 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 2535, would be 36. So if we had 10 more parts with the same part number to identify this month, they would be DB 3646. 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.

20080229, 12:17 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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"?

20080229, 12:25 #5
 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 2535, but as a last resort, I suppose they could be split up into a Year/Month field (DB), Start Field (25), End Field (35).

20080229, 12:36 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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.

20080229, 12:42 #7
 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.

20080229, 12:50 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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.

20080229, 17:15 #9
 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'

20080229, 17:22 #10
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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".

20080303, 11:20 #11
 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?

20080303, 11:58 #12
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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)

20080303, 12:22 #13
 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

20080303, 12:35 #14
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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.

20080702, 01:13 #15
 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?