数据库中的顺序编号 (MSACCESS)
摘要：
我有一个表，它有一个唯一的主键来标识每条记录。 我还有另外三个可以标识记录的字段：
- Category
- CategoryNumber
- DuplicateNumber
当我添加新记录并选择类别时，如何让 CategoryNumber 根据它是否重复而正确递增。
请注意，这是一张大桌子。 这只是从excel到access的升级。
tldr：
昨天我问了如何处理Access中的多个键。 也许我没有问正确的问题，因为我今天的问题更加令人费解。
这就是我正在尝试做的事情以及我陷入困境的地方。
我正在创建一个数据库来跟踪我用不同相机在各种不同胶片和格式上拍摄的胶片照片（每张照片总共有 18 条不同的独立信息），这些照片的时间跨度可以追溯到近 15 年。 几年前，我将数据保存在 MS Excel 中，并对输入表单进行编程以完成我将要解释的操作。
我想以两种方式跟踪照片。 第一个是主照片编号，它可以让我知道我的文件中共有多少张照片。 这是一个连续的数字，我从来没有打算删除该序列中的某些内容。 这将是唯一标识每张照片的数字。 我将其视为整个数据库的主键。
但我还想根据一般组或类别来归档幻灯片、底片和透明胶片，以便在我需要提交照片或正在寻找特定内容时可以快速提取它们。 因此，我还添加了一个类别标识符（例如，风景也附加了“LS”名称，或者日落附加了“SS”名称等）。 我还想为每个类别中的每张幻灯片保留一个连续的序列号。 例如，我的数据库中的第一张照片的 PhotoID = 3，其类别 = LS，类别 ID = 1。再往下，我有 PhotoID = 14，类别 = LS，类别 ID = 2。
现在除了这些之外，我还进一步还有另一个我需要跟踪的数字，那就是我是否对同一场景进行了重复曝光。 为了做到这一点，我只是为每张照片添加了一个 DuplicateNumber。 如果它没有重复项，则 DuplicatNumber 为 0，如果它有一个重复项，则 DuplicateNumber 为 1、2 个重复项，DuplicateNumber 为 2 等等。
因此，我有两种方法可以在文件中查找任何照片。 要么通过 PhotoID，它在数据库中是连续的，但在我的物理文件中不是连续的；或者是通过 Category、CategoryID 和 DuplicateNumber 的组合，这就是我对照片进行物理归档的方式。
现在，在 Excel 中，当我打开表单时，我首先必须通过表单上的单选按钮选择将照片放入哪个类别。 然后我会填写照片的所有相关数据。 当我执行表单时，它会找到主表中的最后一个数字，递增它并将所有数据添加到新记录中。 然后，它将激活我选择的类别的类别表，确定该表中的最后一个数字，递增它并在其中添加新数据。
需要注意的是，默认情况下 DuplicateNumber 始终为 0，除非我将其设置为 1、2 等。它从未被计算过，但我输入了它。
当我创建幻灯片标签（“报告”）时，我会以这种格式（1LS1.0、14LS45.0、15LS45.1）包含主要的 PhotoID、CategoryName、CategoryID 和 DuplicateNumber，以及拍摄的位置和日期。
但现在我不想再使用Excel，并尝试将数据库迁移到MS Access。 所以我有一个名为 PhotoDetails 的主表。
PhotoID 是该表中的主键。 它有一个 PhotoCategory 字段，该字段是一个与另一个名为“类别”的表相关的数字。 它的主键是 PhotoCategoryID，也是 PhotoDetails 中的外键。 PhotoCategoryID 是标识每个类别的数字。 在我的所有类别列表中，LS 排名第 11。但这就是我陷入困境的地方。
在 PhotoDetal 表中，我有 CategoryNumber（标识每个类别中的照片的编号）和 DuplicateNumber（标识重复项的数量）。 我不明白的是，当我添加新照片时，如何增加 CategoryNumber？ 我现在拥有的表单允许我输入所有数据，当我单击输入下一个数据时，它将自动对 PhotoID 键进行编号，但是 CategoryNumber 呢？ 我不记得每个类别的最后一个数字是什么，因此我需要自己输入该数字。 除非我模仿我在 Excel 中所做的操作，将所有数据存储两次，一次存储在 PhotoDetails（Excel 中的主表）中，另一次存储在多个类别表中。 我希望 Access 能为我提供解决方法。
目前，我拥有的关系是以 1-M 关系链接到 PhotoDetails 的类别表。 我还有几个其他表都以 1-M 关系链接（例如，表 Film 存储了我使用过的不同胶片，1-M 关系让我可以将什么胶片链接到每张幻灯片，并且大多数其他数据都存储在该表中）方式）。
我有 31 个不同的类别。 每张照片都有一个类别和一个类别 ID 编号，以及一个重复编号。 我将选择数据输入中的类别和 DuplicateNumber，但我希望 CategoryID 编号以及 PhotoID 能够自动递增。 继续此操作的正确方法是什么，我只需要保留一个 PhotoDetail 表，该表不会像在 Excel 中那样为每个类别重复。
Summary:
I have a table that has a uique primary key identifying each record. I also have three more fields that can identify a record:
- Category
- CategoryNumber
- DuplicateNumber
When I add a new record and choose the Category, how can I get CategoryNumber to increment correctly based on whether it is a duplicate or not.
Note that this is one big table. This is merely an upgrade from excel to access.
tldr:
Yesterday I asked about how to deal with multiple keys in Access. May be I did not ask the right question because my problem is even more puzzling today.
Here is what I am trying to do and where I am getting stuck.
I am creating a database to keep track of my film photos taken on a variety of different films and formats with different cameras (a total of 18 different independent pieces of information for each photo) that span back almost 15 years. Years ago I kept the data in MS Excel and programed the input form to do what I am about to explain.
I wanted to keep track of the photos in two ways. The first was a master photo number that would let me know how many total photos I had in my files. It was a sequentail number that I never intended to ever delete something in that sequence. It would be a number that would uniquely identify each photo. I see this as my primary key for the whole database.
But I also wanted to file the slides, negatives, and transparencies according to general groups or categories so that I could quickly pull them if I needed to make a photo submission or I was looking for something specific. So I also added a category identifer (e.g. Landscapes had a 'LS' name attached as well or Sunsets as 'SS' etc). I also wanted to keep a running sequential number for each slide that was in each category. So e.g. one of my first photos in my data base has PhotoID = 3, its Category = LS and its CategoryID = 1. Further down I have PhotoID = 14, Category = LS and CategoryID = 2.
Now in addition to these, I further have another number that I need to keep track of and that was if I made duplicate exposures of the same scene. To work this I just added a DuplicateNumber to each photo. If it had no duplicates then DuplicatNumber was 0, if it had one dupe than DuplicateNumber was 1, 2 dupes, DuplicateNumber was 2 etc.
So I had two ways for finding any photo in my files. Either by the PhotoID which was sequential in the Database but not in my physical files, or by the combination of Category, CategoryID and DuplicateNumber, which are how I physically file the photos.
Now in Excel what I did was when I opened my form, I first had to choose what category to put the photo in via radio buttons on the form. I would then fill in all the pertinent data for the photo. When I executed the form, it would find the last number in the master sheet, increment it and add all the data to a new record. It would then activate the category sheet for the category I chose, determine the last number in that sheet, increment it and add in the new data there as well.
An important note is that DuplicateNumber is always 0 by defalut unless I made it 1, 2 etc. It was never computed, but I entered it.
When I created slide lables (the "report") I would include the main PhotoID, The CategoryName, CategoryID and DuplicateNumber in this format (1LS1.0, 14LS45.0, 15LS45.1) and in addtion the location and Date taken.
But now I don't want to use Excel anymore and have tried to migrate the database to MS Access. So I have one main table called PhotoDetails.
PhotoID is the primary key in this table. It has a PhotoCategory field that is a number that has a relation to another table called Category. Its Primary key is PhotoCategoryID and it is also a foreign key in PhotoDetails. PhotoCategoryID is a number that identifies each category. In the list of all my categories, LS is number 11. But here is where I get stuck.
I have CategoryNumber, which is the number identifying the photo in each category, and DuplicateNumber, identifying the number of duplicates, in the PhotoDetal table. What I can't figure out is when I add a new photo, how do I increment the CategoryNumber? The form I have right now allows me to enter in all the data, and when I click to enter the next it will autonubmer the PhotoID key, but what about CategoryNumber? I can't remember what the last number is for each Category so as for me to enter that number in myself. Unless I mimic what I did in Excel by having all the data stored twice, once in the PhotoDetails (aka Master Sheet in Excel) and again in multiple Category tables. I was hoping Access could provide me a workaround.
Currently the relationships I have are the Category table linked to PhotoDetails in a 1-M relation. I also have several other tables all linked in a 1-M relation (e.g. table Film stores the different films I have used and the 1-M relation lets me link what film to each slide, and most of the other data is stored in that way).
I have 31 different Categories. Each photo has a Category and a CategoryID number, as well as a Duplicate number. I will choose the Category on data entry and the DuplicateNumber, but I want the CategoryID number as well as PhotoID to be incremented automatically for me. What is the correct way to proceed with this where I only have to keep one PhotoDetail table that is not duplicated for each category as I did in Excel.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
好的，基本上您要做的就是“规范化”您的数据
通常您会有两个表
照片
PhotoID（数据类型：自动编号 - 用户通常不会看到 - 主键）
PhotoRefID（DT：可能是数字或文本 - 引用物理编号）
CategoryID（DT：数字 - 长整数 - 类别表的键）
重复（DT：数字 - 可能也很长 - 如果这只是一个计数）
- 或者 -
DuplicateOf（DT：数字 - 长整型 - 引用其重复项）
类别
类别 ID（DT：自动编号 - 主键）
描述（DT：文本）
您将使用关系（工具 -> 关系）来同时喜欢两个类别 ID。
然后，您可以使用这些表来构建您正在查找的表单和查询。
注意：如果您的照片可以属于多个类别，那么您将需要第三个中间表来将它们连接起来。 类似于：
照片类别
PhotoCategoryID（DT：自动编号、主键）
PhotoID（DT：数字长整型）
类别ID（DT：数字长整型）
OK, so basically what you are looking to do is "normalize" your data
Typically you would have two tables
Photo
PhotoID (Data Type: Autonumber - not normally seen by the user - Primary Key)
PhotoRefID (DT: probably a Number or Text - referencing the physical number)
CategoryID (DT: Number - a Long Integer - key to the Category table)
Duplicate (DT: Number - probably long also - if this is just a count)
-- or --
DuplicateOf (DT: Number - Long Integer - to reference what it is a duplicate of)
Category
CategoryID (DT: AutoNumber - Primary Key)
Description (DT: Text)
You will use relationships (Tools->Relationships) to like the two Category IDs together.
You can then be able to use the tables to build the forms and queries that you are looking for.
Note: If you can have photos that fit into more than one category, then you will need a third intermediate table to join them up. Something like:
PhotoCategories
PhotoCategoryID (DT: Autonumber, Primary Key)
PhotoID (DT: Number Long Integer)
CategoryID (DT: Number Long Integer)
我会考虑通过添加代码来廉价地完成此操作，以便当您尝试添加新记录时，它会在数据库中搜索类别/重复/等的最高数字，然后您可以在添加之前在代码中增加它新纪录。
没有简单的方法可以在 Access 中自动获得此功能，除非您将表分成更多表。
-亚当
I'd look at doing it cheaply by adding code so that when you try to add a new record it does a search in the database for the highest number with the category/duplicate/etc and then you can increment it in code before you add the new record.
There's no easy way to get this functionality automatically in Access, unless you break your table out into more tables.
-Adam