在没有AutoSeq的情况下插入新的唯一序列号 [英] Inserting New Unique Sequence Numbers Without AutoSeq

问题描述

G''day


有一天大脑面不会理解，所以我会发布这个问题。


我有一个表（称为供应商），它有两个字段（序列，一个唯一的数字，但没有标记为主键，虽然它基本上是一个，VendorName，供应商的名称），每月一次，我得到一个来自新供应商的运输部门的清单。此列表以Excel格式提供，因此我将工作表导入Access并运行插入查询以将新供应商放入供应商表（见下文）


序列 - VendorName

16 - ZZZ公司

23 - ABC公司

24 - XYZ公司

- LALA公司

- BABA公司


这里的问题是新的LALA和BABA公司插入没有序列号，现在必须获得下一个号码（25和26）。它可以在插入后完成，如果它在插入时更容易或者做 - 但我的脑袋今天没有得到它。


我知道这不是很难，所以没有指点和咯咯笑，但任何帮助都会很棒：>


提前谢谢

解决方案

G''day


有一天大脑面无法理解，所以我会发布问题。


我有一个表（称为供应商），它有两个字段（序列，一个唯一的号码，但没有标记为主键，虽然它基本上是一个，而VendorName，名称为供应商）每月一次我从新供应商的运输部门获得一份清单。此列表以Excel格式提供，因此我将工作表导入Access并运行插入查询以将新供应商放入供应商表（见下文）


序列 - VendorName

16 - ZZZ公司

23 - ABC公司

24 - XYZ公司

- LALA公司

- BABA公司


这里的问题是新的LALA和BABA公司插入没有序列号，现在必须获得下一个号码（25和26）。它可以在插入后完成，如果它在插入时更容易或者做 - 但我的脑袋今天没有得到它。


我知道这不是很难，所以没有指点和咯咯笑我，但任何帮助都会很棒：>


预付谢谢

我不打算写完整个更新的东西，因为你可能会把它完全不同，但是：

DMax（序列，供应商）+ 1应该做诀窍。

啊，这就是我的想法，当我把它放在我的追加查询中（将我的新记录插入到Vendors表中时），只创建了一个表达式将DMax插入序列表中我得到：


16 - ZZZ公司

23 - ABC公司

24 - XYZ公司

25 - LALA公司

25 - BABA公司

25 - 等......


记录集不会刷新每个a ppend，所以DMAX在下一个条目上看不到25，然后在26中看到+1（依此类推......），所以我的新10条记录全部得到25作为序列号（24 + 1）...


我知道，非常痛苦......：>

我在导入数据时有绝对的没有经验问题，希望这里的其他人会有，但是stwange提供的黑客，这对于这种事情来说非常基本，只会在新的记录一次一个地写入表中时才会起作用，每一个保存。否则，DMax（）将继续转到最后已保存的记录，并且只有该组追加的第一条记录才会有正确的序列号。


Linq ; 0）>

G''day

Having a day where the brain noodle will not comprehend so I though I''d post the question.

I have a table (called Vendors) which has two fields (Sequence, a unique number but not marked as a Primary Key, though it basically is one, and VendorName, the name of the Vendor) and once a month I get a list from the shipping dept of new vendors. This list comes in Excel, so I import the sheet into Access and run an Insert Query to place the new Vendors into the Vendors Table (See below)

Sequence - VendorName
16 - ZZZ Company
23 - ABC Company
24 - XYZ Company
- LALA Company
- BABA Company

The issue here is the new LALA and BABA Company Inserted has no sequence number, and must now obtain the next number (25 and 26). It can be done after the insert if it makes it easier or doen while doing the insert - but my head just isn''t getting it today.

This I know isn''t tough, so no pointing and giggling at me please but any help would be great :>

Thanks in advance

解决方案

G''day

Having a day where the brain noodle will not comprehend so I though I''d post the question.

I have a table (called Vendors) which has two fields (Sequence, a unique number but not marked as a Primary Key, though it basically is one, and VendorName, the name of the Vendor) and once a month I get a list from the shipping dept of new vendors. This list comes in Excel, so I import the sheet into Access and run an Insert Query to place the new Vendors into the Vendors Table (See below)

Sequence - VendorName
16 - ZZZ Company
23 - ABC Company
24 - XYZ Company
- LALA Company
- BABA Company

The issue here is the new LALA and BABA Company Inserted has no sequence number, and must now obtain the next number (25 and 26). It can be done after the insert if it makes it easier or doen while doing the insert - but my head just isn''t getting it today.

This I know isn''t tough, so no pointing and giggling at me please but any help would be great :>

Thanks in advance

I''m not going to bother writing the whole update thing, because you might have it set out completely different, but:
DMax("Sequence","Vendors") + 1 should do the trick.

Ah, that''s what I thought, and when I place that in my Append Query (which is inserting my new records into the Vendors table, just created an expression to insert the DMax into the Sequence Table) I get:

16 - ZZZ Company
23 - ABC Company
24 - XYZ Company
25 - LALA Company
25 - BABA Company
25 - etc...

The recordset doesn''t refresh on each append, so DMAX doesn''t see 25 on the next entry and then +1 for 26 (and so on...), so my new 10 records all get 25 as their Sequence number (24+1)...

I know, very painful... :>

I have absolutley no experience in importing data in this matter, hopefully someone else here will have, but the hack provided by stwange, which is pretty basic for this sort of thing, will only work, I think, if the new records are written to the table one at a time, with each one saved. Otherwise, DMax() is going to continue going to the last saved record, and only the first record of the group append will have a correct Sequence Number.

Linq ;0)>

这篇关于在没有AutoSeq的情况下插入新的唯一序列号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！