Query combining records (1 Viewer)

HAP_Paul

New member
Local time
Today, 02:09
Joined
Jan 12, 2015
Messages
7
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!
 
Thank you both for your usefull reply. Based on this, I was able to minimze the run time!
 
Hi guys,
I got the same issue but using a n:m relationship for Appartments, Rooms, and AppartmentRooms
1. tblAppartments
2.tblRooms
3.tblAppartmentRooms as linked table

I like to have one query which gives me all the rooms listed in one appartment in one Column.
Appartment Top 1, Kitchen, Bath, Livingroom

Can I use a query for that or do I need also a code to do that?

Many thanks for your input

Albert
 
Hi guys,
I got the same issue but using a n:m relationship for Appartments, Rooms, and AppartmentRooms
1. tblAppartments
2.tblRooms
3.tblAppartmentRooms as linked table

I like to have one query which gives me all the rooms listed in one appartment in one Column.
Appartment Top 1, Kitchen, Bath, Livingroom

Can I use a query for that or do I need also a code to do that?

Many thanks for your input

Albert
Use the function I posted in post #2.
That is assuming your data is correctly structured.
 
Not sure it is the same issue.. and suggest this should be a new thread.
are your table relationships actually:
tblApartments -- tblApartmntRooms (1:N)
tblRooms -- tblApartmentRooms (1:N)

If so, then your query should pull ApartmentName, RoomTypeName, with sort order by ApartmentName and RoomTypName.
The list will be like
Apartment1 Bath
Apartment1 Kitchen
Apartment1 Livingroom
Apartment 2 Bath
Apartment 2 Bedroom etc
 
Hi guys,
thanks for your replies.

I have included a sample Database with the Code providet but not sure how to get it in my case working.

Maybe someone could have a look at it please?

Nice greetings

Albert
 

Attachments

I believe this is what you need, but it is giving me an ambiguous name error?

Code:
simplecsv("SELECT tblRooms.RoomName
FROM tblRooms INNER JOIN tblAppRooms ON tblRooms.RoomID = tblAppRooms.RoomID
WHERE (((tblRooms.RoomID)=[tblAppRooms].[RoomID]));
")
Query string works fine on it's own?

Cannot even get ths to work? :(
Code:
simplecsv("SELECT tblRooms.RoomName As Rooms FROM tblRooms WHERE tblRooms.RoomID =" & tblapprooms.RoomID)
 
Last edited:
Hi Gasman,

thanks for your reply I will look into it an get back to you!
Many thanks

Cheers
 
Hi guys,
thanks for your replies.

I have included a sample Database with the Code providet but not sure how to get it in my case working.

Maybe someone could have a look at it please?

Nice greetings

Albert
Hi Albert,

Hope this works for you.
SQL:
SELECT tblAppartment.AppID, tblAppartment.AppAddress, SimpleCSV("SELECT tblRooms.RoomName
FROM tblRooms INNER JOIN tblAppRooms ON tblRooms.RoomID = tblAppRooms.RoomID
WHERE tblAppRooms.AppID=" & [AppID]) AS Rooms
FROM tblAppartment;
Open Query1.
 

Attachments

Hi Albert,

Hope this works for you.
SQL:
SELECT tblAppartment.AppID, tblAppartment.AppAddress, SimpleCSV("SELECT tblRooms.RoomName
FROM tblRooms INNER JOIN tblAppRooms ON tblRooms.RoomID = tblAppRooms.RoomID
WHERE tblAppRooms.AppID=" & [AppID]) AS Rooms
FROM tblAppartment;
Open Query1.
Thank you @theDBguy
 
Hi thanks alot theDBGuy!!!

Fantastic and yes it works just great!
Just what I have been looking for!

Also thanks to gasman for your help and input!
 
Hi thanks alot theDBGuy!!!

Fantastic and yes it works just great!
Just what I have been looking for!

Also thanks to gasman for your help and input!
Glad we could assist. Good luck with your project.
 

Users who are viewing this thread

Back
Top Bottom