Extracting a Date Code

[LBPete]

Hi, I'm new to this forum. I'm not sure if this question should be in Programing or Queries. It involves both. I have a table that contains serial numbers. The serial numbers include a three digit production date code that i need to convert to a Month, Year calendar date.

Most of the serial numbers are 9 digits with the third digit representing the year and the 4th and 5th digits representing the month. xx507xxxx for example would indicate July, 1975 production for a given model.



To make it more complected, depending on the model, the single digit year could one of several decades ranging from 1950 through 1990. To define the year, I have a table that contains a decade reference based on model.

I'm struggling with the coding to reference the decade to determine the year. In some cases the production run of a particular model crossed a decade. For example, 1968 to 1971. The year code for this example could b an 8, 9, 0 or 1, indicating 1968, 1969, 1970 or 1971.

Some of the serial numbers are only 8 digits with the second digit indicating the year and the 3 and 4 digits indicating the month. x605xxxx indicating May, 1966 for a given model.

I'm reasonably proficient with quires but need help with this coding. Thanks in advance.

- Pete

[jwhite]

> xx507xxxx for example would indicate July, 1975 production for a given model

How do you derive 1975 from that??? Being single digit, it could be ANY year ending in 5.

[LBPete]

The line of data that contains the serial also identifies the model. I have a decade reference tied to the model. So for that example the model reference would indicate it was manufactured in the 1970s then the single digit year code would indicate 1975.

- Pete

[andy49]

I think some examples of data from relevant tables might help. Include examples of all the various issues and we'll take a look


Sent from my iPhone using Tapatalk

[LBPete]

Model	Serial Number	Production Date	Decade	Entry Date	Reporter	ID	Notes
1000A	118062907	June, 1968	1966	12/30/05	Import from AK 2005-12-30	492

Here's a line of data from the table "Master List". I would like the production date to be automatically generated rather than manual entry. The more popular models can have a hundred or more lines of data each with a unique serial number. Rare models may only have a single line of data.

The serial number is a unique number so I use that as the primary key in this table. I have another table that lists the model, decade and type of equipment.

Model	Equipment Type	Decade	ID	F5	F6	F7
1000A	Receiver	1966	492

Does this help?

- Pete

[Micron]

The logic might be that
- if the number is 9 digits, determine the 3rd, 4th and 5th digits. Where possible, string together the date parts using those values. For the decade part, lookup the value based on the model.
- if the model is 8 digits in length, use the 2nd, 3rd and 4th
However, I don't quite understand the part about a model going over a decade. This suggests there could be a span of 2 or more years in any decade for a model run without spanning decades, so I don't grasp the foundation of the decade table. If that is the case, then how would you ever choose a year for a model run? You'd need some kind of ascending sort and get the Max of it, then the decade value from that row? A bit fuzzy.

[LBPete]

The production for a model can run several years. In some cases it starts in one decade, say 1968 and continues into the next, say 1971. In this case, the serial number year digit could be an 8, 9, 0 or 1. If it is an 8 or a 9, it would indicate 1968 and 1969 respectively. If the serial number date were a 0 or a 1, it would indicate 1970 or 1971 respectively. No model was in production for more than 10 years so none of the models spans more than two decades.

I can create a reference table that lists all the models and the production range for each but there should be some way to do it with just a decade reference using a greater than/less than argument.

- Pete

[Micron]

I can create a reference table that lists all the models and the production range for each but there should be some way to do it with just a decade reference

If that reference is not to be a table, then I have no idea how you might intend to distinguish 1951 from 1961 or 1971 when presented with 1 as your single digit model decade reference.

[LBPete]

A table may be the easiest way to do it but there might be a way to do it with just the decade field as a reference. The value in the decade field could be set to a year lower than the first year of production. So if production started in 1968 and continued into 1971, the value in the Decade field would be set to 1966. Then you can use an if/then statement.

I don't know how hard this would be to write in code that Access would understand but it would go something like this: If the third digit in the serial number is greater than the 4th digit in the decade field then the year value is the first three digits of the decade field with the third digit of the serial number field as the forth digit. If the third digit in the serial number field is less than the 4th digit in the decade field, than the value is the first three digits of the decade field with the third digit of the serial number field + 10.

