更新查询计数器 [英] Update Query Counter
问题描述
我正在尝试在MS Access中编写更新查询以更新数据库中的计数字段。每次测量员进入特定测量点时，我都有一个包含多个字段记录的表。测量员将信息输入数据库以填充字段，包括点编号（[Survey_point]），日期（[Date_surveyed]），时间等，并且通常在一年中多次（但不总是）。 br />
我想添加一个名为Visit_Count的字段。并更新该值以指示给定年份的访问。因此，如果测量员在一年中访问该点一次，则该记录的Visit_Count = 1。但是，如果测量员到达该点10次，则特定样本点的第一个记录点Visit_Count = 1，第二个记录= 2，依此类推。查询每个点的总访问量我没有问题，但我无法确定如何确定日期X [Date_surveyed]（存储在记录Y [ID]中），它是测量员的？访问（[Survey_count]）。
所以为了简化，这里是表格中的缩小列表字段：
ID
Surveyor_ID
Survey_Point
Date_surveyed
Survey_Count''需要更新此字段
如果有人能指出我，我将不胜感激正确的方向！谢谢。
I am trying to write an update query in MS Access to update a count field in my database. I have a table that has records with multiple fields for every time a surveyor goes to a particular survey point. The surveyor enters information into the database to populate the fields including the point number([Survey_point]), the date([Date_surveyed]), time, etc. and usually goes to point multiple times in a year (but not always).
I would like to add a field called "Visit_Count" and update the value to indicate which visit it is for a given year. So, if the surveyor visits the point a single time in one year, Visit_Count = 1 for that record. But if the surveyor goes to the point 10 times, the first record for a particular sample point Visit_Count = 1, the second = 2, and so on. I have no problem querying the total visits for each point, but I can''t figure how to determine that on date X [Date_surveyed](which is stored in record Y[ID]), it was the surveyor''s ?th visit ([Survey_count]).
So to simplify, here is a reduced list fields in the table:
ID
Surveyor_ID
Survey_Point
Date_surveyed
Survey_Count ''need to update this field
I would be grateful if anyone could point me in the right direction! Thanks.
推荐答案
您不应该将此信息存储在字段中，因为您应该存储计算值。相反，您可以创建一个查询，其中将VisitCount字段作为计算字段添加到其中。这样，如果记录从一个测量员变为另一个测量员，那么您不必修复您的访问次数。这是一个复杂的查询，我目前没有可用的测试数据库，所以可能会有一些错误，但只是让我知道它们是什么，我们可以通过它们。
You shouldn''t store this information in a field as you are supposed to store calculated values. Instead what you can do is create a query that has the VisitCount field added to it as a calculated field. That way, if ever a record gets changed from one surveyor to another, then you don''t have to fix your visit counts. This is a bit of a complicated query, and I don''t have a test database available at the moment, so there might be some errors, but just let me know what they are and we can work through them.
好的。我认为Dcount是我应该走的正确方向。我附上了一个简单的屏幕截图版本的数据外观。如果我能看到这个实现的例子，我想我可以将它应用到真实的东西。
现在，我遇到的问题是它似乎正在返回一个完整的由于某种原因计算所有记录，而不仅仅是返回递增的值。
Ok. I think Dcount is the right direction I should go. I''ve attached a reallllly simplified screen shot version of what the data look like. If I can see an example implemented in this, I think I can apply it to the real thing.
Right now, the problem I am having is that it seems to be returning a full count of all the records for some reason, not just returning an incremented value.
我假设Name_ID正在替换你的OP中的Surveyor_ID？在这种情况下，第6行应该是
I''m assuming that Name_ID is replacing Surveyor_ID from your OP? In that case, line #6 should be
这篇关于更新查询计数器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
