按批次在收据列表中查找缺失的数字 [英] Find missing numbers in list of receipts by batch
问题描述
嘿所有，
我有一张收据表（表名：收据，字段名称：receiptNumber）和一张发出收据的表（表名：收据批次，字段名称：BatchID， BegRng，EndRng）。收据批次中的示例行：3,72551,72600。我需要根据已发布的列表检查已使用的收据列表，并找到任何已跳过的列表。收据;也就是说，找到每个批次的最大使用收据，并检查是否有任何低于该数量的收据。我已经看到了一些循环和选择语句，但是无法接近让它们起作用！在此先感谢您的帮助;这个论坛有很多好消息！
Hey all,
I have a table of receipts used (Table Name: receipts, Field Name: receiptNumber) and a table with receipts issued (Table Name: Receipts Batches, Field Names: BatchID, BegRng, EndRng). Example row in Receipts Batches: 3, 72551, 72600. I need to check the list of used receipts against the issued list and find any "skipped" receipts; that is, find the max used receipt for each batch, and check if any below that number are missing. I''ve seen some loops and select statements out there, but couldn''t come close to getting them to work! Thanks in advance for the help; this forums has so much good info!
推荐答案
嗨。多么有趣的问题！这是一个双查询解决方案。第一个查询查找批次中使用的所有收据编号，第二个查询使用此左连接返回收据并选择空收据ID以返回不在批次范围内的所有剩余收据编号：
qryAllUsedReceipts
Hi. What an interesting problem! Here''s a two-query solution. The first query finds all the receipt numbers that have been used in the batches, and the second uses this left-joined back to the receipts and selected for a null receipt ID to return all remaining receipt numbers not in the batch ranges:
qryAllUsedReceipts
没有太多细节，我会在Count的基础上工作（*）==（Max（） - Min（）+ 1）。
从这一点开始，您可以选择所有匹配的记录或使用一些代码列出缺失的数字。显然，SQL很难列出这些。
Without going into too much detail, I''d work on the basis that Count(*) == (Max() - Min() + 1).
From this point you can select all matching records or use some code to list the missing numbers. Clearly SQL would struggle to list those per se.
嗨。多么有趣的问题！这是一个双查询解决方案。第一个查询查找批次中使用的所有收据编号，第二个查询使用此左连接返回收据并选择空收据ID以返回不在批次范围内的所有剩余收据编号：
qryAllUsedReceipts
Hi. What an interesting problem! Here''s a two-query solution. The first query finds all the receipt numbers that have been used in the batches, and the second uses this left-joined back to the receipts and selected for a null receipt ID to return all remaining receipt numbers not in the batch ranges:
qryAllUsedReceipts
这篇关于按批次在收据列表中查找缺失的数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
