如何从现有数据中获取字母表中的下一个字母？ [英] How do you obtain the next letters up in the alphabet from existing data?

问题描述

我在车辆表中有一个字段，它是一个由3位数字和2个字母组成的唯一代码。所以看起来像这样： -

例如。

003AA

003AB

003AC

021JA

021JB

021JC


我有另一张表，其中包含我最终要添加到第一个的新记录表，但第二个表中的字段仅包含此唯一代码的3位数字部分。所以第二张表看起来像这样： -


Code，Make，Model

003，AUDI，A4 TDI QUATTRO SPORT

003，奥迪，A4 CABRIOLET

021，FORD，SIERRA LX

021，FORD，SIERRA GLX AUTO


等。 。


我想创建一个查询或者其他东西来搜索第一个表中第二个表上的代码以及我找到3位数字的位置（例如003），我想要找到我可以使用的下一个唯一代码，该代码尚未使用。所以我希望它为上面例子的第一辆车找到新的代码003AD，因为003AA，003AB和003AC都已经用于其他车辆了。


我不知道如何自动完成此操作，但我厌倦了这样做作为手动练习。


我真的很感激任何帮助或想法...谢谢

I have a field in a vehicle table that is a unique code consisting of a 3 digit number and 2 letters. So it looks like this:-
eg.
003AA
003AB
003AC
021JA
021JB
021JC

I have another table that holds new records that I eventually want to add to the first table but the field in the second table only contains the 3 digit number part of this unique code. So the second table looks like this:-

Code, Make, Model
003, AUDI, A4 TDI QUATTRO SPORT
003, AUDI, A4 CABRIOLET
021, FORD, SIERRA LX
021, FORD, SIERRA GLX AUTO

etc...

I want to create a query or something to search the first table for the code on the second table and where I find the 3 digit number (eg. 003), I want to find the next unique code up that I can use, that is not already in use. So I would want it to find new code 003AD for the first vehicle of the example above, because 003AA, 003AB and 003AC are all already in use for other vehicles.

I have no idea how to do this automatically but I am tired of doing this as a manual exercise.

I would really appreciate any help or ideas... Thank you

推荐答案

我觉得有一种方法可以用字符代码来做到这一点，但我无法想象如何。


快速解决方法是简单地创建一个参考表，其中每个字母都有一个数值。捕获字符串的最后一个字母，查找它的数值，添加一个，然后查找相关的字母。


或者那种性质的东西，但是你得到了一般的想法

I feel like there''s a way to do this with character codes, but I can''t think of how.

A quick work around would be to simply create a reference table where each letter has a numeric value. Capture the last letter of the string, look up it''s numeric value,add one, and then lookup the related letter.

Or something of that nature, but you get the general idea

我在外出之前把它扔在一起，但它至少应该给出你是一个参考的起点。只需将要为其生成下一个顺序代码的3位数基本代码传递给功能，该代码基于这些3位数。我列出了一些示例数据和结果，但首先是几个假设。

I literally threw this together before going out, but it should at least give you a starting point to reference. Simply pass to the Function a 3-Digit Base Code for which you want generate the next, sequential Code based on those 3-Digits. I listed some Sample Data along with Results, but first a few Assumptions.

1. 您的表名是tblCodes，由一个名为[Code2]（TEXT）的字段组成。此表中的任何其他字段都无关紧要。
2. 代码始终遵循相同的格式（3位，2个字符串），并且本质上是顺序的，如：045AA，045AB，045AC，045AD，076AA ，076AB等。
3. 你永远不会有3个数字后跟一个双''Z'的情况，即XXXZZ。如果你愿意的话，代码将不得不被修改。 ，它允许后端有一个Z，并进行适当的调整。
4. 功能定义：
   

1. Your Table name is tblCodes and consists of a Field named [Code2](TEXT}. Any other Fields in this Table are irrelevant.
2. The Code always follows the same exact Format (3-Digits, 2 Strings), and is sequential in nature as in: 045AA, 045AB, 045AC, 045AD, 076AA, 076AB, etc.
3. You will never have a situation where 3 Digits would be followed by a Double ''Z'', namely XXXZZ. If you would, the code will have to be modified. For now, it allows for a ''Z'' on the back end, and makes the proper adjustments.
4. Function Definition:
   

展开 | 选择 | Wrap | 行号

非常感谢 - 看起来很棒。


我想我明白它在做什么。


唯一的事情就是这段代码在开始编译时没有编译将dim mydb as dao.database设置为dim mydb as dao.database。因为我不认为我有数据库的DAO选项吗？


它说它无法识别用户定义的类型数据库。


如何定义数据库？

非常感谢您的帮助。

Hi ,

Thank you very much - that looks brilliant.

I think I understand what it is doing.

The only thing is that this code doesn''t compile at the beginning when it sets "dim mydb as dao.database" because I don''t think I have the DAO option of Database perhaps?

It is saying it does not recognise the user defined type "database".

How do I define the database?

Thank you very much for your help.

这篇关于如何从现有数据中获取字母表中的下一个字母？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！