Can a version of that statement be written so Access can perform the calculation?

- Pete

[Micron]

The short answer, with a high degree of confidence, would be yes. However, I still don't know where the decade reference is coming from. It seems you have a table for that after all, which was part of my confusion since in one post you seemed to say you have it, and in another, you don't want it. So without a clearer understanding of this relationship, I wouldn't attempt it myself since I don't understand how you get 1968 from 118062907. Based on the limited samples you've shown, and having not shown this decade table, you can't expect me to know why this isn't 1958 or 1978 or whatever.

I don't like looonggg expressions with multiple nested IIF's. I prefer procedures for such complicated decisions. So from what I can glean from the discussion, the solution would involve the use of Len, Left and probably Mid functions, and maybe CDate if you want the result to be of the date data type. Using your field references, maybe something like this

Select Case Len(serial)
  Case Is = 9 'for a 9 digit serial...
  'If the third digit in the serial number is greater than the 4th digit in the decade field then the year value
  'is the first three digits of the decade field with the third digit of the serial number field as the forth digit.
  If Mid(serial,3,1) > Mid(decade,4,1) Then strYear = Left(decade,3) & Mid(serial,3,1)

  'If the third digit in the serial number field is less than the 4th digit in the decade field, then the year value 
  'is the first three digits of the decade field with the third digit of the serial number field + 10.
  If Mid(serial,3,1) < Mid(decade,4,1) Then strYear = Left(decade,3) & (Mid(serial,3,1)+10)

  'If the 3rd digit is equal to the 4th digit??

  Case Is = 8
  repeat for 8

  Case Else
  'there is a problem with the serial number length?

End Select

I presume the data type would work with just adding 10 without needing to coerce data types. The sample code doesn't deal with declaring variables, for which I don't quite understand the source of anyway. Hope that gets you where you need to go.

[LBPete]

The Decade reference is a field in the serial number master table. It's in each line of data. I posted representative data lines earlier. I'll see if I can edit that post to better identify the table names. Each line of data in the master serial number table has the decade reference. Currently there is no separate "decade" table but I can create one that references the first and last production of each model if that will simplify the date extractions. There are over 600 models on file so it would be a significant task to populate production ranges into that table

The decade reference comes from historical data regarding the model production. It's also a field in a separate table that just has model information. I posted a representative data line above. Right now I'm doing the date conversions manually referencing the decade. There are over 6,000 lines of data in the master serial number table and around 75 new serial number are added each month.

I'm trying to automate the data extraction but don't know how. It's beyond my abilities so I'm totally open to suggestions on the best/easiest/most elegant to do it and truly appreciate your help. Please bear with me as I fumble through this.

As a little background, all these serial numbers come from members of an organization called AudioKarma.org. Here's a LINK if you want to take a look. The serial numbers were all posted by members to document the actual production of classic Sansui audio equipment. The original database was set up around 2002 and was maintained through about 2009 when the member that created it retired from the organization. A few months ago I picked it up. I have the old data as an excel file but none of the old coding. This is all volunteer work. I'm not being paid.

- Pete

[LBPete]

Here's a larger data sample from the Serial Number Master Table. What I'm trying to do with this is generate the production date based on the serial number field and the decade field. If this were excel, you would put the appropriate coding in the production date field. That field would do the calculation and display the result. I assume access will do it as well.

