Hi all,
I have a table where comments related to a specific test are divided in several lines.
For practical reasons, I want to have a table where these comments are combined. So some thing like table 2.
Table 1 (original)
ID | Test-ID | Comment-ID | Comment
-----------------------------------------------------------------
1 10 1 Please inform the customer
2 10 2 about the deviation
3 12 1 Check if the result is correct
4 13 1 If the report needs to be sent
5 13 2 as a PDF-document, make sure that
5 13 3 this document is certified!
Table 2 (new)
ID | Test-ID | Comment
-----------------------------------------
1 10 Please inform the customer about the devation
3 12 Check if the result is correct
4 13 If the report needs to be sent as a PDF-document, make sure that this document is certified!
I have a VBA routine to get this done. The way of working is going through all records of the original table and create a new table with combined comments where applicable.
However, as there are about 30.000 records in the original table, this takes too long (over an hour).
Therefor my question: do you have a suggestion for a quick way of working? Maybe a query can do this quite fast, but I do not know how.
As you can see in the example, the number of comment lines per test-id is variable (1 or more).
Thanks for any ideas!
I have a table where comments related to a specific test are divided in several lines.
For practical reasons, I want to have a table where these comments are combined. So some thing like table 2.
Table 1 (original)
ID | Test-ID | Comment-ID | Comment
-----------------------------------------------------------------
1 10 1 Please inform the customer
2 10 2 about the deviation
3 12 1 Check if the result is correct
4 13 1 If the report needs to be sent
5 13 2 as a PDF-document, make sure that
5 13 3 this document is certified!
Table 2 (new)
ID | Test-ID | Comment
-----------------------------------------
1 10 Please inform the customer about the devation
3 12 Check if the result is correct
4 13 If the report needs to be sent as a PDF-document, make sure that this document is certified!
I have a VBA routine to get this done. The way of working is going through all records of the original table and create a new table with combined comments where applicable.
However, as there are about 30.000 records in the original table, this takes too long (over an hour).
Therefor my question: do you have a suggestion for a quick way of working? Maybe a query can do this quite fast, but I do not know how.
As you can see in the example, the number of comment lines per test-id is variable (1 or more).
Thanks for any ideas!
