遍历访问表的每一列 [英] Loop through each column of an access table
问题描述
我是MS Access的新手,想知道如何使用vba编写以下代码并循环表中的每一列。
我有一个包含6列的表,表中有大约500条记录。 Col1包含日期和时间格式。 Col'的2-6包含随机的True。和假每列中的值。我需要读取col2,找到第一个真。行(将其称为开始时间和日期),查找日期和时间,并将开始日期和开始时间添加到新表中。然后它必须找到下一个假。同一列中的值(称为结束时间和日期)并将这些值复制到新表中。
我必须能够为每个列执行此操作。新表必须为每个列名添加数据。 ID,Col_Name,Start_Date,Start_Time,End_Date,End_Time。
我希望这是有道理的。请有人帮助我。
I am new to MS Access and was wondering how do I code the following using vba and looping each column in the table.
I have a table with 6 columns with about 500 records in the table. Col1 contains a date and time format. Col''s 2 - 6 contain random "True" and "False" values in each column. I need to read col2, find the 1st "True" row (call it start time and date), lookup the date and time and add the start date and start time to a new table. it must then find the next "False" value in the same column (call it end time and date) and copy those values to the new table.
I must be able to do this for each of the columns. The new table must have the data added for each of the column names. ID, Col_Name, Start_Date, Start_Time, End_Date, End_Time.
I hope this makes sense. Please could someone assist me.
推荐答案
请提供一些具体的数据,以及您正在寻找的所需结果。我对这个问题的内容很朦胧。
Kindly provide some concrete Data, and the desired results that you are looking for. I''m a little hazy on the content of the question.
感谢您的快速回复。
我有一个记录事件的数据记录器。它创建了一个CSV文件,我想将其导入访问权限以操纵数据和编写报告。当一个事件发生时,它会分配TRUE,带有一个日期戳(参见样本附带的txt)。每列记录一个不同的事件。
我需要知道事件记录何时真实。 (即日期和时间)以及下一次假时事件发生。 (即它的日期和时间)。在那一栏中。所以现在我有一个开始日期和时间以及结束日期和时间。一列可以有5个开始和结束日期和时间,而另一列可能只有2个。
我认为创建一个新表并用提取的信息填充它会更容易并且当能够编写查询以计算出真与真之间的时间差时。及其下一个假活动。
这有帮助吗?
Thanks for the speedy response.
I have a data logger that records events. Its creates a CSV file that i want to import into access to manipulate the data and write reports. When an event occurs it allocates "TRUE", with a date stamp (see txt attached for sample). Each column records a different event.
I need to know when the event records "True" (ie the date and time) and when the next "False" event occurs. (ie its date and time). In that column. So now i have a start date and time and an end date and time. One column could have 5 start and end dates and times, while another column could only have 2.
I thought it would be easier to create a new table and populate it with the extracted information and when be able to write a query to work out the time difference between the "True" and its next "False" event.
Does that help?
下面的代码将逐列处理表中的每个记录(让我们称之为Table1),记录列中第一次出现的True,然后在找到初始True后第一次出现False。它至少应该指向正确的方向。 SQL Gang可能会提供一个不太复杂的SQL解决方案,但其中存在我的弱点(SQL)。
The following code will process every Record in your Table (let''s call it Table1) on a Column by Column basis recording the 1st occurrence of True in a Column, then the 1st occurrence of a False after the initial True is found. It should at least point you in the right direction. There is probably a less complex SQL solution that the SQL Gang will come up with, but therein lies my weakness (SQL).
这篇关于遍历访问表的每一列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!