如何显示两个记录集之间不匹配的记录 [英] How to display records that don't match between two recordsets
问题描述
编辑：下面的解决方案，请继续阅读！ （剧透警报：我使用了DCount。）
如标题所述，我有两个记录集，我正在比较一个到另一个的多个值。我的代码适用于完美匹配，但我导入的excel表并不总是完整的，所以我需要知道手动更新哪些记录。
虽然我写了这段代码我是Access和VBA的新手，所以我不知道如何有效地检索甚至显示我需要的信息。 （我正在考虑使用报告类型记录集进行显示？）
IPAC基本上是我们从工作成本中获得的电子表格。他们没有唯一标识符，所以我比较了几个字段。如果你想知道为什么我没有使用With声明'因为它已经让我很困惑，没有嵌套的withs和哈哈。我希望这是足够的相关细节，我真的很感激你能给予的任何帮助。即使你无法解决问题，我欢迎任何有关我的做法的意见。
谢谢！
Solution below, read on! (Spoiler alert: I used DCount.)
As stated in the title I have two recordsets and am comparing multiple values from one to the other. My code works fine for the perfect matches but the excel sheet I''m importing isn''t always complete so I will need to know what records to update manually.
Though I wrote this code I am new to Access and to VBA so I have no clue how to effectively retrieve or even display the information I need. (I was thinking about displaying with a Report type recordset?)
IPAC is basically a spreadsheet we get with the cost of jobs. They don''t have a Unique Identifier so I compared several fields. In case you are wondering why I didn''t use a "With" statement that''s because it''s already pretty confusing to me without nested "withs" haha. I hope that is enough relevant detail, I really appreciate any help you can give. Even if you can''t solve the problem I welcome any input about my practices.
Thanks!
推荐答案
我可能完全错了，但我有一种直觉，可以通过查询完成整个事情。
看看在QBE网格中显示2个查询并链接
rsPRINT_JOBS！BEST_COST with curIPACBillAmt
rsIPAC！Req_Num with rsPRINT_JOBS！REQUISITION_NUM
并将标准设置为rsPRINT_JOBS！JOB_STATUS =" OPEN"并且
和rsPRINT_JOBS！BEST_COST = 0.
将其作为选择查询运行，看看它是否产生了所需的结果。如果是这样，您可以将其更改为更新查询。
您的代码删除部分只是
I may be totally wrong, but I have a gut feeling that the whole thing can be done with a query.
Have a look at showing the 2 queries in the QBE grid and linking
rsPRINT_JOBS!BEST_COST with curIPACBillAmt
rsIPAC!Req_Num with rsPRINT_JOBS!REQUISITION_NUM
and setting the criteria to rsPRINT_JOBS!JOB_STATUS = "OPEN" and
And rsPRINT_JOBS!BEST_COST = 0.
Run that as a select query and see if it produces the required results. If so, you can change it into an update query.
Your delete part of your code is simply
excel sheet我导入并不总是完整所以我需要知道要手动更新的记录。
excel sheet I''m importing isn''t always complete so I will need to know what records to update manually.
- 当你声明表单并不总是完整时，你的意思是什么？
- 更新记录的最低要求是什么？
感谢输入Phil！我选择使用代码，因为设置此数据库的人拥有数百个查询表单和表。这是一个纠结的混乱。基本上我很害怕让事情变得更糟。此外，我似乎永远不会让SQL做我想要的，因此删除查询的过度复杂哈哈。
我使用excel表中的三个字段来识别我的数据库中的作业，然后使用电子表格中的帐单金额来更新该作业。在上面的代码中，我只使用了两个字段，因为第三个字段有时只是零（在电子表格中）。我可以根据两个字段更新作业，但问题是电子表格上与唯一标识符最接近的是每年重复的工单号码，护照号码每十年重复一次。我的表有22,700条记录，可追溯到2007年。夹克编号从当年的最后一位开始。因此，如果它不能将所有三个与工作相匹配，那么很难知道我有正确的工作。我至少想用一个msgbox来显示来自IPAC的哪些记录无法匹配，但我真的希望我有更好的方法来验证。
再次感谢帮助！
Thanks for the input Phil! I chose to use code because the guy that set up this database has hundreds of queries forms and tables. It''s a tangled mess. Basically I''m paranoid about making that worse. Also I can never seem to get SQL to do what I want, hence the overcomplication of my delete query haha.
I am using three fields from the excel sheet to identify a job in my database then using the bill amount from the spreadsheet to update that job. In my code above I only used two of the fields because the third sometimes is just zeros (on the spreadsheet). I can update the jobs based on two fields but the problem is that the closest thing to a unique identifier on the spreadsheet is a work order number that repeats every year and jacket number repeats every ten years. My table has 22,700 records that date back to 2007. The jacket number begins with the last digit of the current year. So if it can''t match all three to a job it''s hard to know I have the right one. I would at minimum want to use a msgbox to display which records from the IPAC couldn''t be matched, but really I wish I had a better way to validate.
Thanks again for the help!
这篇关于如何显示两个记录集之间不匹配的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