Model	Serial Number	Production Date	Decade	Entry Date	Reporter	ID	Notes
2000A	011010309	January, 1971	1969	6/18/05	Import from AK 2005-06-18	331
2000A	011030037	March, 1971	1969	8/30/04	Import ftom AK	331
2000A	011031092	March, 1971	1969	5/22/04		331
2000A	011090656	September, 1971	1969	1/3/04		331
2000A	810013157	January, 1970	1969	1/13/04		331
2000A	810030966	March, 1970	1969	4/30/05	Import from AK 2005-04-30	331
2000A	810090794	September, 1970	1969	4/29/07		331
2000A	810092037	September, 1970	1969	8/30/04	Import ftom AK	331
2000A	810093565	September, 1970	1969	6/22/04		331
2000A	810101788	October, 1970	1969	5/15/05	Import from AK 2005-05-15	331
2000A	810102451	October, 1970	1969	6/21/05	Import from AK 2005-06-21	331
2000A	810102963	October, 1970	1969	1/30/06	Import from AK 2006-01-30	331
2000A	819110090	November, 1969	1969	2/18/06	Import from AK 2006-02-18	331
2000A	820031158	March, 1970	1969	2/6/04		331
2000A	820031638	March, 1970	1969	2/4/17		331
2000A	820031682	March, 1970	1969	8/28/05	Import from AK 2005-08-28	331
2000A	820050522	May, 1970	1969	5/8/05	Import from AK 2005-05-08	331
2000A	820062769	June, 1970	1969	12/30/05	Import from AK 2005-12-30	331
2000A	829110997	November, 1969	1969	3/22/17		331
210	013030003	March, 1973	1970	12/11/07	Import from AK 2007-12-11	333
210	022070259	July, 1972	1970	3/25/05	Import ftom AK	333
210	022070702	July, 1972	1970	1/30/06	Import from AK 2006-01-30	333
210	213020034	February, 1973	1970	6/14/06	Import from AK 2006-06-14	333
210	223071296	July, 1973	1970	6/21/05	Import from AK 2005-06-21	333
210	233041098	April, 1973	1970	10/10/05		333
210	811080160	August, 1971	1970	8/11/06	Import from AK 2006-08-11	333
210	814010655	January, 1974	1970	2/6/04		333
210	814020080	February, 1974	1970	4/29/07		333
210	822070617	July, 1972	1970	7/13/06	Import from AK 2006-07-13	333
210	224060949	June, 1974	1970	9/18/05		333
5900Z	811100142	October, 1981	1980
5900Z	811110142	November, 1981	1980	3/30/07		565
600L	240020607	February, 1970	1969	5/10/05	Import from AK 2005-05-10	###
600L	240090023	September, 1970	1969	7/25/04		###
6060	026081159	August, 1976	1970	7/1/07	Import from AK 2007-07-01	349
6060	026082681	August, 1976	1970	6/7/04		349
6060	027120190	December, 1977	1970	12/8/06		349
6060	027120292	December, 1977	1970	11/11/07		349
6060	037010899	January, 1977	1970	11/11/07		349
6060	857040443	April, 1977	1970	4/30/17
6060	858020705	February, 1978	1970	10/30/05		349

[orange]

Pete,

I don't know if the following info is relevant to your particular issue, however, I hope it is useful -even if only as a reference.
I often see posters trying to create("concoct") codes to simplify or save space or numerous other rationalizations.
I do understand that some codes make sense, so this is not to say you should NOT use or create codes. It seems in your case the codification was done by a programmer/designer who has since left the organization. It also seems there is no documented technique to identify/interpret each part of the code with ambiguity.

As you probably know, relational databases work quite well when structured to use 1 fact in 1 field. Then use some designed algorithm to concatenate pieces together for some purpose/presentation.

The attached shows the detail in the design of a standard codification. Yours may very well have been designed with similar detail. If it was then there must/should be some reference material.

Good luck with the project.

[Micron]

LBPete; I think you have shed more light on the issue and I didn't realize 1968 was a decade reference, so I think you mean the second example in post 5 is the table you've been referring to. I will take a look at your new data sample (and Orange's post ) later tonight or tomorrow if you can wait. I have company coming for dinner, and guess who has to cook it? And yes, I did bake a 3 layer cake too!

P.S. I've been thwarted by a few issues of cross posting lately, so if you have posted this issue elsewhere, please declare it by providing a link to it in this thread. That is the expected practice. I only ask because I wouldn't blame you for doing so given the length of time this is taking.

[LBPete]

Orange, I'm very familiar with VIN numbers from my professional life. The digits in VINs are very well defined and universally used. There are similarities between VINs and these Sansui serial numbers but with VINs, they use an alphanumeric character for the single digit year and managed to span 30 years without repeating a digit. Since they have started repeating to decode it they would now need a decade reference too.

When I took over the moderation of this database all I got was a couple of excel files with table data. No coding or design data. The previous moderator did have this function automated but for whatever reason he has been unable or unwilling to supply that information.

Micron, there is no deadline for achieving this. I update the master table monthly and that's when I assign the production dates.

- Pete