连续发票编号多表 [英] Sequential Invoice Number Multiple Tables

问题描述

我有3张桌子，3张不同的表格可以创建3种不同类型的发票。所有表格（客户，员工）只有2个公共字段。

我想添加一个连续的发票编号，这样无论使用何种表格，发票编号总是会增加1。表单使用插入查询创建一个新记录。

我是否必须创建一个包含PK字段的第四个表，以便我可以使用Dmax + 1？


谢谢

Hi,
I have 3 tables from which 3 different forms create 3 different types of invoices. There are only 2 common fields to all tables(customer, employees).
I would like to add a sequential Invoice number so that no matter what form is used, the invoice number always goes up by 1. The forms create a new record using an insert query.
Would I have to create a fourth table that contains a PK field so that I can use the Dmax+1?

Thanks

推荐答案

听起来像是一个过于复杂的设置，但我不知道它必须匹配的情况。


您是说您将发票存放在这三个单独的表中？如果是这样你会发现生活困难。糟糕的设计几乎总是如此，但设计越差，你需要使用它们越多，你的项目就越脆弱。


如果真的，真的需要要在三个不同的表之间创建一个序号，那么就可以完成。如果我有一些关于你正在谈论的确切信息的可靠信息，我可以帮助你。您需要访问所有三个表，并且您需要利用存储在其中的任何发票号的格式。因为我从你的问题中没有这样的知识，就我现在所能做的那样。


请记住，如果你包括所有人，将来每个人都会节省时间和精力您原始问题中的相关信息。

Sounds like an overly-complicated setup but I don''t know the situation it has to match.

Are you saying you''re storing the invoices in these three separate tables? If so you''ll find life difficult. There are nearly always ways around bad design but the worse the design the more you''ll need to use them and the more fragile will be your project.

If you really, really need to create a sequential number between three different tables then it can be done. I could help you with it if I had some reliable information as to exactly what it is you''re talking about. You will need to access all three tables and you''ll need to take advantage of the format of any invoice numbers stored within. As I have no such knowledge from your question that''s as far as I can go at this point.

Please remember in future questions that everyone saves time and energy if you include all relevant information in your original question.

您好，

我将发票存放在3个不同的表中，是的。

我会去你的直觉可能是一个糟糕的设计案例。我的最终目标是在3个表中提供这个顺序发票号码，我想只有一个但找不到方法。这些表格主要是不相关的字段。

我附上了截图。他们都使用不同的表格创建销售收据。

我想当从特定日期检索记录时，我将不得不使用来自3个表的联合查询？

我可能会放弃这个想法，如果它太乱了，并且有自己的发票编号，也许有一个前缀来区分它们。


谢谢

Hi,
I am storing Invoices in 3 different tables, yes.
I would go with your intuition that it might be a case of bad design. My end goal is to have this sequential invoice number across 3 tables, I thought about having just one but could not find a way. The tables have mostly unrelated fields.
I have attached a screenshot. They all create a sale receipt using different forms.
I suppose when it comes to retrieve record from a specific date I would have to use a union query from 3 tables?
I might abandon the idea if it gets too messy and have their own Invoice number, maybe with a prefix to distinguish them.

Thanks

附加图像

Capture.jpg （44.1 KB，343 views）

Attached Images

Capture.jpg (44.1 KB, 343 views)

为此忘掉直觉（不一般。它非常重要的。），因为这来自经验。


看着你的表的设计告诉我，你应该避免多重问题通过合并的表一起到一个单一的一个。额外的类型字段将确定它是哪种类型的发票，并且任何未使用该类型的字段都可以简单地保留为Null。即使假设您不能使用自动编号，创建PK值的工作也会简单得多，因为围绕您的发票工作的更多工作也会更加简单。这看起来很简单。

Forget intuition for this (Not generally. It''s very important.) as this comes from experience.

Looking at your table designs informs me that you should avoid multiple problems by merging your tables together into a single one. An extra type field would determine which type of invoice it is and any fields that are unused for that type can simply be left as Nulls. The work to create a PK value, even assuming you couldn''t use AutoNumber, would be much simpler, as would much more of the work surrounding any work with your invoices. This looks like a no-brainer.

这篇关于连续发票编号多表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！